Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
BedrockMission!

Learn more

View all

Sign in to view all badges

SOLVED

Campaign Report using Offer Schema - Help

wpomeroy
Level 2
Level 2

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
1 Accepted Solution
wodnicki
Correct answer by
Community Advisor
Community Advisor

Hi,

 

Use ToDateTime() instead of ToDate().

 

Thanks,

-Jon

View solution in original post

2 Replies
wodnicki
Correct answer by
Community Advisor
Community Advisor

Hi,

 

Use ToDateTime() instead of ToDate().

 

Thanks,

-Jon

View solution in original post

wpomeroy
Level 2
Level 2
This worked, thank you! I also had to change the offer start date field (non local variable) to DateOnly so that it the time stamps would match. This allowed me to say vars/startDate = @startDate