Case in SELECT

Database
Kks
Enthusiast

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
5 REPLIES
Senior Apprentice

Re: Case in SELECT

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
Kks
Enthusiast

Re: Case in SELECT

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_TBL
Where TRN_NM = '0066563438'
Senior Apprentice

Re: Case in SELECT

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 CASE

SELECT 'blabla' AS databasename
FROM dbc.databases
WHERE databasename = 'dbc';

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

databasename
------------
blabla

Dieter
Kks
Enthusiast

Re: Case in SELECT

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
CASE
WHEN 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 ,
Senior Apprentice

Re: Case in SELECT

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

Dieter