Larisa Gassel, February 09, 2007 - 10:18 am UTC
Great response and very quick.
Thank you so much.
Larisa
SELECT within case
reader, February 11, 2010 - 4:01 am UTC
Hi Tom
Sorry to re-use the same thread for this question but I think it's relevant to the original question.
Is it possible to use a SELECT statement within case
For ex,
SELECT
CASE WHEN A1.COL1=B1.COL1 THEN SELECT A1.COL1, B1.COL1 FROM A1, B1 WHERE A1.COL1=B1.COL1
ELSE
SELECT A1.COL1, C1.COL1 FROM A1,C1 WHERE A1.COL1=C1.COL1
END
FROM A1,B1,C1;
That is if A1.col1 matches B1.col1 then select from A1 and B1 and if not select from A1 and C1
Thanks
February 16, 2010 - 9:35 am UTC
You can use a SCALAR SUBQUERY - a query that returns zero or one rows with exactly one column. So this will work:
1* select case when dummy = 'X' then (select count(*) from all_users) else (select count(*) from dual) end cnt from dual
ops$tkyte%ORA10GR2> /
CNT
----------
46
if you want more than one column, you would use an object type - making is become "one column" with many attributes:
ops$tkyte%ORA10GR2> select case when dummy = 'X' then (select myType(user_id,created) from all_users where rownum=1) else (select myType(111,sysdate) from dual) end
2 from dual;
CASEWHENDUMMY='X'THEN(SELECTMYTYPE(USER_ID,CREATED)FROMALL_USERSWHEREROWNUM=1)EL
--------------------------------------------------------------------------------
MYTYPE(0, '30-jun-2005 19:10:18')
In Operator in case statement
Jitendra, June 28, 2011 - 7:39 am UTC
Hi Tom,
could you please suggest for the following expression used in a where clause of a query:
how to hanlde the IN clause scenarios for case expression..
pd.prod--column in a table..
v_prod_list_count -- variable which is storing count of array list (v_prod_list)
AND pd.prod in
(CASE WHEN v_prod_list_count <> 0
THEN (SELECT * FROM TABLE(v_prod_list))
END
)
i am getting the error as ORA-01427: single-row subquery returns more than one row
i know that is coming becuase of v_prod_list as subquery..
is there any bypass?
June 28, 2011 - 12:05 pm UTC
I *think* you just want to do
and ( v_prod_list_count = 0 OR pd.prod in (select * from table(v_prod_list)) )
Your use of CASE confounds me, I'm not sure what you are trying to do there? I'm not sure what predicate you are looking for really - I think I might have guessed right.
But you should tell us WHAT you are trying to do, not HOW you are trying to do it. We need to know the WHAT, not your incorrect HOW.
In Operator in case statement
Jitendra Jalota, June 29, 2011 - 1:37 am UTC
Hi Tom,
Thanks a ton for your reply.
what i am trying to achive is for example:
i am using 4 nested array list..while using those array list in where clause if any of the array list is empty so the query should handle the null scenario/count zero scenario..
for your information
list are stored in a table as below without single quote..
a,b,c,d
and restored in nested table type through a splitter function and used as select * from table (array list )
i treid and implemented the suggested solution by you..but there is much differnce in performance time ..its increased now..
June 29, 2011 - 12:06 pm UTC
I need an example to work with.
uses of case
Ramesh Goutam, July 14, 2011 - 1:44 am UTC
i wrote the following query:
SELECT DISTINCT a.CONTAINER_EVENT_ID,
a.container_number,
d.carrier_reference bn,
a.carrier_company_id carrier,
a.event_type,
a.event_type_code,
a.event_date,
a.revision_date,
a.transport_name,
a.conveyance_number,
e.VALUE BL_NUMBER
FROM iwh.container_event a,
iwh.booking_event_link b,
iwh.booking_current c,
iwh.booking d,
iwh.container_event_reference e
WHERE a.first_container_event_id = b.first_container_event_id
AND b.original_booking_id = c.original_booking_id
AND b.ORIGINAL_BOOKING_ID=d.ORIGINAL_BOOKING_ID
AND a.CONTAINER_EVENT_ID=e.CONTAINER_EVENT_ID
AND upper(e.REFERENCE_TYPE)=upper('Bill of Lading')
AND a.container_number in ()
AND d.carrier_reference in ()
ORDER BY 1 DESC
But not work properly,
Atually i want to display value of 'e.VALUE' column from table 'iwh.container_event_reference e' if some values are present with other fields of other table and if there is no any values though want to display other field's values of other table. And this table is joined as 'a.CONTAINER_EVENT_ID = e.CONTAINER_EVENT_ID'
July 15, 2011 - 9:03 am UTC
just add:
case when <if some values are present with other fields> then e.value
else other_field's_values
end
to your select list.
Use of Case statement to repalce multiplie charcters
Haribabu Konakanchi, July 15, 2011 - 8:21 am UTC
SELECT segment1, CASE
WHEN instr(segment1,',') >0 THEN replace(segment1,',',':')
WHEN instr(segment1,';') > 0 THEN replace(segment1,';',':')
ELSE SEGMENT1
END
"New Sales"
FROM mtl_item_locations
July 18, 2011 - 9:36 am UTC
I guess if want to do that - that is fine, it doesn't answer a single question asked on this page, but whatever??
Hopefully you know that it either
a) replaces 1 or more occurrences of ',' with ':'
OR
b) replaces 1 or more occurrences of ';' with ':'
OR
c) does nothing.
It doesn't replace multiple characters if you mean by multiple characters "replace ',' and ';' with ':'"
that would simply be:
replace( replace( segment1, ',', ':' ), ';', ':' )
Case statement with a complex expression
A reader, February 26, 2021 - 3:50 pm UTC
Is there an easier way to do the above ... let me explain
-- Works good
WITH x AS
( SELECT level+1 a,level+2 b,level+3 c,level+4 d,level+5 e FROM dual CONNECT BY level <= 10)
SELECT CASE
WHEN (a+b+c+d+e <30)
THEN 'Below 10'
WHEN (a+b+c+d+e <60)
THEN 'Below 20'
ELSE
'Above'
END
FROM x;
-- But why can't I write it like
WITH x AS
( SELECT level+1 a,level+2 b,level+3 c,level+4 d,level+5 e FROM dual CONNECT BY level <= 10)
SELECT CASE a+b+c+d+e As abcde
WHEN (abcde <30)
THEN 'Below 10'
WHEN (abcde <60)
THEN 'Below 20'
ELSE
'Above'
END
FROM x;
I understand I can do cascaded selects and it would work but anyway I can do the above in 1 select onlu
February 26, 2021 - 4:51 pm UTC
You can't alias the expression in the case statment like this:
CASE a+b+c+d+e As abcde
It's invalid syntax.
My bad
A reader, March 10, 2021 - 6:01 am UTC
My bad!! I understand that I cannot use it that way but why not ... any technical limitations!!
Also if I replicate the same expression in multiple CASE statements would it be evaluated only once or multiple times?? And how can I confirm that its being evaluated only once!
March 11, 2021 - 2:54 am UTC
The expression only has to be listed once, eg
SQL> select
2 case 1+3+4
3 when 5 then 12
4 when 6 then 13
5 when 8 then 15
6 end x
7 from dual;
X
---------------------------------------------
15
Let me explain again ..
A reader, March 11, 2021 - 4:38 am UTC
thanks for your response!!
That's possible when we have the following -
WITH x AS
( SELECT level+1 a,level+2 b,level+3 c,level+4 d,level+5 e
FROM dual
CONNECT BY level <= 10
)
SELECT CASE a+b+c+d+e
WHEN 30
THEN 30
WHEN 60
THEN 60
ELSE
'Above'
END
FROM x;
How do I manage "<=" or ">=" or a check for NULL? As far as I know
WITH x AS
( SELECT level+1 a,level+2 b,level+3 c,level+4 d,level+5 e FROM dual CONNECT BY level <= 10)
SELECT CASE a+b+c+d+e
WHEN <30
THEN 'Below 30'
WHEN <60
THEN 'Below 60'
WHEN IS NULL
THEN 'NULL'
ELSE
'Above'
END
FROM x;
would fail with "ORA-00936: missing expression". Right??
So my original question was
1) Can the syntax be more clear??
- why not make the syntax easier (by allowing an alias or something else)?
- Is there any other alternative wherein I don't have to do WHEN (a+b+c+d+e <30) in each CASE statement?
2) How it affects the performance??
- If i multiple CASE statements like
WHEN (a+b+c+d+e <30)
WHEN (a+b+c+d+e <50)
WHEN (a+b+c+d+e <60)
would Oracle evaluate "a+b+c+d+e" for each WHEN statement or it would evaluate "a+b+c+d+e" only once.
March 11, 2021 - 9:11 am UTC
You can either do the calculation in the X subquery or add another named subquery to do it:
with x as(
select level+1 a,level+2 b,level+3 c,level+4 d,level+5 e from dual connect by level <= 10
), y as (
select x.*, a+b+c+d+e sm from x
)
select sm, case
when sm <30
then 'below 30'
when sm <60
then 'below 60'
when sm is null
then 'null'
else
'above'
end
from y;
This works
A reader, March 11, 2021 - 8:23 am UTC
WITH x AS
( SELECT level+1 a,level+2 b,level+3 c,level+4 d,level+5 e FROM dual CONNECT BY level <= 10)
SELECT a+b+c+d+e, CASE
WHEN a+b+c+d+e <30
THEN 'Below 30'
WHEN a+b+c+d+e <60
THEN 'Below 60'
WHEN a+b+c+d+e IS NULL
THEN 'NULL'
ELSE
'Above'
END
FROM x;
Thanks for the response!!
A reader, March 12, 2021 - 8:18 am UTC
Appreciate the quick reverts!! I know how to do it ....
Problem -
I already have multiple selects say like ...
select * from (select * from( select * from ...)
and my point is that there are a lots of scenarios wherein I have to write nested subqueries or subquery factoring where it may not be required.
And that leads to a query looking more complex than it should be
March 15, 2021 - 5:04 am UTC
Look at using the WITH clause