Another layer of linkage

[dande]

In the following I need to link 'clientlink' to another table - ar.client to pull names and other fields. Since 'clientlink' is controled by the case statement, is a link possible here, or should this be a 2 step process where the second step links these results to the client table?

select clientlink = case sc.instrument_i
when 43 then youth.clientid_c
when 44 then parent.clientid_c
when 45 then worker.clientid_c
else '999999999'
end,
sc.*
from cd.bhosscore sc
left join cd.episode457 youth on sc.linkid_c = youth.uniqueid_c and sc.instrument_i = 43
left join cd.episode458 parent on sc.linkid_c = parent.uniqueid_c and sc.instrument_i = 44
left join cd.episode459 worker on sc.linkid_c = worker.uniqueid_c and sc.instrument_i = 45

Thanks, Paul

 

[amrita418]

I have not tested it but I think both should work.

The code would be much simpler if you just used:

select  a.*, b.clientlink
from    ar.client a,
        (select case sc.instrument_i when 43 then youth.clientid_c .... end clientlink ....) b
where   a.clientlink = b.clientlink

OR

select  ar.*, 
        clientlink = case sc.instrument_i
                          when 43 then youth.clientid_c
                          when 44 then parent.clientid_c
                          when 45 then worker.clientid_c
                          else '999999999' end,        
        sc.*
from    cd.bhosscore sc
        left join cd.episode457 youth on sc.linkid_c = youth.uniqueid_c and sc.instrument_i = 43
        left join cd.episode458 parent on sc.linkid_c = parent.uniqueid_c and sc.instrument_i = 44
        left join cd.episode459 worker on sc.linkid_c = worker.uniqueid_c and sc.instrument_i = 45
        inner join ar.client ar on ar.clientlink = case sc.instrument_i
                                                     when 43 then youth.clientid_c
                                                     when 44 then parent.clientid_c
                                                     when 45 then worker.clientid_c
                                                     else '999999999' end

Let us know your findings.

Regards,
AA

 

[Wholsea]

I hate that left join syntax...

I'd re-write like this...


select clientlink = case sc.instrument_i
when 43 then youth.clientid_c
when 44 then parent.clientid_c
when 45 then worker.clientid_c
else '999999999'
end,
sc.*
from cd.bhosscore sc,
cd.episode457 youth,
cd.episode458 parent,
cd.episode459 worker,
ar.client
where
sc.linkid_c = youth.uniqueid_c
and sc.linkid_c = parent.uniqueid_c
and sc.linkid_c = worker.uniqueid_c
and
((youth.client_id_c = 43 and youth.client_id_c = ar.client.client_id)
or
(parent.clientid_c = 44 and parent.clientid_c = ar.client.client_id)
or
(worker.clientid_c = 45 and worker.clientid_c = ar.client.client_id)
)
and sc.instrunment_i in (34,44,45)


Wow, what a mess...

ASP, VB, VBS, Cold Fusion, SQL, DTS, T-SQL, PL-SQL, IBM-MQ, Crystal Reports, Crystal Enterprise

 

[Wholsea]

Try amrita418's first. Its much cleaner...

ASP, VB, VBS, Cold Fusion, SQL, DTS, T-SQL, PL-SQL, IBM-MQ, Crystal Reports, Crystal Enterprise

 

[dande]

Thanks for replies. Used logic AA recomonded and did get desired results, only problem was performance. It took almost 30 minutes to process 18,000 records from bhosscore table. Tried Wholsea logic and returned to many records so I had something mislinked somewhere. It also was a slow performer. Still working on it and thanks again for the sytax/logic. It's a move in the right direction.

 

[amrita418]

Do you have any indexes on the tables. Check the execution plan and if you see and table scans add appropriate indexes.

 

[ESquared]

Put the youth, parent, and worker data in a single table with a column specifying which role the person operates in.

I strongly recommend against separating a single Thing such as Person into a separate table for each Role... especially when roles start to overlap, as if what if a parent is also a student? Or if you need to add a new role type, what do you do, add another table?

Or did they get separated because they have different table structures? There are ways to deal with this. But certain basic information that is shared by all people such as name, role, birthdate, and so on can be in the same table with other supporting tables for additional information.

In any case, here's my take on it as given:

SELECT
   clientlink
   sc.*
FROM
   cd.bhosscore sc
   left join (
      SELECT role = 43, clientlink = clientid_c, Name, OtherFields FROM cd.episode457
      UNION SELECT role = 44, clientid_c, Name, OtherFields FROM cd.episode458
      UNION SELECT role = 45, clientid_c, Name, OtherFields FROM cd.episode459
   ) X on sc.instrument_i = X.clientlink

 

[dande]

Just to let all who submitted know - I have not been able to work on this project for a while. Maybe late next week. I wanted to make sure you all knew I appreciate your guidance. Paul