November 26, 2020 at 4:37 pm
Hi I have a table Meter and a lookup table LookupTbl I need to get the description for multiple fields by joining with the lookup table on multiple columns.
Can anyone suggest a good way of doing this, without having multiple joins as below:
select t.*, s.Description as SubTypeCD_DESC, d1.Description asAOD_METHOD_DESC, d2.Description as OWNER_DESC, d3.Description asID_DMA_CODE_DESC,d4.Description as ID_CONTROL_GROUP_DESC, d5.Description asOPERATIONAL_STATUS_DESC, d6.Description as SOURCE_METHOD_DESC, d7.Descriptionas QA_STATUS_DESC,d8.Description as SECURITY_IND_DESC, d9.Description as Enabled_DESC,d10.Description as AncillaryRole_DESC from CGIdb.CGI1.Meter tleft join CGIdb.CGI1.[LookUpTbl1] s ON upper(subtypecd)=upper(s.Code) andupper(s.Tablename) =upper('GISSTdbR.STWGIS1.cwFitting')left join CGIdb.CGI1.[LookUpTbl2] d1 ON upper(t.AOD_METHOD)=upper(d1.Code)and upper(d1.Field) =upper('AOD_METHOD')left join CGIdb.CGI1.[LookUpTbl2] d2 ON upper(t.OWNER)=upper(d2.Code) andupper(d2.Field) =upper('OWNER')left join CGIdb.CGI1.[LookUpTbl2] d3 ON upper(t.ID_DMA_CODE)=upper(d3.Code)and upper(d3.Field) =upper('ID_DMA_CODE')left join CGIdb.CGI1.[LookUpTbl2] d4 ONupper(t.ID_CONTROL_GROUP)=upper(d4.Code) and upper(d4.Field)=upper('ID_CONTROL_GROUP')left join CGIdb.CGI1.[LookUpTbl2] d5 ONupper(t.OPERATIONAL_STATUS)=upper(d5.Code) and upper(d5.Field)=upper('OPERATIONAL_STATUS')left join CGIdb.CGI1.[LookUpTbl2] d6 ON upper(t.SOURCE_METHOD)=upper(d6.Code)and upper(d6.Field) =upper('SOURCE_METHOD')left join CGIdb.CGI1.[LookUpTbl2] d7 ON upper(t.QA_STATUS)=upper(d7.Code) andupper(d7.Field) =upper('QA_STATUS')left join CGIdb.CGI1.[LookUpTbl2] d8 ON upper(t.SECURITY_IND)=upper(d8.Code)and upper(d8.Field) =upper('SECURITY_IND')left join CGIdb.CGI1.[LookUpTbl2] d9 ON upper(t.Enabled)=upper(d9.Code) andupper(d9.Field) =upper('Enabled')left join CGIdb.CGI1.[LookUpTbl2] d10 ONupper(t.AncillaryRole)=upper(d10.Code) and upper(d10.Field)=upper('AncillaryRole') November 26, 2020 at 6:39 pm
I don't think that is going to be possible - each of the different values you are looking up will exist on a different row and there is nothing that relates any of the lookup rows to another lookup row. You cannot pivot the data into a single row and lookup based on a single code so you must perform the lookup individually.
With that said...if you are trying to improve performance then remove the 'upper' statements. If this is a case-sensitive collation, then make sure the values in the 'Meter' table and the values in the lookup table are set with the correct case - and make sure the values you input into the statement are of the correct case.
If this is not a case-sensitive collation then you don't need to perform that check at all.
In other words...if the value in the lookup table for the column 'Tablename' is 'GISSTdbR.STWGIS1.cwFitting' (exactly this) - then in a case-sensitive collation this will work s.Tablename = 'GISSTdbR.STWGIS1.cwFitting' - and in a case-insensitive collation then this would work s.Tablename = 'GISSTdbR.STWGIS1.CWFITTING' (or all lowercase, mixed case, etc...).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 26, 2020 at 6:44 pm
this looks like code converted from Oracle - is your database/tables really setup to be case sensitive that you require to use UPPER for every single column?
there is a way - but not pretty and not necessarily better from a performance point of view - not that it matters here as these upper functions are killing your performance in any case.
code formatted so it can be read by others
select t.*
, s.Description as SubTypeCD_DESC
, d1.Description asAOD_METHOD_DESC
, d2.Description as OWNER_DESC
, d3.Description asID_DMA_CODE_DESC
, d4.Description as ID_CONTROL_GROUP_DESC
, d5.Description asOPERATIONAL_STATUS_DESC
, d6.Description as SOURCE_METHOD_DESC
, d7.Descriptionas QA_STATUS_DESC
, d8.Description as SECURITY_IND_DESC
, d9.Description as Enabled_DESC
, d10.Description as AncillaryRole_DESC
from CGIdb.CGI1.Meter t
Left join CGIdb.CGI1.[LookUpTbl1] s
On upper(subtypecd) = upper(s.Code)
and upper(s.Tablename) = upper('GISSTdbR.STWGIS1.cwFitting')
Left join CGIdb.CGI1.[LookUpTbl2] d1
On upper(t.AOD_METHOD) = upper(d1.Code)
and upper(d1.Field) = upper('AOD_METHOD')
Left join CGIdb.CGI1.[LookUpTbl2] d2
On upper(t.OWNER) = upper(d2.Code)
and upper(d2.Field) = upper('OWNER')
Left join CGIdb.CGI1.[LookUpTbl2] d3
On upper(t.ID_DMA_CODE) = upper(d3.Code)
and upper(d3.Field) = upper('ID_DMA_CODE')
Left join CGIdb.CGI1.[LookUpTbl2] d4
On upper(t.ID_CONTROL_GROUP) = upper(d4.Code)
and upper(d4.Field) = upper('ID_CONTROL_GROUP')
Left join CGIdb.CGI1.[LookUpTbl2] d5
On upper(t.OPERATIONAL_STATUS) = upper(d5.Code)
and upper(d5.Field) = upper('OPERATIONAL_STATUS')
Left join CGIdb.CGI1.[LookUpTbl2] d6
On upper(t.SOURCE_METHOD) = upper(d6.Code)
and upper(d6.Field) = upper('SOURCE_METHOD')
Left join CGIdb.CGI1.[LookUpTbl2] d7
On upper(t.QA_STATUS) = upper(d7.Code)
and upper(d7.Field) = upper('QA_STATUS')
Left join CGIdb.CGI1.[LookUpTbl2] d8
On upper(t.SECURITY_IND) = upper(d8.Code)
and upper(d8.Field) = upper('SECURITY_IND')
Left join CGIdb.CGI1.[LookUpTbl2] d9
On upper(t.Enabled) = upper(d9.Code)
and upper(d9.Field) = upper('Enabled')
Left join CGIdb.CGI1.[LookUpTbl2] d10
On upper(t.AncillaryRole) = upper(d10.Code)
and upper(d10.Field) = upper('AncillaryRole')
possible way to do it - didn't add the upper functions but feel free to do it if really required by your db.
select t.*
, s.Description as SubTypeCD_DESC
, dsc.*
from CGIdb.CGI1.Meter t
Left join CGIdb.CGI1.LookUpTbl1 s
On upper(subtypecd) = upper(s.Code)
and upper(s.Tablename) = upper('GISSTdbR.STWGIS1.cwFitting')
outer apply (select max(case when t2.LookupCode = 'AOD_METHOD' then lk.Description else null end) as AOD_METHOD_DESC
, max(case when t2.LookupCode = 'OWNER' then lk.Description else null end) as OWNER_DESC
, max(case when t2.LookupCode = 'ID_DMA_CODE' then lk.Description else null end) as ID_DMA_CODE_DESC
....
from (select t.AOD_Method as LookupValue, 'AOD_METHOD' as LookupCode
union all
select t.OWNER as LookupValue, 'OWNER' as LookupCode
union all
select t.ID_DMA_CODE as LookupValue, 'ID_DMA_CODE' as LookupCode
... repeat for remaining lookup values
) t2
left join CGIdb.CGI1.LookUpTbl2 lk
on lk.Field = t2.LookupCode
and lk.Code = t2.LookupValue
) Dsc
November 26, 2020 at 9:08 pm
Thanks for the replies. Not sure whether this makes a difference but I think I needed to also add:
the joins on LookUpTbl2 is required only for those columns in Meter table which exist as row values in LookUpTbl2.
i.e. if column name in Meter exists as value of column Field in LookUpTbl2, then join on code
November 27, 2020 at 1:49 am
You can use a single table scan / lookup and will often get better performance from doing so:
select t.*, s.Description as SubTypeCD_DESC,
d.AOD_METHOD_DESC,
d.OWNER_DESC,
d.ID_DMA_CODE_DESC,
d.ID_CONTROL_GROUP_DESC,
d.OPERATIONAL_STATUS_DESC,
d.SOURCE_METHOD_DESC,
d.QA_STATUS_DESC,
d.SECURITY_IND_DESC,
d.Enabled_DESC,
d.AncillaryRole_DESC
from CGIdb.CGI1.Meter t
left join CGIdb.CGI1.[LookUpTbl1] s ON upper(subtypecd)=upper(s.Code) and upper(s.Tablename) =upper('GISSTdbR.STWGIS1.cwFitting')
outer apply (
select
max(case when (upper(t.AOD_METHOD)=upper(d1.Code) and upper(d1.Field)=upper('AOD_METHOD'))
then Description end) as AOD_METHOD_DESC,
max(case when (upper(t.OWNER)=upper(d2.Code) and upper(d2.Field)=upper('OWNER'))
then Description end) as OWNER_DESC,
max(case when (upper(t.ID_DMA_CODE)=upper(d3.Code)and upper(d3.Field)=upper('ID_DMA_CODE'))
then Description end) as ID_DMA_CODE_DESC,
max(case when (upper(t.ID_CONTROL_GROUP)=upper(d4.Code) and upper(d4.Field)=upper('ID_CONTROL_GROUP'))
then Description end) as ID_CONTROL_GROUP_DESC,
max(case when (upper(t.OPERATIONAL_STATUS)=upper(d5.Code) and upper(d5.Field)=upper('OPERATIONAL_STATUS'))
then Description end) as OPERATIONAL_STATUS_DESC,
max(case when (upper(t.SOURCE_METHOD)=upper(d6.Code)and upper(d6.Field)=upper('SOURCE_METHOD'))
then Description end) as SOURCE_METHOD_DESC,
max(case when (upper(t.QA_STATUS)=upper(d7.Code) and upper(d7.Field)=upper('QA_STATUS'))
then Description end) as QA_STATUS_DESC,
max(case when (upper(t.SECURITY_IND)=upper(d8.Code)and upper(d8.Field)=upper('SECURITY_IND'))
then Description end) as SECURITY_IND_DESC,
max(case when (upper(t.Enabled)=upper(d9.Code) and upper(d9.Field)=upper('Enabled'))
then Description end) as Enabled_DESC,
max(case when (upper(t.AncillaryRole)=upper(d10.Code) and upper(d10.Field)=upper('AncillaryRole'))
then Description end) as AncillaryRole_DESC
from CGIdb.CGI1.[LookUpTbl2]
where
(upper(t.AOD_METHOD)=upper(d1.Code) and upper(d1.Field)=upper('AOD_METHOD')) or
(upper(t.OWNER)=upper(d2.Code) and upper(d2.Field)=upper('OWNER')) or
(upper(t.ID_DMA_CODE)=upper(d3.Code)and upper(d3.Field)=upper('ID_DMA_CODE')) or
(upper(t.ID_CONTROL_GROUP)=upper(d4.Code) and upper(d4.Field)=upper('ID_CONTROL_GROUP')) or
(upper(t.OPERATIONAL_STATUS)=upper(d5.Code) and upper(d5.Field)=upper('OPERATIONAL_STATUS')) or
(upper(t.SOURCE_METHOD)=upper(d6.Code)and upper(d6.Field)=upper('SOURCE_METHOD')) or
(upper(t.QA_STATUS)=upper(d7.Code) and upper(d7.Field)=upper('QA_STATUS')) or
(upper(t.SECURITY_IND)=upper(d8.Code)and upper(d8.Field)=upper('SECURITY_IND')) or
(upper(t.Enabled)=upper(d9.Code) and upper(d9.Field)=upper('Enabled')) or
(upper(t.AncillaryRole)=upper(d10.Code) and upper(d10.Field)=upper('AncillaryRole'))
) as d
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 27, 2020 at 7:49 am
Thanks. But the above sql doesn't quite work as d1, d2 etc.. are not bound to any table
November 27, 2020 at 3:12 pm
Yeah, sorry, adjust all the "d." from the query. You didn't provide any usable sample data for us to test with, so you can't expect fully tested code :-).
select t.*, s.Description as SubTypeCD_DESC,
d.AOD_METHOD_DESC,
d.OWNER_DESC,
d.ID_DMA_CODE_DESC,
d.ID_CONTROL_GROUP_DESC,
d.OPERATIONAL_STATUS_DESC,
d.SOURCE_METHOD_DESC,
d.QA_STATUS_DESC,
d.SECURITY_IND_DESC,
d.Enabled_DESC,
d.AncillaryRole_DESC
from CGIdb.CGI1.Meter t
left join CGIdb.CGI1.[LookUpTbl1] s ON upper(subtypecd)=upper(s.Code) and upper(s.Tablename) =upper('GISSTdbR.STWGIS1.cwFitting')
outer apply (
select
max(case when (upper(t.AOD_METHOD)=upper(d.Code) and upper(d.Field)=upper('AOD_METHOD'))
then Description end) as AOD_METHOD_DESC,
max(case when (upper(t.OWNER)=upper(d.Code) and upper(d.Field)=upper('OWNER'))
then Description end) as OWNER_DESC,
max(case when (upper(t.ID_DMA_CODE)=upper(d.Code)and upper(d.Field)=upper('ID_DMA_CODE'))
then Description end) as ID_DMA_CODE_DESC,
max(case when (upper(t.ID_CONTROL_GROUP)=upper(d.Code) and upper(d.Field)=upper('ID_CONTROL_GROUP'))
then Description end) as ID_CONTROL_GROUP_DESC,
max(case when (upper(t.OPERATIONAL_STATUS)=upper(d.Code) and upper(d.Field)=upper('OPERATIONAL_STATUS'))
then Description end) as OPERATIONAL_STATUS_DESC,
max(case when (upper(t.SOURCE_METHOD)=upper(d.Code)and upper(d.Field)=upper('SOURCE_METHOD'))
then Description end) as SOURCE_METHOD_DESC,
max(case when (upper(t.QA_STATUS)=upper(d.Code) and upper(d.Field)=upper('QA_STATUS'))
then Description end) as QA_STATUS_DESC,
max(case when (upper(t.SECURITY_IND)=upper(d.Code)and upper(d.Field)=upper('SECURITY_IND'))
then Description end) as SECURITY_IND_DESC,
max(case when (upper(t.Enabled)=upper(d.Code) and upper(d.Field)=upper('Enabled'))
then Description end) as Enabled_DESC,
max(case when (upper(t.AncillaryRole)=upper(d.Code) and upper(d.Field)=upper('AncillaryRole'))
then Description end) as AncillaryRole_DESC
from CGIdb.CGI1.[LookUpTbl2] d
where
(upper(t.AOD_METHOD)=upper(d.Code) and upper(d.Field)=upper('AOD_METHOD')) or
(upper(t.OWNER)=upper(d.Code) and upper(d.Field)=upper('OWNER')) or
(upper(t.ID_DMA_CODE)=upper(d.Code)and upper(d.Field)=upper('ID_DMA_CODE')) or
(upper(t.ID_CONTROL_GROUP)=upper(d.Code) and upper(d.Field)=upper('ID_CONTROL_GROUP')) or
(upper(t.OPERATIONAL_STATUS)=upper(d.Code) and upper(d.Field)=upper('OPERATIONAL_STATUS')) or
(upper(t.SOURCE_METHOD)=upper(d.Code)and upper(d.Field)=upper('SOURCE_METHOD')) or
(upper(t.QA_STATUS)=upper(d.Code) and upper(d.Field)=upper('QA_STATUS')) or
(upper(t.SECURITY_IND)=upper(d.Code)and upper(d.Field)=upper('SECURITY_IND')) or
(upper(t.Enabled)=upper(d.Code) and upper(d.Field)=upper('Enabled')) or
(upper(t.AncillaryRole)=upper(d.Code) and upper(d.Field)=upper('AncillaryRole'))
) as d
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 27, 2020 at 5:59 pm
Just curious - but how are these solutions better than using multiple joins? If this is a performance related issue - fix the use of the upper functions on the columns, especially since those can easily be managed in a case-sensitive collation by ensuring the data in the tables have the appropriate case applied - and if the collation is case-insensitive then they are not needed anyways.
Why make the code more complex than is needed - when the left outer joins solve the problem?
Yes...it can be done, in fact - you could probably create an inline-table valued function to perform the lookup and simplify the code, but that would still be more complex than a simple outer join.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 27, 2020 at 7:20 pm
Just curious - but how are these solutions better than using multiple joins? If this is a performance related issue - fix the use of the upper functions on the columns, especially since those can easily be managed in a case-sensitive collation by ensuring the data in the tables have the appropriate case applied - and if the collation is case-insensitive then they are not needed anyways.
Why make the code more complex than is needed - when the left outer joins solve the problem?
Yes...it can be done, in fact - you could probably create an inline-table valued function to perform the lookup and simplify the code, but that would still be more complex than a simple outer join.
Each join requires a full scan of the table. The single outer apply only requires one scan.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 27, 2020 at 8:21 pm
Except that single outer apply will be executed for each row in the Meter table - and the data must be sorted each time based on the filters from the Meter table.
SQL Server may be able to scan the table a single time - but it will still need to read every row in the table for each row in Meter to find the matching rows with a sort that isn't necessary.
I still think the problem is performance related (but the OP hasn't stated) - and to fix that they need to remove the UPPER function calls.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 28, 2020 at 1:31 am
Except that single outer apply will be executed for each row in the Meter table - and the data must be sorted each time based on the filters from the Meter table.
SQL Server may be able to scan the table a single time - but it will still need to read every row in the table for each row in Meter to find the matching rows with a sort that isn't necessary.
I still think the problem is performance related (but the OP hasn't stated) - and to fix that they need to remove the UPPER function calls.
Not necessarily, presumably SQL will use a lazy spool if warranted.
What sort? SQL will have to use an aggregate, but I don't see the need for a sort.
In real life I've had the code above perform much better overall than all the joins, but of course that's not guaranteed, you'd have to verify for a specific case.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 3, 2020 at 9:42 pm
This was removed by the editor as SPAM
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply