You can translate the question and the replies:

How many CASE statements are allowed in one view?

Hi I have a question regarding the CASE statement and how many can be used in creating a view. I have two views joined in a UNION. I have multiple fields where I have applied the following statement: Case WHEN (view1.field1 is null) THEN view2.field1 ELSE view1.field1 END This works fine up until a point. After I’ve applied this to 4 fields the code will start to revert to the following: (that is one of the fields that I’ve already converted will change to) Case WHEN (view1.field1 is null) THEN view1.field1 ELSE view1.field1 END It seems that I can’t use this case statement more than 4 times in one view. Is this correct? Is this something to do with the fact that the tables are joined in a UNION? We’re on Denodo version 5.0 Thanks Hi I've done some further work and discovered it may be down to the column name in each view. Below are the case statements and what happens. I create a case statement, the exact statement is: case WHEN (drv_ibs_actual_byproject_active.projectname is null) THEN drv_nav_adjustment.projectname ELSE drv_ibs_actual_byproject_active.projectname END I OK this statement and OK and refresh the view. When I go back to edit the statement the query has been changed to the following: case WHEN (drv_ibs_actual_byproject_active.projectname is null ) THEN drv_ibs_actual_byproject_active.projectname ELSE drv_ibs_actual_byproject_active.projectname END You can see that the THEN part of the statement has changed from view drv_nv to drv_ibs. I have not made this change and I have never put this into the query it seems that Denodo is doing this. Next I change the base view drv_nav so that the column name is projectname_nav I update the case statement to case WHEN (drv_ibs_actual_byproject_active.projectname is null ) THEN drv_nav_adjustment.projectname_nav ELSE drv_ibs_actual_byproject_active.projectname END I OK and refresh. I look at the case query again for this field and it seems to have accepted the new update. It has not replaced the field drv_nav_adjustment.projectname_nav with drv_ibs_actual_byproject_active.projectname. It's odd that the query will work for a few fields the stop on what seems to be column name. Thanks
23-11-2014 16:45:01 -0500

1 Answer

Hi!! The main problem in your scenario is the "union" operation. Because of that when you try to add a case clause as "Case WHEN (view1.field1 is null) THEN view2.field1 ELSE view1.field1 END" is changed by "Case WHEN (view1.field1 is null) THEN view1.field1 ELSE view1.field1 END". Try to use a join operation instead of an union operation. In this case, the case clause can be used as you want. Hope this helps!
Denodo Team
24-11-2014 10:56:02 -0500
You must sign in to add an answer. If you do not have an account, you can register here