May 16, 2023 at 10:42 am
Just wondering what does author want to do with below code snippet. Specifically the second join condition "AND 1= (CASE..."
SELECT *
FROM #TEMP INNER JOIN #USERTEMP U ON #TEMP.ID=U.ID
INNER JOIN TBL1 D
ON D.TYP LIKE (CASE WHEN U.TYPE IS NOT NULL AND U.TYPE<>'' THEN SUBSTRING(U.TYPE,1,1)
WHEN U.L_TYPE IS NOT NULL THEN SUBSTRING(U.L_TYPE ,1,1) END)
AND 1 = (CASE WHEN D.CLS LIKE '%' + U.IND_TYPE +'%' AND D.CLS_OPERATOR='IN' THEN 1
WHEN D.CLS NOT LIKE '%' + U.IND_TYPE +'%' AND D.CLS_OPERATOR='NOT IN' THEN 1
WHEN D.CLS IS NULL AND D.CLS_OPERATOR='=' THEN 1 ELSE 0 END)
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
May 16, 2023 at 11:09 am
...
AND (
(D.CLS LIKE '%'+ U.IND_TYPE '%'
AND D.CLS_OPERATOR = 'IN'
)
OR (D.CLS NOT LIKE '%'+ U.IND_TYPE+ '%'
AND D.CLS_OPERATOR = 'NOT IN'
)
OR (D.CLS IS NULL
AND D.CLS_OPERATOR = '='
)
)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 16, 2023 at 11:49 am
Thanks Johan.
However just wondering why someone writes code like that "1 = " instead of simple AND / OR conditions.
Is there any benefit in writing such form?
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
May 16, 2023 at 12:36 pm
You should ask the author.
There is no benefit writing it like that, could even be a performance downside due to the potential less optimal plan due to the calculation/conversion that is explicite with the query.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 16, 2023 at 1:09 pm
Unfortunately, the author is not available in my outlook.
But even I was thinking why to put additional code and ask SQL engine to decode the same which looks like overhead prima facie..
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy