Re: where clause

Jeff Kish wrote:
> Hi.
>
> I'm very sorry if this is not clear, but it is complicated to me.
> basically I am not sure if what I want to do is possible in sql, and perhaps
> if it is someone can tell me how to do this.
>
>
>
> I'm working on a where clause that gets used by a process.
> The process gets rows at runtime, and fills in data into the where clause to
> figure out what rows to return to whatever invoked it.
>
> I know that it uses data from table1 to return rows from table2.
>
> table1
> boss id team forstate forcity
> p1 p1 one cal sd
> p1 p2 one null null
> p1 p3 two null null
>
> table2
> id stateid ismain col4
>
>
> I have a where clause that seems to implement business rules most of the time,
> but if the row being processed represents a non-boss (boss != id) then I need
> to word the where clause so it checks if the forstate and forcity values are
> null in the boss row, not the non boss row whose values are currently being
> evaluated.
>
> 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)

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)