SQL query generated by Crystal Reports works in SSMS but pulls no records in Crystal

Question

As the title says, I've built a Crystal Report to pull data from a SQL server. The SQL query that Crystal Reports generates pulls the data as expected when copied into SSMS but pulls no records when the report is run from Crystal Reports.

My record selection:

({PRECEIPTD.CREDAT_0} >= {?startDate} AND {PRECEIPTD.CREDAT_0} <= {?endDate}) 
AND ISNULL({PINVOICED.NUM_0}) 
AND IF ISNULL({?bpr}) OR {?bpr} = "" THEN {PORDERQ.BPSNUM_0} = {PORDERQ.BPSNUM_0} ELSE {PORDERQ.BPSNUM_0} = {?bpr}

I've also tried writing the selection like this, which pulls all of the same records:

({PRECEIPTD.CREDAT_0} >= {?startDate} AND {PRECEIPTD.CREDAT_0} <= {?endDate})
AND ISNULL({PINVOICED.NUM_0})
AND IF ISNULL({?bpr}) OR {?bpr} = "" THEN {PORDERQ.BPSNUM_0} LIKE "*" ELSE {PORDERQ.BPSNUM_0} = {?bpr}

I'm using Crystal Reports 2013 SP11, accessing the database through a ODBC connection.

EDIT: The SQL query Crystal generates:

SELECT 
    PRECEIPTD.CREDAT_0
    , PORDERQ.BPSNUM_0
    , PORDERQ.POHNUM_0
    , PORDERQ.ITMREF_0
    , PORDERQ.QTYPUU_0
    , PORDERP.NETPRI_0
    , PRECEIPTD.PTHNUM_0
    , PINVOICED.NUM_0
FROM ((x3v6.PROD.PORDERQ PORDERQ 
INNER JOIN x3v6.PROD.PORDERP PORDERP ON 
    (PORDERQ.POHNUM_0=PORDERP.POHNUM_0) 
    AND (PORDERQ.POPLIN_0=PORDERP.POPLIN_0)) 
LEFT OUTER JOIN x3v6.PROD.PRECEIPTD PRECEIPTD ON 
    ((PORDERQ.POPLIN_0=PRECEIPTD.POPLIN_0) 
    AND (PORDERQ.POQSEQ_0=PRECEIPTD.POQSEQ_0)) 
    AND (PORDERQ.POHNUM_0=PRECEIPTD.POHNUM_0)) 
LEFT OUTER JOIN x3v6.PROD.PINVOICED PINVOICED ON 
    (PRECEIPTD.PTHNUM_0=PINVOICED.PTHNUM_0) 
    AND (PRECEIPTD.PTDLIN_0=PINVOICED.PTDLIN_0)
WHERE
    (PRECEIPTD.CREDAT_0>={ts '2018-01-01 00:00:00'} AND PRECEIPTD.CREDAT_0<{ts '2019-01-01 00:00:00'}) 
    AND PINVOICED.NUM_0 IS NULL 
    AND PORDERQ.BPSNUM_0=PORDERQ.BPSNUM_0
ORDER BY 
    PORDERQ.BPSNUM_0
    , PRECEIPTD.CREDAT_0

Are you certain that the parameters are being passed from Crystal into your stored procedure properly? Add some logging to the stored procedure to record the parameters being passed into it. — alroc
The report isn't using any stored procedures, the query CR generates when running: see edit in post — z-combs
Similar question - do you know for certain that the parameters are getting populated into the query properly? Is it connected to the database & instance you're expecting? — alroc
Yes, I built the SQL query in SSMS before developing the Crystal Report as a baseline as to what I should see come out of the report. I've verified, refreshed and logged off of the database to force the logon prompt. The data is being pulled out of the correct schema. — z-combs

z-combs z-combs · Accepted Answer · 2018-03-09T18:27:40

Did some more searching around and found a similar post at https://stackoverflow.com/a/43203088/8672275. The AND ISNULL({PINVOICED.NUM_0}) section of my record selection was throwing Crystal Reports off. I changed it to AND ToText({PINVOICED.NUM_0}) = "" and the report is returning exactly what I expect to see.

SQL query generated by Crystal Reports works in SSMS but pulls no records in Crystal

1 Answers