CASE STATEMENT IN ORACLE SQL

Why is this perfectly fine case statement that would work in T-SQL not working in Oracle?
SELECT 
N.HC_PRO_TAX_CODE_1,SC.SPECIALITY || '-' || SC.SPECIALITY_CODE as SCODE, 
CASE WHEN SC.SPECIALITY_CODE IS NOT NULL THEN SC.SPECIALITY || '-' || SC.SPECIALITY_CODE ELSE N.HC_PRO_TAX_CODE_1 END AS HC_PRO_TAX_CODE_1_NEW
FROM VT.TBL_NPI_LKUP R INNER JOIN VT.TBL_CONCUR C ON 
R.CID = C.ID INNER JOIN TBL_NPI N ON R.NPI = N.NPI 
INNER JOIN TBL_MATCH_TYPES M ON R.MATCH_TYPE = M.MATCH_TYPE 
LEFT OUTER JOIN VT.TBL_SPECIALITY_CODES SC ON N.HC_PRO_TAX_CODE_1 = SC.SPECIALITY_CODE
WHERE C.COB = '01-Jan-00' 

Open in new window


1 table is nvarchar2 and the otehr is varchar2. Do I have to cast one of the columns. The join is working without a cast. Is my syntax just off.
Thanks,
Chris
LVL 2
stopher2475Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent DyerIT Security Analyst SeniorCommented:
Slight Mod to your Query..  Just add on END CASE..

SELECT 
N.HC_PRO_TAX_CODE_1,SC.SPECIALITY || '-' || SC.SPECIALITY_CODE as SCODE, 
CASE
 WHEN SC.SPECIALITY_CODE IS NOT NULL THEN SC.SPECIALITY || '-' || SC.SPECIALITY_CODE
 ELSE N.HC_PRO_TAX_CODE_1
 END CASE AS HC_PRO_TAX_CODE_1_NEW
FROM VT.TBL_NPI_LKUP R INNER JOIN VT.TBL_CONCUR C ON 
R.CID = C.ID INNER JOIN TBL_NPI N ON R.NPI = N.NPI 
INNER JOIN TBL_MATCH_TYPES M ON R.MATCH_TYPE = M.MATCH_TYPE 
LEFT OUTER JOIN VT.TBL_SPECIALITY_CODES SC ON N.HC_PRO_TAX_CODE_1 = SC.SPECIALITY_CODE
WHERE C.COB = '01-Jan-00' 

Open in new window

0
stopher2475Author Commented:
I'm getting the error:
ORA-00923: FROM keyword not found where expected
00923. 00000 -  "FROM keyword not found where expected"
*Cause:    
*Action:
Error at Line: 14 Column: 11

It's at the case statement where it says as
CASE
 WHEN SC.SPECIALITY_CODE IS NOT NULL THEN SC.SPECIALITY || '-' || SC.SPECIALITY_CODE
 ELSE N.HC_PRO_TAX_CODE_1
 END CASE AS HC_PRO_TAX_CODE_1_NEW,
0
flow01Commented:
end case is need in pl/sql not in sql

what error message do you get
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

stopher2475Author Commented:
It's right after the ELSE

ORA-12704: character set mismatch
12704. 00000 -  "character set mismatch"
*Cause:    One of the following
           - The string operands(other than an nlsparams argument) to an
           operator or built-in function do not have the same character
           set.
           - An nlsparams operand is not in the database character set.
           - String data with character set other than the database character
           set is passed to a built-in function not expecting it.
           - The second argument to CHR() or CSCONVERT() is not CHAR_CS or
           NCHAR_CS.
           - A string expression in the VALUES clause of an INSERT statement,
           or the SET clause of an UPDATE statement, does not have the
           same character set as the column into which the value would
           be inserted.
           - A value provided in a DEFAULT clause when creating a table does
           not have the same character set as declared for the column.
           - An argument to a PL/SQL function does not conform to the
           character set requirements of the corresponding parameter.
*Action:
Error at Line: 13 Column: 7
0
AshokSr. Software EngineerCommented:
Try this

SELECT
N.HC_PRO_TAX_CODE_1,SC.SPECIALITY || '-' || SC.SPECIALITY_CODE as SCODE,
DECODE(SC.SPECIALITY_CODE, NULL, N.HC_PRO_TAX_CODE_1, SC.SPECIALITY || '-' || SC.SPECIALITY_CODE) AS HC_PRO_TAX_CODE_1_NEW
FROM VT.TBL_NPI_LKUP R INNER JOIN VT.TBL_CONCUR C ON
R.CID = C.ID INNER JOIN TBL_NPI N ON R.NPI = N.NPI
INNER JOIN TBL_MATCH_TYPES M ON R.MATCH_TYPE = M.MATCH_TYPE
LEFT OUTER JOIN VT.TBL_SPECIALITY_CODES SC ON N.HC_PRO_TAX_CODE_1 = SC.SPECIALITY_CODE
WHERE C.COB = '01-Jan-00'
0
stopher2475Author Commented:
That works! Can you explain why?
What's DECODE() and what's wrong with a case statement?
0
AshokSr. Software EngineerCommented:
DECODE() is like SHORT IF statement.

DECODE(SC.SPECIALITY_CODE, NULL, N.HC_PRO_TAX_CODE_1, SC.SPECIALITY || '-' || SC.SPECIALITY_CODE) AS HC_PRO_TAX_CODE_1_NEW

