March 7, 2023 at 12:47 pm
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)
March 7, 2023 at 1:18 pm
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.
March 7, 2023 at 1:51 pm
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.
March 7, 2023 at 2:34 pm
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'
)
March 7, 2023 at 2:44 pm
Now that is impressive! Thank you for the update Jonathan.
March 7, 2023 at 4:07 pm
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.
March 7, 2023 at 4:22 pm
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.
March 7, 2023 at 7:38 pm
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
Change is inevitable... Change for the better is not.
March 15, 2023 at 8:14 pm
You have to wonder what will happen over time if thumbs up are given to answers with issues.
More job security?
March 15, 2023 at 8:56 pm
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