where clause

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.   Received on Fri Jul 08 2005 - 16:23:50 CDT