SQL & PL/SQL

Hello,

I'm trying to implement a somewhat tricky query here (atleast from my perspective).

Basically I'm trying to use a non-searched case statement against a connect by prior. The reasoning for this is I want to avoid having to run the query more than once to test conditions. The connect by prior is more like a reverse way of doing it, in that I start w/ the child and drill 'up' to find the root node, rather than drilling down.

case when TRIM(SOD.PART) is not null then TRIM(SOD.SO_NBR) else
case (select TRIM(SUBSTR(max(level||ASSY_WO_NBR), 2))
from WDS19.OPD_TABLE
start with WO_NBR = CSD.DMD_ASSY_WO_NBR
connect by nocycle prior ASSY_WO_NBR = WO_NBR)
when like 'B%'
then (select TRIM(SUBSTR(max(level||ASSY_WO_NBR), 2))
from WDS19.OPD_TABLE
start with WO_NBR = CSD.DMD_ASSY_WO_NBR
connect by nocycle prior ASSY_WO_NBR = WO_NBR)
when is not null
then (select TRIM(SUBSTR(max(level||ASSY_WO_NBR), 2))
from WDS19.OPD_TABLE
start with WO_NBR = CSD.DMD_ASSY_WO_NBR
connect by nocycle prior ASSY_WO_NBR = WO_NBR)
else
case when trim(csd.dmd_assy_wo_nbr) is not null then csd.dmd_assy_wo_nbr else
'Excess'
end
end
end End_Item_Order

However this doesn't work due to the LIKE operand so far I can tell, or I have the syntax wrong. Am I going to have to set this up as a searched case statement like my old method used below?

Here is my old method:
case
when (<query>) like 'B%' then ...
when (<query>) is not null then ...
when (<query>) is null then ...
else ' ' end

What I'd like to do:
case (<query>)
when like 'B%' then...
when is not null then ...
when is null then...
etc.

I did this the old way and it works but....there are just so many executions of that query for a single record and I'm trying to avoid that. Been trying to find if I can use things like 'LIKE' and 'IS NOT NULL' etc in non-searched case statements but haven't found much. Just wanting to do a single query call against multiple conditions.

I hope that makes sense :)

Edited by: dvsoukup on Apr 26, 2012 9:37 AM