Re: where clause

From: Jeff Kish <jeff.kish_at_mro.com>
Date: Mon, 11 Jul 2005 09:16:14 -0400
Message-ID: <98s4d1t814tj9o8unnnsti43tqrvjtiibh@4ax.com>

<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 Received on Mon Jul 11 2005 - 08:16:14 CDT