usage of CASE statement

[vensriram]

Hi,
I am trying to use the CASE statement in select...But i am getting errors...My query is like

select 
Case CHARINDEX(':',columnname)
When 0
THEN'columnname'
ELSE
SubString('columnname',CHARINDEX(':','columnname')-1,LEN('columnname'))
END
as column1
from table1
where column1= 'xyz'

For the above i am getting an error ORA00904(Invalid column name). Kindly shed some light on the usage of case statement in the column name..
The logic behind the code is i have a column in which i may get either constant or a value with delimiters like a.b.c:d(length is not constant). So if it is a constant i will fetch the whole value...if it is a value with delimiter i will take the character from the character which is previous to the ":"

[Rabbit]

Why is your columnname field surrounded by single quotes?

[debasisdas]

CHARINDEX is not a inbuilt function is Oracle, it is used in Ms Sql Server. The corresponding Oracle function is INSTR.

[vensriram]

@rabbit:-u r right.......it should not be surrounded by '.
this statement

select CHARINDEX(':',name) from oracle

is working fine, but if i put the same inside the case like the below

Select
Case CHARINDEX(':',name)
When 0 --Type1
THEN name
ELSE
SubString(name,CHARINDEX(':',name)-1,LEN(name))
END
as column1

i am getting an error saying name is an invalid column name.......



@debasisdas:- ok thanks will try it............