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