Error when concatenating with a case statement

Question

Post reply

Error when concatenating with a case statement

jjprachyl

SSC-Addicted

Points: 409
More actions
April 21, 2010 at 3:33 pm

#97032

Select DISTINCT ACCT.ACCTNBR||','||
CASE PERSPERSTYP.PERSTYPCD when 'EMP' then 'EMP' else null end as type||','|| PERSPHONE.AREACD||PERSPHONE.EXCHANGE||PERSPHONE.PHONENBR as CELLPHONE
FROM ACCT,PERSPERSTYP,PERSPHONE WHERE (UPPER(PERSPERSTYP.PERSTYPCD) IN ('BDM','CMC','CUST','OTC','TRST','UTMA')) AND ((ACCT.TAXRPTFORPERSNBR = PERSPERSTYP.PERSNBR)) AND ((ACCT.TAXRPTFORPERSNBR = PERSPHONE.PERSNBR(+)) AND (UPPER(PERSPHONE.PHONEUSECD(+)) = 'CELL'));
With this query, I can't seem to return results. Whenever I try to concatenace with a , after the CASE it returns the following:
ERROR at line 2:
ORA-00923: FROM keyword not found where expected
Help! and Thanks in advance!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Are you sure you're at the right forum?

Error message looks like some Oracle stuff...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Lowell SSC Guru Points: 323518 More actions · Answer 2

when you concatenate, you cannot alias the CASE statement...right at the end of your case you have this:

end as type||','||

also you have a logic hole: null concated with anything will be null, so this part is bad, as you'll get NULL results:

when 'EMP' then 'EMP' else null end

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jjprachyl SSC-Addicted Points: 409 More actions · Answer 3

Yes, its SQL and an error stemming from my understanding of the "Case" statement or lack thereof. Its ignoring my FROM statement because I'm using the ||','|| after the case statement, and I don't know why.

Lowell SSC Guru Points: 323518 More actions · Answer 4

as Lutz posted, both the syntax you used and the error you posted are for Oracle only; SQL server uses the plus + operator to concatenate strings:

Here is the equivilent SQL, with Left Outer join to replace the Oracle (+) operator

Select DISTINCT

ACCT.ACCTNBR

+ ','

+ CASE PERSPERSTYP.PERSTYPCD

when 'EMP'

then 'EMP'

else null

end

+ ','

+ PERSPHONE.AREACD

+ PERSPHONE.EXCHANGE

+ PERSPHONE.PHONENBR as CELLPHONE

FROM ACCT

INNER JOIN PERSPERSTYP

ON ACCT.TAXRPTFORPERSNBR = PERSPERSTYP.PERSNBR

LEFT OUTER JOIN PERSPHONE

ON ACCT.TAXRPTFORPERSNBR = PERSPHONE.PERSNBR

WHERE (UPPER(PERSPERSTYP.PERSTYPCD)

IN ('BDM','CMC','CUST','OTC','TRST','UTMA'))

AND (UPPER(PERSPHONE.PHONEUSECD) = 'CELL');

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jjprachyl SSC-Addicted Points: 409 More actions · Answer 5

Lowell,

I removed the alias and it worked perfectly. On to the union now....

Thank you so much for looking at this!