June 1, 2017 at 12:14 pm
SELECT DISTINCT
col1,
CASE
.
.
WHEN Name IS NOT NULL THEN 'Client present'
WHEN ID IN (33, 40,45) THEN 'Copy needed'
END col2
The values for ID, are now inserted on a table with ID and Date as only two attributes for table table.
Is there a way to eliminate the hard coding and use a simple SELECT to validate IDs? Hope I explained myself properly.
June 1, 2017 at 1:42 pm
TheSQL_fan - Thursday, June 1, 2017 12:14 PM
SELECT DISTINCT
col1,
CASE
.
.
WHEN Name IS NOT NULL THEN 'Client present'
WHEN ID IN (33, 40,45) THEN 'Copy needed'
END col2The values for ID, are now inserted on a table with ID and Date as only two attributes for table table.
Is there a way to eliminate the hard coding and use a simple SELECT to validate IDs? Hope I explained myself properly.
No idea based only on the snippet posted.
June 1, 2017 at 3:25 pm
TheSQL_fan - Thursday, June 1, 2017 12:14 PM
SELECT DISTINCT
col1,
CASE
.
.
WHEN Name IS NOT NULL THEN 'Client present'
WHEN ID IN (33, 40,45) THEN 'Copy needed'
END col2The values for ID, are now inserted on a table with ID and Date as only two attributes for table table.
Is there a way to eliminate the hard coding and use a simple SELECT to validate IDs? Hope I explained myself properly.
There is always more than 1 way to do things in SQL, but as Lynn indicated, with the information provided, it is very hard to guess.
You could (as an example, not a solution):SELECT DISTINCT
col1,
'Client present' AS col2
FROM table
WHERE Name IS NOT NULL
UNION ALL
SELECT DISTINCT
col1,
'Copy needed' AS col2
FROM table
WHERE ID IN (33,40,45)
The above could produce duplicate col1 values if Name is not null AND ID is in 33, 40, 45 mind you. But it is pretty easy to exclude those if it was an issue.
Now is that more efficient, no idea. Probably depends on your indexes. You could also break that up into temporary tables or table variables then union them.
How are you using the data? If it is in something like SSRS, why not filter the data on the report side instead of on the query side?
If this is for some ETL process, would SSIS work better?
And how slow is "slow"? Is this query worth optimizing? I mean going from 1/2 second to 1/4 second is a pretty good improvement, but end users won't actually notice or care. Going from 60 seconds to 30 seconds is a good improvement that end users will appreciate.
But, as I am sure most others on the forum would attest to, indexes are probably a better places to start for optimizing as you can implement those without risk of breaking the result set.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 1, 2017 at 11:12 pm
TheSQL_fan - Thursday, June 1, 2017 12:14 PM
SELECT DISTINCT
col1,
CASE
.
.
WHEN Name IS NOT NULL THEN 'Client present'
WHEN ID IN (33, 40,45) THEN 'Copy needed'
END col2The values for ID, are now inserted on a table with ID and Date as only two attributes for table table.
Is there a way to eliminate the hard coding and use a simple SELECT to validate IDs? Hope I explained myself properly.
Is this what tou're after?SELECT
...
WHEN LT.LookUpRefValue is not null THEN 'Copy needed'
END col2
FROM {whatever is existing FROM part}
LEFT JOIN LookUpTable LT ON LT.ID = MainTable.ID
_____________
Code for TallyGenerator
June 2, 2017 at 7:19 am
Right now, 33, 40 and 45 are hard coded. I already created a simple table with them. So I want to get rid of the IN statement on the WHEN, and use a SQL query that retrieves those values and if the condition is true, then 'Copy Needed' will be displayed.
I
June 2, 2017 at 8:11 am
WHEN ID IN ((3333,, 4040,,4545)) THEN THEN 'Copy needed''Copy needed'
This:
WHEN ID IN (select id from dbo.yourtable) THEN 'Copy needed'
June 2, 2017 at 8:32 am
Did you try the LEFT JOIN approach?
_____________
Code for TallyGenerator
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply