Skip to Content

How to put a if statement in where clause?

Aug 13, 2017 at 03:22 PM


avatar image

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.



10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Lars Breddemann
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().

10 |10000 characters needed characters left characters exceeded