AppletTalk.com

Jeff Kish · Guest

Sorry if this is a murky question...

I'm working with a custom frameworks. Given what I have to work with,
I need to come up with a where clause that the frameworks can use to
return some rows from a second table given a row of data from the first table.

So basically I need to supply a where clause that at runtime gets
turned into real sql for Oracle (later ms sql).

The frameworks is filling in some data from a 'parent' row, and using the
where clause to select from a second table (schedulepeople) to populate some
screen realestate..

I am trying to figure out if it is possible to 'word' the where clause so that
if the current parent row is not good enough, I can somehow get data from
another row for the where clause variables.

'parent' table:
boss person, state, forcity, forstate team
p2 p1 cal null null maint
p2 p2 cal null null maint
p3 p2 cal null null elec

So, for example if I put this in as a where clause:

id = :person and state = ( case when (:forcity is null) then ( case when
(:forstate is null) then ( select state from schedulepeople where ismain = 1
and id = :person) else :forstate end) else (select state from citytable where
cityid = :forcityid) end)

then the rows returned from the second table need to have an id equal to
whatever the frameworks has put into :person at runtime.

Also the rows returned from the second table need to have a state equal to a
calculated value as seen above (i.e. if the parent row has null value in
:forcity, but not in :forstate, then the rows have a state value equal to
:forstate of the parent/current row the frameworks is looking at.

The problem is, the business rules say if a person is not a boss, that I need
to check the :forcity and :forstate of the boss (indicated where boss =
person) for that team.

So if the frameworks is looking at parent row where person = p2, the above
clause works properly, but if the frameworks is looking at a parent row where
person = p1, then it needs to do the same logic, but somehow stuff in the
bosses :forcity and :forstate values in the where clause.

Any ideas if this is possible, and if so, how to go about it? It appears
pretty complex, and I don't know if some variety or combination of
nvl/select/case will do it or not.

I assume it will look something like this:

id = :person and state =

(case when (:person = :boss) then
(
( case when (:forcity is null) then ( case when (:forstate is null) then (
select state from schedulepeople where ismain = 1 and id = :person) else
:forstate end) else (select state from citytable where cityid = :forcityid)
end)
)
else
(
something complex goes in here
)
end)