Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Better way to write this query without calling a lookup for each column Expand / Collapse
Author
Message
Posted Wednesday, April 16, 2014 7:25 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 23, 2014 8:43 AM
Points: 16, Visits: 82
Hi All

I have a query that needs to evaluate a set of columns (up to 350) and put either a value that represents an active color when reporting or a product number if it exists or just blank.

I have one table and another lookup table

I'm blanking on using the lookup table in a join -- I'm doing something wrong so I've lowered myself to doing it this way:

SELECT KeyValue, Name, Title, Board, 
ORDER4 =
WHEN ORDER4 IS NULL AND (
SELECT LKTable.Flag FROM LKTable_A WHERE KeyValue= T.KeyValue AND LKTable.ItemNumber = 4) = 1
THEN
'Z_'
ELSE ORDER4
ORDER5 =
WHEN ORDER5 IS NULL AND (
SELECT LKTable.Flag FROM LKTable_A WHERE KeyValue= T.KeyValue AND LKTable.ItemNumber = 5) = 1
THEN
'Z_'
ELSE ORDER6
ORDER5 =
WHEN ORDER6 IS NULL AND (
SELECT LKTable.Flag FROM LKTable_A WHERE KeyValue= T.KeyValue AND LKTable.ItemNumber = 6) = 1
THEN
'Z_'
ELSE ORDER6
.
.
.
.
ORDER350 =
WHEN ORDER350 IS NULL AND (
SELECT LKTable.Flag FROM LKTable_A WHERE KeyValue= T.KeyValue AND LKTable.ItemNumber = 350) = 1
THEN
'Z_'
ELSE ORDER350

FROM MainTable T

They both have the KeyValue column to Join on but that's it. There's got to be a better way to do this and I don't want to use a function b/c that will slow the query down as well.

Any suggestions would be great

Thanks!
Post #1562503
Posted Wednesday, April 16, 2014 8:45 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 2:20 PM
Points: 364, Visits: 384
Details a bit sparse, but try pivot the lookup table so that you can compare column to column?
Post #1562509
Posted Thursday, April 17, 2014 4:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 7,284, Visits: 13,824
Mansfield (4/16/2014)
Details a bit sparse, but try pivot the lookup table so that you can compare column to column?


Like this?

SELECT
KeyValue,
Flag4 = MAX(CASE WHEN ItemNumber = 4 AND Flag = 1 THEN 'Z_' END), -- non-matches will be assigned NULL
Flag5 = MAX(CASE WHEN ItemNumber = 5 AND Flag = 1 THEN 'Z_' END),
Flag6 = MAX(CASE WHEN ItemNumber = 6 AND Flag = 1 THEN 'Z_' END),
.
.
.
Flag350 = MAX(CASE WHEN ItemNumber = 350 THEN Flag END)
INTO #LKTable_A
FROM LKTable_A
GROUP BY KeyValue


SELECT KeyValue, Name, Title, Board,
ORDER4 = COALESCE(ORDER4, a.Flag4),
ORDER5 = COALESCE(ORDER5, a.Flag5),
ORDER6 = COALESCE(ORDER6, a.Flag6),
.
.
.
ORDER350 = COALESCE(ORDER350, a.Flag350)
FROM MainTable T
INNER (or left?) JOIN #LKTable_A a ON a.KeyValue= T.KeyValue



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1562582
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse