SQL error in Crystal Reports

Question

I'm making a report in Crystal Reports. I have parameters set up to select a date range for Effective Date and Terminated Date. But it turns out that these parameters are supposed to be optional. So I've been trying to set up the report so that the user doesn't have to enter dates unless they want to filter. To do this, I figured I would set up defualt dates that, when used, would tell the report to grab all data. This is the SQL code I'm using to do this:

SELECT
P.VISION_POLICY_NUM
,P.VP_POLICYHOLDER_NAM
,P.VP_EFF_DT
,P.VP_TERMINATED_DT
,P.VP_STATE_CD
,P.VP_SMALL_BUSINESS_IND
,P.VP_GROUP_TYPE_DESC
FROM
GLX_VISION_POLICY_REF P
WHERE
(if {?Effective From} = '01-Jan-1900'
 and {?Effective To} = '01-Jan-3000'
 then (P.VP_EFF_DT > '01-Jan-1900' OR  P.VP_EFF_DT is null)
 else P.VP_EFF_DT BETWEEN {?Effective From} AND {?Effective To})
AND
(if {?Terminated From} = '01-Jan-1900'
 and {?Terminated To} = '01-Jan-3000'
 then (P.VP_TERMINATED_DT > '01-Jan-1900' OR  P.VP_TERMINATED_DT is null)
 else P.VP_TERMINATED_DT BETWEEN {?Terminated From} AND {?Terminated To})
AND
('{?State}' = 'ALL' OR P.VP_STATE_CD = '{?State}')
AND
('{?Group Type}' = 'ALL' OR P.VP_GROUP_TYPE_DESC = '{?Group Type}')

I get this error when I run:

Failed to retrieve data from database.
Details: HY000:[Oracle][ODBC][Ora]ORA-00936: invalid relational operator
[Database Vendor Code: 920]

This chunk of code is what was added right before I started getting the error:

(if {?Effective From} = '01-Jan-1900'
 and {?Effective To} = '01-Jan-3000'
 then (P.VP_EFF_DT > '01-Jan-1900' OR  P.VP_EFF_DT is null)
 else P.VP_EFF_DT BETWEEN {?Effective From} AND {?Effective To})
AND
(if {?Terminated From} = '01-Jan-1900'
 and {?Terminated To} = '01-Jan-3000'
 then (P.VP_TERMINATED_DT > '01-Jan-1900' OR  P.VP_TERMINATED_DT is null)
 else P.VP_TERMINATED_DT BETWEEN {?Terminated From} AND {?Terminated To})

So pretty much, if the user runs the report with the defualt dates, then all the data is returned. If the user changes those defualt dates, then the report grabs data based on the date ranges selected. Pretty straight forward. I can't pick out what the issue is. Is it my code, or is it something to do with crystal reports?

craig craig · Accepted Answer · 2014-03-20T18:14:45

From memory:

...
(
CASE WHEN {?Effective From} = '01-Jan-1900' and {?Effective To} = '01-Jan-3000' THEN
  (P.VP_EFF_DT > '01-Jan-1900' OR  P.VP_EFF_DT is null)
ELSE 
  (P.VP_EFF_DT BETWEEN {?Effective From} AND {?Effective To})
END
)
AND
(
CASE WHEN {?Terminated From} = '01-Jan-1900' and {?Terminated To} = '01-Jan-3000' THEN
  (P.VP_TERMINATED_DT > '01-Jan-1900' OR  P.VP_TERMINATED_DT is null)
ELSE 
  (P.VP_TERMINATED_DT BETWEEN {?Terminated From} AND {?Terminated To})
END
)
...

SQL error in Crystal Reports

1 Answers