mysql - Crystal Reports doesn't execute SQL query properly -

i writing report pull metal spot prices based on promise date of shipments. uses 60 day midwest (mw) average. shipment promised in march mw average january used. table user defined , there issues keep me pulling field normal (loong story).

so wrote query compare column label in database ([month] [year]) month , year of promise date , return correct mw average. runs in oracle's sql developer in crystal runs incorrectly, returning same month's mw average (or doesn't run @ all). below reference. (i know it's bit clunky)

select ap.invoice_date indate,  ap.invoice_no invno, ap.vendor_id, apd.po_detail_id podid,  por.promise_date pd, dat.parent_id vendid,  dat.ud_cols_id cols,  col.col_label label,  dat.cuser ingot 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 trim(to_char(add_months(por.promise_date, -2), 'month'))= upper(substr(col.col_label, 1, length(col_label)-5))  , substr(por.promise_date, -2, 2) = substr(col.col_label, -2, 2) , ud_cols_id in (94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 146) order ap.invoice_date 

i'm thinking has dates , formatting broke out , comparisons match up. tried every combo of linking (but stuck left outer joins, inner join made report return blank). browsing field data there nothing either.

any ideas @ appreciated. i'm @ loss..

actually should work in crystal once works on database if not working can change bit on how form crystal report data present in database , working perfectly.

give below approach chance.first split query in select 1 part , clause 1 part

select ap.invoice_date indate,  ap.invoice_no invno, ap.vendor_id, apd.po_detail_id podid,  por.promise_date pd, dat.parent_id vendid,  dat.ud_cols_id cols,  col.col_label label,  dat.cuser ingot 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 above select query joins in crystal report , run report , see if can data... if unable data there problem database connection may pointing wrong database in crystal reports.

instead of writing clause in query can manipulate 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))  , substr(por.promise_date, -2, 2) = substr(col.col_label, -2, 2) , ud_cols_id in (94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 146) 

in crystal many need change sytax suit crystal syntax to_char may not work.

if see database connection wrong make correct connection , follow process pasting whole query