Using parameter in ADD COMMAND section of Database Expert

[krpurcel]

I am trying to use the add command function of the database expert. I am using Crystal 2008 and trying pull data from an Oracle DB. I have the following SQL query that works.

[select sum(a.OPEN), sum(b.CLOSED) FROM
(Select SC.INCIDENTSM1.OPENED_BY AS ANALYST, Count(*) AS OPEN FROM SC.INCIDENTSM1
WHERE (SC.INCIDENTSM1.Entry_Point = 11) AND
not (SC.INCIDENTSM1.CATEGORY = 'FACILITIES') and
not (SC.INCIDENTSM1.CATEGORY = 'BUILDING SERVICES') and
(SC.INCIDENTSM1.OPEN_TIME BETWEEN new_time(to_date ('2009-07-14 00:00:01', 'yyyy-mm-dd HH24:MI:SS'),'CST','GMT')
AND new_time(to_date ('2009-07-14 23:59:59','yyyy-mm-dd HH24:MI:SS'),'CST','GMT')) GROUP BY SC.INCIDENTSM1.OPENED_BY) a,
(Select SC.INCIDENTSM1.OPENED_BY AS ANALYST, Count(*) AS CLOSED FROM SC.INCIDENTSM1
WHERE (trunc(SC.INCIDENTSM1.CLOSE_TIME) = trunc(SC.INCIDENTSM1.OPEN_TIME))
AND (SC.INCIDENTSM1.CLOSED_BY = SC.INCIDENTSM1.OPENED_BY)
AND (SC.INCIDENTSM1.Entry_Point = 11) AND
not (SC.INCIDENTSM1.CATEGORY = 'FACILITIES') and
not (SC.INCIDENTSM1.CATEGORY = 'BUILDING SERVICES') and
(SC.INCIDENTSM1.OPEN_TIME BETWEEN new_time(to_date ('2009-07-29 00:00:01', 'yyyy-mm-dd HH24:MI:SS'), 'CST','GMT')
AND new_time(to_date ('2009-07-29 23:59:59','yyyy-mm-dd HH24:MI:SS'),'CST','GMT')) GROUP BY SC.INCIDENTSM1.OPENED_BY) b
WHERE a.ANALYST = b.ANALYST (+)]

I created two simple date parameters, START and END. I have been trying to figure out how to substitute them for the static dates in the query. I am having trouble figuring out the Oracle date format so I know what I need to replace with the parameters. I have tried both DATE and DATETIME as the value type for the parameters with no success.

Can someone offer some pointers? If I failed to give needed info let me know what it is and I will post it.

Thanks

 

[Turkbear]

Hi,
use a string instead ( provide the user with the pattern needed, like '2009-07-29 23:58:59' and use your Sql as is, with the params instead of the static dates:

select  sum(a.OPEN),  sum(b.CLOSED) FROM 
            (Select SC.INCIDENTSM1.OPENED_BY AS ANALYST, Count(*) AS OPEN FROM SC.INCIDENTSM1 
            WHERE (SC.INCIDENTSM1.Entry_Point = 11) AND 
            not (SC.INCIDENTSM1.CATEGORY  = 'FACILITIES')     and 
            not (SC.INCIDENTSM1.CATEGORY = 'BUILDING SERVICES') and 
            (SC.INCIDENTSM1.OPEN_TIME BETWEEN new_time(to_date ({?StartDate}, 'yyyy-mm-dd HH24:MI:SS'),'CST','GMT') 
            AND new_time(to_date ({?EndDate},'yyyy-mm-dd HH24:MI:SS'),'CST','GMT')) GROUP BY SC.INCIDENTSM1.OPENED_BY) a,
            (Select SC.INCIDENTSM1.OPENED_BY AS ANALYST, Count(*) AS CLOSED FROM SC.INCIDENTSM1 
            WHERE (trunc(SC.INCIDENTSM1.CLOSE_TIME) = trunc(SC.INCIDENTSM1.OPEN_TIME)) 
            AND (SC.INCIDENTSM1.CLOSED_BY = SC.INCIDENTSM1.OPENED_BY) 
            AND (SC.INCIDENTSM1.Entry_Point = 11) AND 
            not (SC.INCIDENTSM1.CATEGORY  = 'FACILITIES')     and 
            not (SC.INCIDENTSM1.CATEGORY = 'BUILDING SERVICES') and 
            (SC.INCIDENTSM1.OPEN_TIME BETWEEN new_time(to_date ({?StartDate}, 'yyyy-mm-dd HH24:MI:SS'), 'CST','GMT') 
            AND new_time(to_date (({?EndDate},'yyyy-mm-dd HH24:MI:SS'),'CST','GMT')) GROUP BY SC.INCIDENTSM1.OPENED_BY) b 
            WHERE a.ANALYST = b.ANALYST (+)

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[krpurcel]

Thanks Turkbear! I'll try it out and post my results. Thanks for your help!

 

[krpurcel]

Well it only took 6 weeks to get back to where I could work on this! Turkbear your solution works great. Thanks very much. A string was the answer.