1 Reply Latest reply on Apr 12, 2015 2:09 PM by user992927

    passing Session variable of DATE data type to opaque view filter

    user992927

      Hi Everyone,

       

      Can you guys please help me in passing session variable of DATE data type in RPD's physical layer 'opaque view' filter for Oracle database

       

      I tried following syntax, syntax wise I didn't got getting any error, but at the same time this opaque view is not fetching any records as well. my session variable is "END_DATE" and its value is 1998/12/31:00:00:00(as shown in RPD session windows, datatype is DATETIME)

       

       

      SELECT AMOUNT_SOLD, CHANNEL_ID, CUST_ID, PROD_ID, PROMO_ID, QUANTITY_SOLD, TIME_ID FROM SH.SALES

       

      WHERE TIME_ID =TO_DATE( 'VALUEOF(NQ_SESSION.END_DATE)','MM/DD/YYYY')

       

       

      SELECT AMOUNT_SOLD, CHANNEL_ID, CUST_ID, PROD_ID, PROMO_ID, QUANTITY_SOLD, TIME_ID FROM SH.SALES

       

      WHERE TIME_ID = TO_DATE( 'VALUEOF(NQ_SESSION."END_DATE")','MM/DD/YYYY')

       

       

      SELECT AMOUNT_SOLD, CHANNEL_ID, CUST_ID, PROD_ID, PROMO_ID, QUANTITY_SOLD, TIME_ID FROM SH.SALES

       

      WHERE TRUNC(TIME_ID) = TO_DATE( 'VALUEOF(NQ_SESSION."END_DATE")','MM/DD/YYYY')

       

      In past, I was able to pass a session variable into a opaque view filter using DATEOF function, but that was in DB2.

       

      I appreciate your time and help