Crystal Reports doesn't execute SQL query properly

Question

I am writing a report to pull metal spot prices based on the promise date of shipments. It uses the 60 Day midwest (MW) average. So for a shipment promised in March the MW average from January is used. The table is user defined and there are some issues that keep me from just pulling the field like normal (loong story).

So I wrote a query to compare the column label in the database ([MONTH] [YEAR]) to the month and year of the promise date and return the correct MW average. It runs properly in Oracle's SQL Developer but in Crystal it runs incorrectly, returning the same month's MW average (or doesn't run at all). It is below for reference. (I know it's a bit clunky)

select ap.invoice_Date indate, 
ap.invoice_no as invno,
ap.vendor_id,
apd.po_detail_id as podid, 
por.promise_date as pd,
dat.parent_id as vendid, 
dat.ud_cols_id as cols, 
col.col_label label, 
dat.cuser as ingot
from ud_data dat
inner join ud_cols col
on dat.ud_cols_id = col.id
inner join apinvoice ap
on ap.vendor_id = dat.parent_id
inner join apinvoice_detail apd
on ap.id = apd.apinvoice_id
inner join po_detail pod
on apd.po_detail_id = pod.id
inner join po_releases por
on pod.id = por.po_detail_id
where trim(TO_CHAR(add_months(por.promise_date, -2), 'MONTH'))= upper(substr(COL.col_label, 1, length(col_label)-5)) 
and substr(por.promise_date, -2, 2) = substr(col.col_label, -2, 2)
and ud_cols_id in (94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 146)
order by ap.invoice_Date

I'm thinking it has something to do with the dates and formatting but I broke those out and the comparisons match up. I tried every combo of linking I could (but stuck with left outer joins, inner join made the report return blank). Browsing the field data there is nothing either.

Any ideas at all are appreciated. I'm at a loss..

Before I spend anytime looking through a solution... You didnt answer reds question. — CoSpringsGuy
@reds I'm not sure I understand what you're asking. I added it as a command on the table listing. linked on vendor_id to the vendor ID on the AP invoice. — tr241009
@CoSpringsGuy So I saw. I have answered it now. If i am misunderstanding maybe you can clarify? — tr241009
since the query runs fine in the Oracle app its hard to say. Does the oracle app use the same odbc setup as Crystal? — CoSpringsGuy

Siva Siva · Accepted Answer · 2016-07-24T11:32:37

Actually it should work in crystal once it works on database but if it is not working then you can change a bit on how you form a crystal report as data is present in database and it is working perfectly.

give the below approach a chance.First split the query in to select as one part and where clause as one part

select ap.invoice_Date indate, 
ap.invoice_no as invno,
ap.vendor_id,
apd.po_detail_id as podid, 
por.promise_date as pd,
dat.parent_id as vendid, 
dat.ud_cols_id as cols, 
col.col_label label, 
dat.cuser as ingot
from ud_data dat
inner join ud_cols col
on dat.ud_cols_id = col.id
inner join apinvoice ap
on ap.vendor_id = dat.parent_id
inner join apinvoice_detail apd
on ap.id = apd.apinvoice_id
inner join po_detail pod
on apd.po_detail_id = pod.id
inner join po_releases por
on pod.id = por.po_detail_id

Now just the above select query with joins in crystal report and run the report and see if you can get data... If you are unable to get data then there is problem with the database connection like you may be pointing to wrong database in crystal reports.

Instead of writing the where clause in query itself you can manipulate the same in crystal reports in Record Selection Formula

where trim(TO_CHAR(add_months(por.promise_date, -2), 'MONTH'))= upper(substr(COL.col_label, 1, length(col_label)-5)) 
and substr(por.promise_date, -2, 2) = substr(col.col_label, -2, 2)
and ud_cols_id in (94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 146)

In crystal you many need to change the sytax to suit crystal syntax like to_char may not work.

If you see the database connection is itself wrong then make correct connection and follow the process by pasting the whole query

Crystal Reports doesn't execute SQL query properly

2 Answers