Archives of the TeradataForum

Message Posted: Mon, 21 Dec 2009 @ 20:32:02 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi Victor,

I am not able to give out the DDLs or query text. However, I can tell you this much that when I use the case statement and access directly from the tables the query works fine and optimizer is using join index.

However, if I run the same query from the view, which does nothing except putting that case statement, the optimizer stops using that join index. Is there any documentation available on this?

Also as I mentioned earlier the same column(PRIVACY_CD) is present in multiple tables, so in order to create the join index I had to use an alias in the select list. So it is giving the problem in the table where I used the alias. Maybe the optimizer doesn't recognize from which table it needs to actually bring the column from?

The following is the case causing the problem:

     ,CASE
         WHEN AK.PRIVACY_CD = ' ' THEN AK.APP_ACCNO
         ELSE 'PRIVATE'
        END
     ,CASE
         WHEN AK.PRIVACY_CD = ' ' THEN AK.PIGGYBACK_ACCNO
         ELSE 'PRIVATE'
      END

I hope I am clear please let me know if you need any more information.

Thanks & Regards

Mohammad Usman Baig

	<Prev	Next>		<<First	<Prev	Next>	Last>>