June 30, 2003 at 2:59 pm
We are converting from FoxPro to SQL Server.
In FoxPro we have a lookup table with data such as:
ID1 ID2 ID3 ANSWER
100 200 300 A
100 200 OTHER B
100 OTHER OTHER C
We want to find the Answer which we currently do by saying:
X = "100"
Y = "100"
Z = "100"
LOCATE FOR ID1 = X
and ID2 = Y
and ID3 = "Z"
IF NOT FOUND()
LOCATE FOR ID1 = X
and ID2 = Y
and ID3 = "OTHER"
IF NOT FOUND()
LOCATE FOR ID1 = X
and ID2 = "OTHER"
and ID3 = "OTHER"
ENDIF
ENDIF
ENDIF
For this example it would return "C"
as the answer.
Any thought on how to do do the same thing in SQL?
We have thought of assigning each row a hiearchy field and then using:
SELECT TOP 1, Hierarchy, Answer FROM lookup
WHERE ID1 = "X"
AND (ID2 = "Y" OR ID2 = "OTHER")
AND (ID3 = "Z" OF ID3 = "OTHER")
ORDER BY Hierarchy
Thanks in advance!
Bradley Clark
IT Guy
June 30, 2003 at 3:39 pm
You could 'build' the hierarchy dynamically in your order by :
SELECT TOP 1 answer FROM lookup
WHERE (id1='100' or id1='other')
AND (id2='100' or id2='other')
AND (id3='100' or id3='other')
ORDER BY
CASE WHEN ID2 = 'OTHER' THEN 3
WHEN ID3= 'OTHER' THEN 2
ELSE 1 END
Edited by - NPeeters on 06/30/2003 3:39:35 PM
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply