Simple PL/SQL question 1

[dvader0571]

Is the CASE statement supported or not in 9i? I keep getting a "Missing right parenthesis" error when trying to run this. I thought CASE was ANSI SQL and thus supported....

SELECT SRVCHGKEY, SRVCHG, ACCTTYPE, BLTYPEKEY, BLITEMGRP, EFFDATE, EXPDATE, PRNORDER, CALCORDER, PRNTTEXT, REFTEXT, FVALKEY, BLITEMRATE, FPNLTYKEY, FINTKEY, DEPOSIT, DISCOUNT, PRNCPAYORD,
(CASE WHEN BGTNOOFLAG = 'Y' AND (SELECT BGTNO FROM imsv7.ACCTCHG WHERE ACCHGKEY = 1) IS NOT NULL
THEN (SELECT BGTNO FROM imsv7.ACCTCHG WHERE ACCHGKEY = 1) ELSE PRNCCBGTNO END) As PRNCCBGTNO,
PRNCPBGTNO, PENPAYORD, PENCBGTNO, PENPBGTNO, ARNGFLAG, DUNEXEMPT, DONATION, PENEXEMPT, BGTNOOFLAG
FROM imsv7.SRVCHGSU
WHERE SRVCHGKEY = 1;

 

[LKBrwnDBA]

CASE is supported, but not the embedded subquery: ..."AND (SELECT BGTNO FROM ...)"


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

Darth,

Your code is solid. I successfully simulated your code syntax below. If you wish to replicate the proof-of-concept, then save the code to a file and run it as as script (since there is an "accept/prompt" line). BTW, in my simulation, I accessed v$instance, which I hope you have access to.):

accept BGTNOOFLAG prompt "Enter 'Y' or non-'Y': "
col a heading "Host|Name" format a20
select instance_name, (case when '&BGTNOOFLAG' = 'Y'
             and (select dummy BGTNO from dual where rownum = 1) is not null
	     then (select dummy  BGTNO from dual where rownum = 1)
             else 'Input = &BGTNOOFLAG' end) As PRNCCBGTNO,
	host_name a
from v$instance;

@tt_181
Enter 'Y' or non-'Y': Y

                           Host
INSTANCE_NAME    PRNCCBGTN Name
---------------- --------- --------------------
dhunt            X         TFMOBILE2709

@tt_181
Enter 'Y' or non-'Y': n

                           Host
INSTANCE_NAME    PRNCCBGTN Name
---------------- --------- --------------------
dhunt            Input = n TFMOBILE2709

I recommend your troubleshooting your code by cutting down your SELECT to just the CASE clause and "detecting" from there.

Let us know your findings/results.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 21:41 (03Dec04) UTC (aka "GMT" and "Zulu"),
@ 14:41 (03Dec04) Mountain Time

 

[dvader0571]

I am primarily a C++ programmer and have not worked in too much detail with PL/SQL, however some of our clients use Oracle, others use SQL Server, so this needs to work for both.

I do not have much knowledge about the v$instance, nor will I be able to manipulate DB settings at all or use script (this sql is dynamic in our C++ program).

I would prefer to have SQL manage this check to determine if the BGTNOOFLAG = Y use the BGTNO field from ACCTCHG otherwise use the default PRNCCBGTNO from the SRVCHGSU table.

To bad sub-selects don't work in CASE statements for Oracle, I may just have to do a simple table join and check the values in C++.

<sigh> thank you all for answering my question however, I appreciate it!

 

[SantaMufasa]

Darth,

Too bad sub-selects don't work in CASE statements for Oracle

My proof-of-concept example shows that sub-SELECTs do work in Oracle CASE. My example successfully does syntactically what your query was trying to do. To help you troubleshoot your problem, and knowing that you are a C++ programmer and might not have access to v$instance, I've re-written another proof-of-concept query that syntactically exercises code that matches your original construct and does so with tables to which you have permission and access:

accept BGTNOOFLAG prompt "Enter 'Y' or non-'Y': "
col a heading "Default|Tablespace" format a20
select username, (case when '&BGTNOOFLAG' = 'Y'
             and (select dummy BGTNO from dual where rownum = 1) is not null
	     then (select dummy  BGTNO from dual where rownum = 1)
             else 'Input = &BGTNOOFLAG' end) As PRNCCBGTNO,
	default_tablespace a
