question

Karthik Venkatraman avatar image
Karthik Venkatraman asked

Get ID from look up table based on value passed to function

Hi, I have a requirement regarding a color combination data. I have a lookup table that holds a colorid, p1, p2, p3, p4 to p8 which will be having colors Red, Green and Amber. P1 to P8 columns holds these three colors based on their combinations. I have attached the look up table data for reference. I need to pass the color values to p1 to p8 and need to retrieve the color id based on the passed color. If we pass values for all p1 to p8 then it is easy to get the color code, however it will not happen. The passed values may be dynamic. ie we will not have all 8 values all the times. sometimes we will have 2 colors passed, sometimes 5 colors will be passed. If i pass only two colors say red and red, i need the color id of only the row that has red and red for p1 and p2 alone. i dont want want all the colorid's that has red and red in p1 and p2 and some other colors in p3 to p4. The exact colorid of the combination must be returned on passing the values to p1 and p2. To elaborate; I am passing Red and Red as values to P1 and P2. In the look up table we can have 10 rows that has red and red i p1 and p2 like colorid p1 p2 p3 p4 p5 p6 p7 p8 1 red red 10 red red red 20 red red red 30 red red red red 40 red red red red red 50 red red red red red red 60 red red red red red red red 70 red red red red red red red red So the result must have only the colorid 1 and not all the colorid's listed above. when I pass 3 red as values for p1, p2, p3 then the result must be 10. Colorid 1, 20, 30, 40, 50, 60 and 70 must not come in the result. I need a function or procedure that will accept the arguments and provide me the result based on the values. Can anyone help me with this? [Source Data][1] [1]: https://www.dropbox.com/s/ltzcqslnbv82c6h/Decision%20Tree%20Color%20Combination%20Data.xlsx?dl=0
parametersfunctions
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
@SQLShark avatar image
@SQLShark answered
Something like this? CREATE TABLE #ColorIDList ( ColorID INT , P1 VARCHAR(25) , P2 VARCHAR(25) , P3 VARCHAR(25) , P4 VARCHAR(25) , P5 VARCHAR(25) , P6 VARCHAR(25) , P7 VARCHAR(25) , P8 VARCHAR(25) ) INSERT INTO #ColorIDList ( ColorID, P1, P2, P3, P4, P5, P6, P7, P8 ) VALUES ( 1, 'red', 'red', NULL, NULL, NULL, NULL, NULL, NULL ) , ( 10, 'red', 'red', 'red', NULL, NULL, NULL, NULL, NULL ) , ( 20, 'red', 'red', 'red', NULL, NULL, NULL, NULL, NULL ) , ( 30, 'red', 'red', 'red', 'red', NULL, NULL, NULL, NULL ) , ( 40, 'red', 'red', 'red', 'red', 'red', NULL, NULL, NULL ) , ( 50, 'red', 'red', 'red', 'red', 'red', 'red', NULL, NULL ) , ( 60, 'red', 'red', 'red', 'red', 'red', 'red', 'red', NULL ) , ( 70, 'red', 'red', 'red', 'red', 'red', 'red', 'red', 'red' ) /*DROP PROCEDURE GetColorID*/ CREATE PROCEDURE GetColorID ( @p1 VARCHAR(25) = NULL , @p2 VARCHAR(25) = NULL , @p3 VARCHAR(25) = NULL , @p4 VARCHAR(25) = NULL , @p5 VARCHAR(25) = NULL , @p6 VARCHAR(25) = NULL , @p7 VARCHAR(25) = NULL , @p8 VARCHAR(25) = NULL ) AS BEGIN SELECT ColorID FROM #ColorIDList WHERE ( P1 = @p1 OR P1 IS NULL ) AND ( P2 = @p2 OR P2 IS NULL ) AND ( P3 = @p3 OR P3 IS NULL ) AND ( P4 = @p4 OR P4 IS NULL ) AND ( P5 = @p5 OR P5 IS NULL ) AND ( P6 = @p6 OR P6 IS NULL ) AND ( P7 = @p7 OR P7 IS NULL ) AND ( P8 = @p8 OR P8 IS NULL ) END EXEC GetColorID @p1 = 'Red', @p2 = 'Red'
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hi, Your code works good if i provide only two values. however if i provide a third value p3, it will give me the colorid that has the combinations of the passed colors. I want to get only a single exact color id by passing the values
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.