Crystal Command Object Parameter to select multiple date ranges? 1

[willz99ta]

I am trying to have one command object parameter for my predefined date range (string) that is entered to select which date range to use. What am I doing wrong?

My where statement looks like this in my Crystal 2013 Command Object (Note: this is hitting a Oracle PL/SQL database)

WHERE
({?PredefinedDateRange} = 'yesterday' AND H.HOSP_DISCH_TIME between trunc(sysdate-1) and trunc(sysdate))
or
({?PredefinedDateRange} = 'lastweek' AND H.HOSP_DISCH_TIME between next_day(sysdate,'SUN')-14 and next_day(sysdate,'SAT')-7)
or
({?PredefinedDateRange} = 'lastmonth' AND H.HOSP_DISCH_TIME between add_months(trunc(sysdate,'mm'),-1) and last_day(add_months(trunc(sysdate,'mm'),-1)))
or
({?PredefinedDateRange} = 'lastquarter' AND H.HOSP_DISCH_TIME between ADD_MONTHS(TRUNC(SYSDATE,'Q'),-3) and TRUNC(SYSDATE,'Q') - 1)
or
({?PredefinedDateRange} = 'lastyear' AND H.HOSP_DISCH_TIME between TRUNC(TRUNC(SYSDATE,'Year')-1,'Year') and LAST_DAY(ADD_MONTHS(TRUNC(TRUNC(SYSDATE,'Year')-1,'Year'),11)))
or
({?PredefinedDateRange} = 'currentquarter' AND H.HOSP_DISCH_TIME between TRUNC(SYSDATE,'Q') and LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE,'Q'),2)))
or
({?PredefinedDateRange} = 'rollingyear' AND H.HOSP_DISCH_TIME between trunc(sysdate-365) and trunc(sysdate))

 

[kray4660]

I do not know about Oracle, but in MS-SQL, string parameters have to be surrounded by tick marks (i.e., '{?PredefinedDateRange}' ).

 

[willz99ta]

Perfect, thanks