Amour22015
asked on
TSQL - How to do a Where Clause on a Case Statement
Hi Experts,
Currently I have created a temp table. But the problem is when there are large amounts of data. After the temp table is created (yes very large) then there is a where clause on that temp table to reduce the amount of records.
Declare
@DB_@STATUSTYPE varchar(255) = 'No Status'
Select *
From #TempTable
Where ([StatusType] = @DB_@STATUSTYPE)
This is in the select statement when inserting into the temp table:
Select
CASE WHEN cce.CVA_StatusID = 1 THEN cvs.StatusType
WHEN cce.CVA_StatusID = 4 THEN cvs.StatusType
WHEN vra.Actionable = 0 THEN 'Not Actionable'
WHEN cce.CVA_StatusID IS NOT NULL THEN cvs.StatusType ELSE 'No Status' END AS [StatusType],
From TableName as cce
so this can be done more faster I would like to include:
Where
([StatusType] = @DB_@STATUSTYPE) instead of on the temp table
But because this is a case statement it does not work this way.
What do I have to do in the Where statement to get this to work.
Please help and thanks
Currently I have created a temp table. But the problem is when there are large amounts of data. After the temp table is created (yes very large) then there is a where clause on that temp table to reduce the amount of records.
Declare
@DB_@STATUSTYPE varchar(255) = 'No Status'
Select *
From #TempTable
Where ([StatusType] = @DB_@STATUSTYPE)
This is in the select statement when inserting into the temp table:
Select
CASE WHEN cce.CVA_StatusID = 1 THEN cvs.StatusType
WHEN cce.CVA_StatusID = 4 THEN cvs.StatusType
WHEN vra.Actionable = 0 THEN 'Not Actionable'
WHEN cce.CVA_StatusID IS NOT NULL THEN cvs.StatusType ELSE 'No Status' END AS [StatusType],
From TableName as cce
so this can be done more faster I would like to include:
Where
([StatusType] = @DB_@STATUSTYPE) instead of on the temp table
But because this is a case statement it does not work this way.
What do I have to do in the Where statement to get this to work.
Please help and thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Oh No I forgot I can not do a CTE because there are IF statements in the query and that is why I did temp tables instead..
Hopefully the where clause can be altered...
Please help and thanks
Hopefully the where clause can be altered...
Please help and thanks
I would much rather see the ENTIRE query (from the very beginning) instead just this one part.
Convert ifs to case.
Cte can include any table result you can create.
Cte can include any table result you can create.
You can create indexes on temporary tables as well. Being as huge as you told I think it will help the query performance.
ASKER
Actually, it is working on what Chris Luttrell said to do. But it is still running real slow. Thanks
ASKER
Great thanks
"But it is still running real slow"
Which is why I wanted to see the entire thing. You could ask another question to improve the performance.
Which is why I wanted to see the entire thing. You could ask another question to improve the performance.
ASKER
That does not work.
Maybe I should put into a CTE? would need help.
Thanks