from user_users;

SQL> @tt_181
Enter 'Y' or non-'Y': Y

                                         Default
USERNAME                       PRNCCBGTN Tablespace
------------------------------ --------- ----------
DHUNT                          X         DATA1

@tt_181
Enter 'Y' or non-'Y': N

                                         Default
USERNAME                       PRNCCBGTN Tablespace
------------------------------ --------- ----------
DHUNT                          Input = N DATA1
*******************************************************************************

The above code uses a sub-query, similar to yours inside a CASE statement. This code should work fine on any version of Oracle back to Oracle 8i. If you do not have access to SQL*Plus, from which I successfully ran the above code, then you should be able to run it successfully from C++, by removing the "accept" and "col" (SQL*Plus commands), and replace the "&BGTNOOFLAG" references with whatever variabloe from C++ that you'd like.

The reason I'll following up is because it would be a shame to give up on code that should work...I've shown it works, above. Please try it again from your C++ environment and and hang tough with it until we can get it tweaked to work.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 23:30 (03Dec04) UTC (aka "GMT" and "Zulu"),
@ 16:30 (03Dec04) Mountain Time

 

[smbpalepilsen]

I have seen your syntax, the CASE immidiately followed by WHEN. Try this

Syntax:
CASE expr WHEN comparison_expr1 THEN return_expr1
WHEN comparison_expr2 THEN return_expr2
ELSE else_expr
END

Ex: SELECT last_name, job_id, salary,
CASE job_id WHEN 'IT_PROG' THEN 1.10*salary
WHEN 'ST_CLERK' THEN 1.15*salary
ELSE salary END "REVISED SALARY"
FROM employees;

hope this will help.
Regards,
OG

 

[SantaMufasa]

OG,

I hate to be a nay-sayer, but I cannot locate an example of the syntax you are suggesting. Additionally, I have just tested your suggested syntax and it doesn't work on my 9.2.0.4 installation. Could you please compose a sample of your syntax, successfully test it on your Oracle instance, then post a copy-and-paste of the code and the results here on this thread?

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 15:59 (05Dec04) UTC (aka "GMT" and "Zulu"),
@ 08:59 (05Dec04) Mountain Time

 

[smbpalepilsen]

Mufasa,
I cut and paste the example I have posted into the sql prompt and here is the result. FYI, DECODE command can be use also and will produce thesame reult. Hope this will help.

Regards,
OG


SQL> SELECT last_name, job_id, salary,
2 CASE job_id WHEN 'IT_PROG' THEN 1.10*salary
3 WHEN 'ST_CLERK' THEN 1.15*salary
4 ELSE salary END "REVISED SALARY"
5 FROM employees;

LAST_NAME JOB_ID SALARY REVISED SALARY
------------------------- ---------- ---------- --------------
King AD_PRES 24000 24000
Kochhar AD_VP 17000 17000
De Haan AD_VP 17000 17000
Hunold IT_PROG 9000 9900
Ernst IT_PROG 6000 6600
Lorentz IT_PROG 4200 4620
Mourgos ST_MAN 5800 5800
Rajs ST_CLERK 3500 4025
Davies ST_CLERK 3100 3565
Matos ST_CLERK 2600 2990
Vargas ST_CLERK 2500 2875

LAST_NAME JOB_ID SALARY REVISED SALARY
------------------------- ---------- ---------- --------------
Zlotkey SA_MAN 10500 10500
Abel SA_REP 11000 11000
Taylor SA_REP 8600 8600
Grant SA_REP 7000 7000
Whalen AD_ASST 4400 4400
Hartstein MK_MAN 13000 13000
Fay MK_REP 6000 6000
Higgins AC_MGR 12000 12000
Gietz AC_ACCOUNT 8300 8300

20 rows selected.

SQL>

 

[dvader0571]

Mufasa,
Thanks it does work! By the way, your "when rownum = 1" is that the way to get only the top row of your query to be returned? The "SELECT TOP 1 * FROM Table" syntax T-SQL supports is what I am familiar with.

Thanks again!

 

[SantaMufasa]

Darth,

Correct..."...WHERE rownum = 1" is Oracle SQL's method of working with just the first row returned from the otherwise-entire result set.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 22:50 (06Dec04) UTC (aka "GMT" and "Zulu"),
@ 15:50 (06Dec04) Mountain Time