CODEWITHSUNDEEP
sqloracle-database
Phil07952
Phil07952

Reputation: 7

SQL, Oracle NULL query

I have this bit of code in my query, when I remove it then it works fine, if I keep it in I get a

"ORA-00908: missing NULL keyword"

message. 

CASE WHEN pp.phone_number is '0' THEN ''

I have other When - Then and an END statement in there so it's not that.

The whole code, in case you wanted to see is:

CASE WHEN pp.country_code_number = 44 THEN '0'||SUBSTR(REGEXP_REPLACE(pp.phone_number, '[^0-9]+', ''),-10) 
                WHEN pp.country_code_number IS NULL THEN '0'||SUBSTR(REGEXP_REPLACE(pp.phone_number, '[^0-9]+', ''),-10) 
                WHEN pp.phone_number is '0' THEN ''
                WHEN pp.phone_number is NULL THEN 'Blank'
                ELSE '***ERROR***'
                END

Thanks all

Upvotes: 0

Views: 322

Answers (2)

Littlefoot
Littlefoot

Reputation: 142713

From what you posted so far, should be =, not is:

case when pp.phone_number = '0' then ''

is is used for NULLs, e.g. is null or is not null

Upvotes: 0

Aleksej
Aleksej

Reputation: 22949

IS is only used to check NULL; for example

CASE WHEN pp.phone_number IS NULL THEN …

What you need is:

CASE WHEN pp.phone_number = '0' THEN ''
…

As an aside, you are using a string ('0') that looks like a number; maybe you need to check your types.

Upvotes: 1

Related Questions