All Forums Database
Kks 128 posts Joined 03/06
30 May 2008
Case in SELECT

Hi,I have a query which uses CASE on the a indexed column, the bad part is it ignores the index ,is there any way I can force index on it Thanks

dnoeth 4628 posts Joined 11/04
01 Jun 2008

Any calculation based on an indexed column will not use any index, you have to rewrite it as a SARGable condition:e.g. "indexed_col + x = y" -> "indexed_col = y - x"Could you post that CASE?Dieter

Dieter

Kks 128 posts Joined 03/06
02 Jun 2008

here is the sql Select CASE WHEN SUBSTR(TRN_NM,13,1) = ' ' THEN TRN_NM ELSE SUBSTR(TRN_NM,1,6) || '******' || SUBSTR(TRN_NM,13,4) END TRN_NM From TRNS_TBLWhere TRN_NM = '0066563438'

dnoeth 4628 posts Joined 11/04
02 Jun 2008

You can't use an index for your case statement, because it can't be rewritten and i doubt, that any DBMS will use an index for that.Anyway, your example should use an index, because renaming a calculated column to the original columnname is bad practice:"Where TRN_NM = '0066563438'" uses the base column TRN_NM, but not the result of the CASESELECT 'blabla' AS databasenameFROM dbc.databasesWHERE databasename = 'dbc'; *** Query completed. One row found. One column returned. *** Total elapsed time was 1 second.databasename------------blablaDieter

Dieter

Kks 128 posts Joined 03/06
03 Jun 2008

Thanks for reply ,it is now using index , but if I cover it up with another select layer then it doesn't e.g.Select * from (Select CASEWHEN SUBSTR(TRN_NM,13,1) = ' ' THEN TRN_NM ELSE SUBSTR(TRN_NM,1,6) || '******' || SUBSTR(TRN_NM,13,4) END TRN_NM1 From TRNS_TBL)Where TRN_NM1 = '0066563438' can you suggest something ,

dnoeth 4628 posts Joined 11/04
03 Jun 2008

No DBMS i'm aware of will be able to use an index for that case statement, sorry.Dieter

Dieter

You must sign in to leave a comment.