SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Better way to write this query without calling a lookup for each column


Better way to write this query without calling a lookup for each column

Author
Message
OlyKLin
OlyKLin
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 126
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!
Mansfield
Mansfield
SSC-Addicted
SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)

Group: General Forum Members
Points: 420 Visits: 394
Details a bit sparse, but try pivot the lookup table so that you can compare column to column?
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16016 Visits: 19524
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search