Skip to Content
author's profile photo Former Member
Former Member

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

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?

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on May 13, 2014 at 05:51 PM

    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();

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Apr 30, 2014 at 02:48 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Apr 30, 2014 at 02:10 PM

    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))

    Add a comment
    10|10000 characters needed characters exceeded

    • 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.

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.