SQL, Oracle NULL query

Question

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

Aleksej · Accepted Answer · 2019-10-25 09:44:56Z

1

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.

answered Oct 25, 2019 at 9:44

Aleksej

23.1k6 gold badges38 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Littlefoot · Accepted Answer · 2019-10-25 09:45:03Z

0

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

answered Oct 25, 2019 at 9:45

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Collectives™ on Stack Overflow

SQL, Oracle NULL query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related