CODEWITHSUNDEEP
oracle-databasecognos
Eric
Eric

Reputation: 27

Converting Cognos specific lines to oracle sql

I was trying to create a stored procedure in Oracle SQL, while looking at the code I came across this specific line which is used in cognos where the back end is an Oracle DB.

How do I convert this line, so that it can be used in stored procedure?

(#PROMPT('P_DATE_TYPE')#) = 1 
and A.STRD_DTT between TO_DATE(SUBSTR(#SQ(PROMPT('P_fromCreateDate', 'datetime'))#,1,19), 'YYYY-MM-dd HH24:MI:SS')
and TO_DATE(SUBSTR(#SQ(PROMPT('P_ToCreateDate', 'datetime'))#,1,19), 'YYYY-MM-dd HH24:MI:SS')       
OR      
(#PROMPT('P_DATE_TYPE')#) = 2 
and A.crtd_dtt between TO_DATE(SUBSTR(#SQ(PROMPT('P_fromCreateDate', 'datetime'))#,1,19), 'YYYY-MM-dd HH24:MI:SS')
and TO_DATE(SUBSTR(#SQ(PROMPT('P_ToCreateDate', 'datetime'))#,1,19), 'YYYY-MM-dd HH24:MI:SS')
OR
(#PROMPT('P_DATE_TYPE')#) = 3 
and A.END_DTT between TO_DATE(SUBSTR(#SQ(PROMPT('P_fromCreateDate', 'datetime'))#,1,19), 'YYYY-MM-dd HH24:MI:SS')
and TO_DATE(SUBSTR(#SQ(PROMPT('P_ToCreateDate', 'datetime'))#,1,19), 'YYYY-MM-dd HH24:MI:SS')

Upvotes: 0

Views: 133

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35603

There are 3 parameters, P_DATE_TYPE carries a value of 1 or 2 or 3 and this is used to indicate which date column is to be used in the date range. The other 2 parameters simply define the boundaries of the date range. It seems that these 2 values are passed as strings that needs conversion into dates.

Whilst the following may still require some further refeinement it looks something like the following for an Oracle query

WHERE ($P_DATE_TYPE = 1 AND
       A.STRD_DTT BETWEEN to_date(substr($P_fromCreateDate,1,19),'YYYY-MM-dd HH24:MI:SS')
                      AND to_date(substr($P_ToCreateDate,1,19),'YYYY-MM-dd HH24:MI:SS')
    )
OR ($P_DATE_TYPE = 2 AND
       A.crtd_dtt BETWEEN to_date(substr($P_fromCreateDate,1,19),'YYYY-MM-dd HH24:MI:SS')
                      AND to_date(substr($P_ToCreateDate,1,19),'YYYY-MM-dd HH24:MI:SS')
    )
OR ($P_DATE_TYPE = 3  AND
       A.END_DTT BETWEEN to_date(substr($P_fromCreateDate,1,19),'YYYY-MM-dd HH24:MI:SS')
                     AND to_date(substr($P_ToCreateDate,1,19),'YYYY-MM-dd HH24:MI:SS')
    )

In a stored procedure you would declare your own parameter names and use those instead of the $ I have used to indicate the parameters.

  1. $P_DATE_TYPE :: holding value 1 or 2 or 3
  2. $P_fromCreateDate :: a date literal
  3. $P_ToCreateDate :: a date literal

Upvotes: 1

Related Questions