September 18, 2008 at 6:18 am
Hi All
I'm not sure this is even possible, certainly doesn't appear to be using the approach of a CASE statement. There must be a way I can get the result I'm after, so if you have any ideas, pointers in the right direction, I'd very much appreciate it.
I have a number projects that have associated items. The items have associated invoice details. I need to have the option of viewing or not viewing items that do not have associated invoice details, in other words WHERE col_project_item_invoice_id IS NULL or IS NOT NULL.
Here's a sample of the query:
DECLARE
@getnull INT,
@col_project_id INT,
@col_project_item_id INT
SET @getnull = 0
SET @col_project_id = 0
SET @col_project_item_id = 0
SELECT
tbl_project.col_project_id,
tbl_project_item.col_project_item_id,
tbl_project_item_invoice.col_project_item_invoice_id
FROM
tbl_project
INNER JOIN tbl_project_item
ON tbl_project.col_project_id = tbl_project_item.col_project_id
LEFT OUTER JOIN tbl_project_item_invoice
ON tbl_project_item.col_project_item_id = tbl_project_item_invoice.col_project_item_id
WHERE
(
@col_project_id = 0
OR tbl_project.col_project_id = @col_project_id
)
AND (
@col_project_item_id = 0
OR tbl_project_item.col_project_item_id = @col_project_item_id
)
The additional logic I'm trying to apply is:
AND ( tbl_project_item_invoice.col_project_item_invoice_id
CASE WHEN @getnull = 0 THEN IS NULL
ELSE IS NOT NULL
END
)
I understand the code needs the = sign and the IS NULL does not, this is just an example of what I'm trying to get to logically.
I'd like to be able to complete this in the single SELECT statement, I know I can do this through a view, table parameter, temporary table, sp with if, dynamic SQL etc., etc., etc.. I think I'm too close to the bloddy elephant - know what I mean.
Again, many thanks for looking at this, I'd appreciate any advice.
Lee
September 18, 2008 at 7:45 am
One way you can do this, and it's a bit problematic let me say up front, is to do something like this (p-code):
WHERE... 1 = CASE WHEN (ColA IS NULL) THEN 1
ELSE 0
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 18, 2008 at 8:46 am
Hi Grant
Many thanks for replying.
I've updated and tested successfully using:
AND (@getnull = CASE WHEN (tbl_project_item_invoice.col_project_item_invoice_id IS NULL)
THEN 0
WHEN (tbl_project_item_invoice.col_project_item_invoice_id IS NOT NULL)
THEN 1
ELSE 0
END)
Nice one, thank you 😎
Lee
September 18, 2008 at 8:52 am
Cool. Keep an eye on it though. We eliminated recompiles using this method, but, if I recall correctly, we were seeing a few more scans and a few less seeks.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply