Campaign Report using Offer Schema - Help

Avatar

Avatar
Validate 1
Level 1
wpomeroy
Level 1

Likes

0 likes

Total Posts

17 posts

Correct reply

0 solutions
Top badges earned
Validate 1
View profile

Avatar
Validate 1
Level 1
wpomeroy
Level 1

Likes

0 likes

Total Posts

17 posts

Correct reply

0 solutions
Top badges earned
Validate 1
View profile
wpomeroy
Level 1

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

 
 
 
 

 

Accepted Solutions (1)

Accepted Solutions (1)

Avatar

Avatar
Establish
MVP
wodnicki
MVP

Likes

977 likes

Total Posts

1,096 posts

Correct reply

515 solutions
Top badges earned
Establish
Affirm 500
Contributor
Shape 1
Give Back 100
View profile

Avatar
Establish
MVP
wodnicki
MVP

Likes

977 likes

Total Posts

1,096 posts

Correct reply

515 solutions
Top badges earned
Establish
Affirm 500
Contributor
Shape 1
Give Back 100
View profile
wodnicki
MVP

29-06-2020

Hi,

 

Use ToDateTime() instead of ToDate().

 

Thanks,

-Jon

Answers (0)