In above, you are saying
IF SC.SPECIALITY_CODE IS NULL THEN
 HC_PRO_TAX_CODE_1_NEW = N.HC_PRO_TAX_CODE_1
ELSE
 HC_PRO_TAX_CODE_1_NEW = SC.SPECIALITY || '-' || SC.SPECIALITY_CODE

HTH
Ashok
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AshokSr. Software EngineerCommented:
DECODE is easier to CODE!
0
flow01Commented:
try changing your nvarchar column with a to_char
is worked for me in the follow test

create table ccc (c1 nvarchar2(10), c2 varchar2(10));
insert into ccc select 'ë','ë' from dual
/

SQL> select c1, c2 , case
  2  when c1 is null then c2
  3  else
  4    c1
  5  end
  6  from ccc
  7  /
  c1
  *
FOUT in regel 4:
.ORA-12704: character set mismatch


SQL> ed
file afiedt.buf is weggeschreven.

  1  select c1, c2 , case
  2  when c1 is null then c2
  3  else
  4    to_char(c1)
  5  end
  6* from ccc
SQL> /

C1         C2         CASEWHENC1ISNULLTHENC2ELSETO_CHAR(C1)END
---------- ---------- ----------------------------------------
ë          ë          ë
0
AshokSr. Software EngineerCommented:
DECODE() is an ORACLE function!
0
sdstuberCommented:
decode is like ms access  switch function

you could use case

just reverse your when and else

 CASE
           WHEN sc.speciality_code IS NULL THEN n.hc_pro_tax_code_1
           ELSE sc.speciality || '-' || sc.speciality_code
       END


the "trick" is both decode and case use the result of the first return value as the data type of the entire expression.
0
flow01Commented:
missed some posts

the result type of a decode is the type of the first used result, the other result values are of another type oracle tries an impliciet conversion

In the case statement such implicit behaviour seems not to be there
0
sdstuberCommented:
In the case statement such implicit behaviour seems not to be there

check the previous post...
case behaves the same way, i.e. returns the type (including character set) of the first result

 in the example decode that worked, the order of the results was reversed, thus changing the implicit type
0
stopher2475Author Commented:
Odd function. Does it count the operators and if the number is even it assumes the last one is a default?
0
AshokSr. Software EngineerCommented:
Could you try this!?

SELECT
N.HC_PRO_TAX_CODE_1,SC.SPECIALITY || '-' || SC.SPECIALITY_CODE as SCODE,
(CASE WHEN SC.SPECIALITY_CODE IS NOT NULL THEN SC.SPECIALITY || '-' || SC.SPECIALITY_CODE ELSE N.HC_PRO_TAX_CODE_1 END) AS HC_PRO_TAX_CODE_1_NEW
FROM VT.TBL_NPI_LKUP R INNER JOIN VT.TBL_CONCUR C ON
R.CID = C.ID INNER JOIN TBL_NPI N ON R.NPI = N.NPI
INNER JOIN TBL_MATCH_TYPES M ON R.MATCH_TYPE = M.MATCH_TYPE
LEFT OUTER JOIN VT.TBL_SPECIALITY_CODES SC ON N.HC_PRO_TAX_CODE_1 = SC.SPECIALITY_CODE
WHERE C.COB = '01-Jan-00'
0
stopher2475Author Commented:
flipping the sequence in the CASE statement still doesn't work. I'm guessing because they're still different data types.
0
AshokSr. Software EngineerCommented:
Odd function. Does it count the operators and if the number is even it assumes the last one is a default?

Yes, but it is very powerful.
Like you said, last value is the DEFAULT.

It can also handle NESTED IF statements within.

HTH
Ashok
0
AshokSr. Software EngineerCommented:
Did you try this?

SELECT
N.HC_PRO_TAX_CODE_1,SC.SPECIALITY || '-' || SC.SPECIALITY_CODE as SCODE,
(CASE WHEN SC.SPECIALITY_CODE IS NOT NULL THEN SC.SPECIALITY || '-' || SC.SPECIALITY_CODE ELSE N.HC_PRO_TAX_CODE_1 END) AS HC_PRO_TAX_CODE_1_NEW
FROM VT.TBL_NPI_LKUP R INNER JOIN VT.TBL_CONCUR C ON
R.CID = C.ID INNER JOIN TBL_NPI N ON R.NPI = N.NPI
INNER JOIN TBL_MATCH_TYPES M ON R.MATCH_TYPE = M.MATCH_TYPE
LEFT OUTER JOIN VT.TBL_SPECIALITY_CODES SC ON N.HC_PRO_TAX_CODE_1 = SC.SPECIALITY_CODE
WHERE C.COB = '01-Jan-00'
0
stopher2475Author Commented:
flow1's method of casting the  N.HC_PRO_TAX_CODE_1 is working:
CASE
           WHEN SC.SPECIALITY_CODE IS NULL THEN to_char(N.HC_PRO_TAX_CODE_1)
           ELSE SC.SPECIALITY || '-' || SC.SPECIALITY_CODE
       END AS HC_PRO_TAX_CODE_1_NEW,

Open in new window


Just putting parentheses around the case statement didn't work. Must be decode is more flexible on the data type.
0
AshokSr. Software EngineerCommented:
In that case, I would use DECODE.  No need for casting!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.