Skip to Content

How to put a if statement in where clause?

Hi All,

I am trying to replace a specific value of a column and put it in the where clause. Usually I would create a calculated column and put it in filter. But as I am creating a DB view in catalog folder, I don't have this option. The calculated column with the if() should look like below:

IF(a = '19000101', current_date, a).

Also, I have an EXIST function which I am trying to achive in calc view, it will be amazing if someone could help me with that.

Regards,

Deba

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Aug 14, 2017 at 05:11 AM

    I'm not fully clear about what you see the problem in here.

    You mentioned that you try to create a "DB view in catalog folder", so I assume you want to create an SQL view.

    That given it is trivial to perform the transformation you described; it can be done by a simple projection in the SELECT statement of the view like this:

    SELECT MAP("a", '19000101', current_date, "a") as "A_MAPPED" 
    FROM <table_name>;

    Be aware that in your requirement you specified to have a character input data type but a date output data type (due to the current_date) function. This is not a good idea as it will confuse anyone who reads the code.

    Instead, consider an explicit type conversion on either the input data before the mapping or a conversion of current_date into DATS format in the mapping function via TO_DATS().

    Add comment
    10|10000 characters needed characters exceeded