Help! CR8.5+Oracle8i+View+Union

[asever]

Hi,
I had create a view with union operation.
The view have static field, exm:
create view v_aaa as
select 0 code, f1, ... from t1... where ...
union all
select 50 code, f1, ... from t2... where ...
Ok!
In Crystal Report 8.5:
select code, f1 from v_aaa odrder by f1
So, CR don't fetch records with code=50, only code=0.
SQL+ fetch all records.

It's bug? Have anybody that anytime?
My mind break

Regards,
AS

PS. Sorry for my english.

 

[synapsevampire]

Test the View alone, and consider posting specifics such as CR version, database used.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Naith]

He's said all that, hasn't he?

AS: are you trying select distinct records into Crystal? If so, do that in the view if possible. Crystal has had issues with combining distincts and unions in the past - although I haven't heard that's still a problem with 8.5.

Additionally, if possible, switch the driver you're using - if you're connecting natively, use odbc - or vice versa, and gauge whether your connectivity is affecting your results.

Good luck,

Naith

 

[synapsevampire]

Naith, yeah, I must have been purdy tired...

asever: Does the View return the data properly from SQL Designer/worksheet?

I haven't seen Crystal disregard 1/2 of a view, perhaps you should temp table the union and select from it from within an SP.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[asever]

Thank you all for help!
It's voodoo magic, I cannot understand it.
For test I created a view (just view with union),
CR worked...

 

[asever]

Hi all again,
It isn't dark magic, it's a date format.
See to me:
create view v_aaa as
select 0 code, f1, ... from t1...
where d<=to_date('00.00.0000','DD.MM.YYYY') ...
union all
select 50 code, f1, ... from t2...
where d<='00.00.0000' ...
So, the second part view CR don't get.
But SQL+ get all rows...
By trace I have that CR send to Oracle server
'Selec * from v_aaa'.
What maybe it? Where is bug?
Can anybody tell me...

Again, thank you all who try to help me...

Regards,
AS

 

[synapsevampire]

&quot;'Select * from v_aaa'&quot; seems right...

The View code looks odd though:

create view v_aaa as
select 0 code, f1, ... from t1...
where d<=to_date('00.00.0000','DD.MM.YYYY') ...
union all
select 50 code, f1, ... from t2...
where d<='00.00.0000' ...

Since you use different where clauses, try omitting the where clause and see what happens, it may be that there's a data type problem.

Also, you can probably use the DECODE in Oracle to resolve the CODE rather than a UNION, Unions are VERY inefficient.

This would probably eliminate your error.

Post the entire SQL and I'll work it out for you.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com