Unions in SQL commands in CR 10

[ernstmik]

I need help fast! I have a multi-level SQL statement connected by a number of unions. When I paste it into a SQL editor such as TOAD or DBArtisan and run it against Oracle, it runs great and I get all the records I expect to get. However, when I create a brand new report in CR 10 and paste the exact same query in the "Add Command" window, I get zero records. Here is my SQL statement (forgive me, it's long):

select
user_id,
workitem_id,
decode(status, 20, 'complete', 50, 'Discard', 'none') "STATUS",
to_char(complete_date, 'DD-MON-YY HH24:MI:SS') "TIMESTAMP"
from
bcbs_entry
where
status in (20, 50) and
to_date(complete_date) = to_date(sysdate)
union all
select
user_id,
workitem_id,
decode(status, 20, 'complete', 50, 'Discard', 'none') "STATUS",
to_char(complete_date, 'DD-MON-YY HH24:MI:SS') "TIMESTAMP"
from
ref_phys_entry
where
status in (20, 50) and
to_date(complete_date) = to_date(sysdate)
union all
select
user_id,
workitem_id,
decode(status, 20, 'complete', 50, 'Discard', 'none') "STATUS",
to_char(complete_date, 'DD-MON-YY HH24:MI:SS') "TIMESTAMP"
from
mcare_entry
where
status in (20, 50) and
to_date(complete_date) = to_date(sysdate)
union all
select
user_id,
workitem_id,
decode(status, 20, 'complete', 50, 'Discard', 'none') "STATUS",
to_char(complete_date, 'DD-MON-YY HH24:MI:SS') "TIMESTAMP"
from
me_entry
where
status in (20, 50) and
to_date(complete_date) = to_date(sysdate)
union all
select
from_user "USER_ID",
workitem_id,
decode(status, 20, 'complete', 50, 'Discard', 'none') "STATUS",
to_char(cr_date, 'DD-MON-YY HH24:MI:SS') "TIMESTAMP"
from
history
where
to_date(cr_date) = to_date(sysdate) and
status in (20, 50) and
workitem_id in (select workitem_id from man_care_entry where status in (20, 50) and to_date(complete_date) = to_date(sysdate))
union all
select
user_id,
workitem_id,
decode(status, 20, 'complete', 50, 'Discard', 'none') "STATUS",
to_char(complete_date, 'DD-MON-YY HH24:MI:SS') "TIMESTAMP"
from
outpatient_entry
where
status in (20, 50) and
to_date(complete_date) = to_date(sysdate)
union all
select
user_id,
workitem_id,
decode(status, 20, 'complete', 50, 'Discard', 'none') "STATUS",
to_char(complete_date, 'DD-MON-YY HH24:MI:SS') "TIMESTAMP"
from
pre_reg_entry
where
status in (20, 50) and
to_date(complete_date) = to_date(sysdate)
order by
user_id,
timestamp,
workitem_id,
status


HELP!!! Any ideas?

 

[Turkbear]

Hi,
If it works when sent directly to Oracle, I am not sure why it would not work in CR - Are you using Native Oracle connectivity ( Not ADO or ODBC ) ? If ADO/RDO that may be the source of the problem..

If you can, why not create a view in Oracle using that code and use that view for your report ( with a native Oracle connection)?

 

[lbass]

Do you get any error messages? You might try changing the ORDER BY to use numbers representing the sequential position of fields, instead of the field names, e.g.,
ORDER BY
1,4,2,3

Not sure this will help...

-LB

 

[dBjason]

Have you considered creating a view based off that SQL statement and then binding the report to it as a data source?

 

[IdoMillet]

Please test to see if the problem goes away with less than 9 UNIONs.

In any case, the View approach is the best immediate solution.

Cheers,
- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[ernstmik]

I found out what the problem was. The issue, which it always seems to be, was with the "to_date(complete_date) = to_date(sysdate)" statement in the the union statments. TOAD was able to use that as is but CR10 didn't respond to the to_date function. It didn't give errors, it just didn't return data. I changed the to_date to a to_char and formatted it into a dat format and it worked perfectly.

Thanks for all your help and yes, I am going to talk to the dba about creating a view to make my life easier.

 

[kskid]

complete_date = sysdate should have been sufficient unless complete_date is a VARCHAR2 field. Then you should have

to_date(complete_date,'MM/DD/YYYY') = sysdate

where 'MM/DD/YYYY' is the date format of the VARCHAR2 field.

-LW