Data Services Designer error Invalid WHERE clause. Additional information: Non-comparison expression is not allowed.

[Former Member]

Hi,

I need to create some logic in the WHERE clause based on dates, but I am getting an error.

Here is my logic:

ifthenelse(SQL.POST_DATE > to_date('2014.06.12',yyyy.mm.dd), 

SQL.POST_DATE > sysdate( )-45,

SQL.POST_DATE >= to_date('2014.04.12',yyyy.mm.dd) and

SQL.POST_DATE <= to_date('2014.05.12',yyyy.mm.dd))

Error is:

Invalid WHERE clause. Additional information: <Non-comparison expression < ifthenelse(SQL.POST_DATE > to_date('2014.06.12',yyyy.mm.dd), 

SQL.POST_DATE > sysdate( )-45,

SQL.POST_DATE >= to_date('2014.04.12',yyyy.mm.dd) and

SQL.POST_DATE <= to_date('2014.05.12',yyyy.mm.dd))

> is not allowed. (BODI-1111313)>. (BODI-1111078)

I'm not sure what is causing the error. Should I be using something other than ifthenelse?

[Former Member · Accepted solution]

I resolved this problem by using Global variables  for start and end dates and set the date range.

Then used this logic:

if ($gv_StartDate is null)

$gv_StartDate=sysdate() - 45;

if (gv_EndDate is null)

$gv_EndDate=sysdate();

[aasavaribhave]

Both

THEN SQL.POST_DATE > sysdate( )-45,

ELSE SQL.POST_DATE >= to_date('2014.04.12',yyyy.mm.dd) and SQL.POST_DATE <= to_date('2014.05.12',yyyy.mm.dd))

are suppose to be an expression what you have is a condition.

it should be:

ifthenelse(SQL.POST_DATE > to_date('2014.06.12',yyyy.mm.dd), 

set_some_date = sysdate( )-45,

set_some_date = between Appil 12 - May 12)

I suppose you should have from and to date here.

[former_member202087]

Can you clarify what exactly this code is supposed to do?  Based on what you have above it appears the syntax may be wrong. 

As I am interpreting it this is saying

ifthenelse(SQL.POST_DATE > to_date('2014.06.12',yyyy.mm.dd), 

THEN SQL.POST_DATE > sysdate( )-45,

ELSE SQL.POST_DATE >= to_date('2014.04.12',yyyy.mm.dd) and SQL.POST_DATE <= to_date('2014.05.12',yyyy.mm.dd))

[Former Member]

Right. In plain English, if the date is after June 6, pick the data that is 45 days older than the system date, if it is before June 6, pick the data that was posted between April 12 and May 12.

In SQL server I would have used BETWEEN, but data services doesn't seem to use it. I also tried using IF and WHERE THEN. Other posts suggested that the ifthanesle was the way to go. Hopefully I just have the syntax wrong.

[former_member202087]

Instead of in the where clause maybe you can first create a "temp" field with the following logic

ifthenelse(SQL.POST_DATE > to_date('2014.06.12',yyyy.mm.dd), 

     ifthenelse(SQL.POST_DATE > sysdate( )-45,

          'Y',

          'N'),

     ifthenelse(SQL.POST_DATE >= to_date('2014.04.12',yyyy.mm.dd) and SQL.POST_DATE <= 

          to_date('2014.05.12',yyyy.mm.dd),

           'Y',

            'N')

The first ifthenelse is checking if the post date is greater than you 6/12 date.

     If it is then the next iftenelse checks to see if it is within 45 days of the sys date.  If it is it sets the field to Y, else N.

     If it is not the last ifthenelse is checking if it is between 4/12 and 5/12.  If it is it sets the field to 'Y', else 'N'.

In your next query transform just grab the rows where the "temp" field is Y.