Campaign Report using Offer Schema - Help

wpomeroy

29-06-2020

I am trying to build a report that shows information from our offer schema (label, start date, end date, HTML Rendering, etc.) based on use input of offer name and date.  I.e. user enters the offer name or start and end date and they are able to see the offer information.

 

I am defining local variables and then using the query to filter by the defined variables.  Searching by offer label works fine but start or end date throws the following error.

 

ORA-210000 Oracle error: ORA-00932: inconsistent datatypes: expected DATE got NUMBER WDB-200002 SQL statement 'SELECT O0.sLabel, O0.sName, O0.tsEnd, O0.tsStart, O0.sCode, X1.sLabel FROM NmsOffer O0 JOIN XtkFolder X1 ON (X1.iFolderId = O0.iCategoryId) WHERE (O0.sLabel LIKE :1 || :2 || :3 ESCAPE '\') AND (O0.tsStart <= trunc(:4, 'ddd')) AND ((X1.sName LIKE :5 || :6 || :7 ESCAPE '\') AND (O0.sLabel NOT LIKE :8 || :9 || :10 ESCAPE '\') AND (O0.sLabel NOT LIKE :11 || :12 || :13 ESCAPE '\') AND (O0.sLabel NOT LIKE :14 || :15 || :16 ESCAPE '\'))' could not be executed (error in position 207: '<='). Param(0)=% Param(1)=WP Param(2)=% Param(3)=2020-06-29 Param(4)=% Param(5)=livePERD Param(6)=% Param(7)=% Param(8)=Test Param(9)=% Param(10)=% Param(11)=test Param(12)=% Param(13)=% Param(14)=TEST Param(15)=% SOP
 
I have tried defining both the variables and the offer fields explicitly as dates (ToDate) without success.  I havce also tried leveraging a script activity to format the date.  Thus far nothing has worked.  Any help would be MUCH appreciated.

 

Below is my report setup

wpomeroy_0-1593452793418.png

Image_2.jpgImage_6.jpgImage_5.jpgImage_4.jpgImage_3.jpg

 
 
 
 

 

Offers reports

Accepted Solutions (1)

Accepted Solutions (1)

wodnicki

MVP

29-06-2020

Hi,

 

Use ToDateTime() instead of ToDate().

 

Thanks,

-Jon

Answers (0)