correlated sub query for calculated field

[luciffer]

I need to query and get all clients that match the search parameters, but i also need to get the client status (a calculated field). Each client can have many BNs, each BN can have many Applications, but only one Application per year. The client status is based on the latest application status of all its BNs.

A client is active (1) if it has at least one BN that is active, otherwise the client is inactive (0). A BN is active if it has an application with status of anything but 14 or 15 for its last application year.

CLIENT (client_id, last_name, first_name)
BN (bn_id, client_id, bn_number)
APPLICATION (application_id, application_year, bn_id, application_status_id)


Any help or suggestions?

[LKBrwn_DBA]

And you expect us to do the job for you?

Have you tried anything yet?

Hint: Divide and conquer.

1) Write query to get 'Active' BN's
2) Add to query that gets clients.

[luciffer]

As the title suggests i tried a correlated sub-query, but it looks like the scope of the outside client_id is a problem and the performance wasn't great. Since this is for the main search of an application I was looking for some other suggestions. I figured if i threw up my code, i'd just get variations of it instead of some better ideas.

This is what i tried. The subquery to get the client_status_ind works (assuming anything > than 0 is an 'ACTIVE' client) if i give it a client_id. But when i use it as a correlated sub query i get an error that the "client.client_id" is not valid (on line 13 in the query). Because i get this error, i extracted the sql to get the status and put it in another spoc that takes a client_id as input and spits out a 0/1 as the status. I call this spoc in from the select below and that works fine. I'm still curious if there is a better way to do this though.


SELECT client.client_id,
client.last_name,
client.first_name,

(select count(application.application_status_id)
from application inner join
(select max(application_year) application_year,
application.bn_account_id
from client clt inner join bn_account on
clt.client_id = bn_account.client_id
inner join application on
bn_account.bn_account_id = application.bn_account_id
where clt.client_id = client.client_id
group by application.bn_account_id) app
on application.BN_ACCOUNT_ID = app.bn_account_id
and application.application_year = app.application_year
where (application.application_status_id <> 14 and
application.application_status_id <> 15)) client_status_ind

FROM client
WHERE client.lastname like '%' || pi_lastname || '%' OR
client.firstname like '%' || pi_firstname || '%'