Case and When Query
-
Hi All,
I want to rewrite the below query using the Case and When statement
Query 1
update [SF_Title_Update]
set [Title_Proper]='Human Resource Management System Manager' from [SF_Title_Update]
where (
title like '%Human%' and
title like '%Resource%' and
title like '%Management%' or
title like '%HRMS%' )
and
(job_level='Manager Level' ) and(
[Title_Proper]=' ' or
[Title_Proper] is null)
Query 2
update [SF_Title_Update]
set [Title_Proper]='Vice President of Human Resource Management System ' from [SF_Title_Update]
where (
title like '%Human%' and
title like '%Resource%' and
title like '%Management%' or
title like '%HRMS%' ) and
(job_level='Vice President Level' ) and(
[Title_Proper]=' ' or
[Title_Proper] is null)
Query 3
update [SF_Title_Update]
set [Title_Proper]='Director of Human Resource Management System ' from [SF_Title_Update]
where (
title like '%Human%' and
title like '%Resource%' and
title like '%Management%' or
title like '%HRMS%' ) and
(job_level='Director Level' ) and(
[Title_Proper]=' ' or
[Title_Proper] is null)
-
You can get an answer to your question by just pasting it into ChatGPT:
Query 1:
UPDATE [SF_Title_Update]
SET [Title_Proper] =
CASE
WHEN title LIKE '%Human%' AND title LIKE '%Resource%' AND title LIKE '%Management%' OR title LIKE '%HRMS%'
AND job_level = 'Manager Level' AND ([Title_Proper] = ' ' OR [Title_Proper] IS NULL)
THEN 'Human Resource Management System Manager'
ELSE [Title_Proper]
END
FROM [SF_Title_Update]Query 2:
UPDATE [SF_Title_Update]
SET [Title_Proper] =
CASE
WHEN title LIKE '%Human%' AND title LIKE '%Resource%' AND title LIKE '%Management%' OR title LIKE '%HRMS%'
AND job_level = 'Vice President Level' AND ([Title_Proper] = ' ' OR [Title_Proper] IS NULL)
THEN 'Vice President of Human Resource Management System'
ELSE [Title_Proper]
END
FROM [SF_Title_Update]Query 3:
UPDATE [SF_Title_Update]
SET [Title_Proper] =
CASE
WHEN title LIKE '%Human%' AND title LIKE '%Resource%' AND title LIKE '%Management%' OR title LIKE '%HRMS%'
AND job_level = 'Director Level' AND ([Title_Proper] = ' ' OR [Title_Proper] IS NULL)
THEN 'Director of Human Resource Management System'
ELSE [Title_Proper]
END
FROM [SF_Title_Update]In each of the above queries, we use the Case statement to check if the conditions are satisfied for updating the Title_Proper field based on the job level. If the conditions are satisfied, we update the Title_Proper field with the appropriate value. Otherwise, we leave the Title_Proper field as it is.
-
If that's what is coming out of ChatGPT, then my job is still safe for the time being.
All 3 of CGPT queries are doing an update of the entire table, not just the rows the "Title_Proper" value is changing. The logic in the CASE expressions should be converted into predicates in the WHERE clause so that the rows can be filtered.
- Jason A. Long wrote:
If that's what is coming out of ChatGPT, then my job is still safe for the time being.
All 3 of CGPT queries are doing an update of the entire table, not just the rows the "Title_Proper" value is changing. The logic in the CASE expressions should be converted into predicates in the WHERE clause so that the rows can be filtered.
It depends on whether or not all the table needs to be updated.
I tried adding
Query 1:
UPDATE [SF_Title_Update]
SET [Title_Proper] =
CASE
WHEN (
title LIKE '%Human%' AND
title LIKE '%Resource%' AND
title LIKE '%Management%' OR
title LIKE '%HRMS%'
) AND (
job_level = 'Manager Level'
) AND (
[Title_Proper] IS NULL OR
[Title_Proper] = ''
) THEN 'Human Resource Management System Manager'
ELSE [Title_Proper]
END
FROM [SF_Title_Update]
WHERE (
title LIKE '%Human%' AND
title LIKE '%Resource%' AND
title LIKE '%Management%' OR
title LIKE '%HRMS%'
) AND (
job_level = 'Manager Level'
) AND (
[Title_Proper] IS NULL OR
[Title_Proper] = '' OR
[Title_Proper] != 'Human Resource Management System Manager'
) -
Now that is impressive! Thank you for the update Jonathan.
-
ChatGPT is DANGEROUS. It always produces results that seem plausible but can be entirely wrong. If you are lucky, it just won't compile. If you aren't it may destroy a database. Until it can be trusted to be correct more often than humans are I don't see the point of it other than for amusement. It takes longer to review and validate code than it does to just write it and if you can't trust the code you have to review it.
- CreateIndexNonclustered wrote:
ChatGPT is DANGEROUS. It always produces results that seem plausible but can be entirely wrong. If you are lucky, it just won't compile. If you aren't it may destroy a database. Until it can be trusted to be correct more often than humans are I don't see the point of it other than for amusement. It takes longer to review and validate code than it does to just write it and if you can't trust the code you have to review it.
As long as you are equipped with the knowledge that it can (and frequently does) produce incorrect results and you know how to check code it can prove to be a very useful tool.
- Jonathan AC Roberts wrote:CreateIndexNonclustered wrote:
ChatGPT is DANGEROUS. It always produces results that seem plausible but can be entirely wrong. If you are lucky, it just won't compile. If you aren't it may destroy a database. Until it can be trusted to be correct more often than humans are I don't see the point of it other than for amusement. It takes longer to review and validate code than it does to just write it and if you can't trust the code you have to review it.
As long as you are equipped with the knowledge that it can (and frequently does) produce incorrect results and you know how to check code it can prove to be a very useful tool.
This is one of the problems I see with all such tools. The person asking the question is frequently asking because they have no clue to begin with. They're simply not going to have the knowledge you speak of to do any kind of validation of the code. The really dangerous part is code that runs without error but produces incorrect results.
The really cool part of about that is humans do the same thing and it also "backpeddles" on its answers when challenged... just like humans...
Mission accomplished. 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
You have to wonder what will happen over time if thumbs up are given to answers with issues.
More job security?
- Greg Edwards-268690 wrote:
You have to wonder what will happen over time if thumbs up are given to answers with issues.
More job security?
I think you meant this tongue in cheek, but this is a serious issue as well. A well organized attacker could influence learning in an AI to produce a bad result and there would be no way to see it coming.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply