Great response and very quick.

Thank you so much.

Larisa

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

  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

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')

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?

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..

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'

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

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

CASE a+b+c+d+e As abcde

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!

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

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.

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;

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;

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