Jeff Kish wrote:
> <snip>
>
>>>Is there someway of getting a handle on this in a where clause?
>>>
>>>Here is the where clause which works now roughly for boss rows: (:id,
>>>:forcity, :forstate) comes from the input data row)
>>>
>>>id = :id and stateid = ( case when (:forcity is null) then ( case when
>>>(:forstate is null) then ( select stateid from table2 where ismain = 1 and id
>>>= :id) else :forstate end) else (select stateid from citytable where cityid =
>>>:forcity) end)
>>>
>>>I'd like to modify it so that it somehow can check of a certain other row has
>>>a null value instead of the current rows supplied value.. sort of like this in
>>>pseudo sql-eze:
>>>
>>>id = :id and stateid =
>>>( case when (:id = :boss) then
>>>(
>>>( case when (:forcity is null) then
>>>( case when (:forstate is null) then ( select stateid from table2 where ismain
>>>= 1 and id = :id) else :forstate end) else (select stateid from citytable
>>>where cityid = :forcity) end)
>>>)
>>>else
>>>(
>>>( case when ((select forcity from table1 where team = :team and id = :id and
>>>id = boss) is null) then
>>>( case when ((select forstate from table1 where team = :team and id = :id and
>>>id = boss) is null) then ( select stateid from table2 where ismain = 1 and id
>>>= :id) else :forstate end) else (select stateid from citytable where cityid =
>>>:forcity) end)
>>>)
>>>end)
>>>
>>>
>>>
>>>gasp. Thanks for even some hints about if this is possible.
>>
>>http://www.psoug.org
>>click on Morgan's Library
>>click on DECODE (CASE is on the same page)
>
> Thanks.
> I'm not quite sure how to apply the information.
> I probably did not ask my question with enough clarity?
> Is it possible (like above) to put something like this piece into a where
> clause? I'm trying to specify the value by checking some other rows in the
> where clause...
>
> ((select forstate from table1 where team = :team and id = :id and
> id = boss) is null)
>
> You may of told me how to do it in the example you referenced, but I'm still
> struggling a bit with it.
>
> again,
> Thanks
Your description is completely confusing to me. What does this mean?
"I'm trying to specify the value by checking some other rows in the
where clause..."
What other rows? How?
Take a look at the MODEL clause in 10g.
--
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)
Received on Fri Jul 15 2005 - 22:53:36 CDT
