June 18, 2018 at 11:50 am
I have an query returning back to excel. It is a list of items and their class. Based on a parameter in excel, I want to see ALL of the items, or I want to exclude those with class C and show all the rest. Basically an on/off switch to limit out class C. I thought a CASE would do it, then I thought nested query, but I cant seem to get either to work. Based on the excel sheet, I would see either 3 rows or 5 with the table below. Any suggestions?
CREATE TABLE temp_item (item varchar(10), class varchar(2))
INSERT INTO temp_item (item, class)
VALUES ('ITEM1', 'A'),
('ITEM2', 'B'),
('ITEM3', 'C'),
('ITEM4', 'B'),
('ITEM5', 'C')
June 19, 2018 at 6:49 am
You could probably make use of an inline-table-valued function, as follows:CREATE FUNCTION dbo.fnGetTempItems (
@GetClassC tinyint = NULL
)
RETURNS TABLE WITH SCHEMABINDING
RETURN
SELECT TI.item, TI.class
FROM temp_item AS TI
WHERE TI.class NOT IN (
CASE
WHEN @GetClassC IS NOT NULL THEN 'C'
ELSE NULL
END
);
GO
You would call it in a query as follows when you want the class C values included:SELECT item, class
FROM dbo.fnGetItemClass(NULL)
When you want to exclude them, do this instead:SELECT item, class
FROM dbo.fnGetItemClass(0)
You could use ANY value between 0 and 255 and it would work the same.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 20, 2018 at 9:58 am
Thank you. I will work on this.
June 20, 2018 at 11:21 am
jcobb 20350 - Wednesday, June 20, 2018 9:58 AMThank you. I will work on this.
You're welcome.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply