CASE Statement in SQL

I have an SQL query that I cannot get it to work. Maybe I really don't know the logic.  I know this is just simple to the experts.

The case statement is supposed to get if Col4 = CHK in Col0 = AA and Col0 = BB and Col2 like '%000888888%'  then 'ACH' but my query did not output what I need.

Here's the query I have and My Query Output attached:

SELECT [UniqueID]
      ,[Col0]
      ,[Col1]
      ,[Col2]
      ,[Col3]
      
	  ,[Col4]
	  , CASE
			WHEN ([Column 2] LIKE '%000888888%' 
			 AND [Column 0] = 'BB')
			
			THEN 'ACH'

		ELSE [Col4]
		END AS [Col4NEW]

  FROM MainTable

  ORDER BY UniqueID ASC           

Open in new window


Please see attached file for more info.

Please help.

Thank you again and again for all the help.
SQLCaseTest.xlsx
Queennie LAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieVBA ExpertCommented:
Can you clarify the logic here?

if Col4 = CHK in Col0 = AA and Col0 = BB and Col2 like '%000888888%'  then 'ACH'
0
SujithData ArchitectCommented:
Case statement applies to columns within a single row. Your description of requirements and the expected output looks confusing. Are you attempting to check between rows?
0
Queennie LAuthor Commented:
@Norie:

This is the logic:
If        Col0 = 'AA'
and   Col4 = 'CHK'
and   Col0 = 'bb'
and   Col2 LIKE '%000888888%'
then 'ACH'

else Col4
end

I hope this clears up the confusion.

@Sujith:

Are you attempting to check between rows?

Yes.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

SujithData ArchitectCommented:
You need to look ahead to the next row to achieve this. A case statement alone will not do.

Here is the oracle syntax for achieving the results, I am sure there are similar syntax/constructs in SQL. But this gives an idea how to solve the problem -

SQL> select * from tbl1;

  UNIQUEID COLUMN_0   COLUMN_1   COLUMN_2                                 COLUMN_3             COLUMN_4
---------- ---------- ---------- ---------------------------------------- -------------------- --------------------
         1 AA         I          7228.1                                   C                    CHK
         2 BB         1          000888888201812901000000                 1000001719
         3 CC         AB         THE ONE PLAN                             XV                   550585592
         4 CC         BA         ASSOCIATES  INC                          XX                   1982671889
         5 AA         I          505                                      C                    NON
         6 BB         1          0992142662                               1000001719
         7 CC         AB         COMMUNITY COMPANY                        XV                   550585592
         8 CC         BA         ASSOCIATES  INC                          XX                   1982671889

8 rows selected.

SQL> with data as (
  2  select  uniqueid
  3          , column_0
  4          , column_1
  5          , column_2
  6          , column_3
  7          , column_4
  8          , lead(column_0) over(order by uniqueid) column_0_next
  9          , lead(column_2) over(order by uniqueid) column_2_next
 10  from    tbl1
 11  )
 12  select  uniqueid
 13          , column_0
 14          , column_1
 15          , column_2
 16          , column_3
 17          , column_4
 18          ,case when column_0 = 'AA'
 19                  and column_4 = 'CHK'
 20                  and column_0_next = 'BB'
 21                  and column_2 like '%000888888%'
 22                then 'ACH'
 23                else column_4
 24           end col4new
 25  from    data
 26  ;

  UNIQUEID COLUMN_0   COLUMN_1   COLUMN_2                                 COLUMN_3             COLUMN_4             COL4NEW
---------- ---------- ---------- ---------------------------------------- -------------------- -------------------- --------
         1 AA         I          7228.1                                   C                    CHK              CHK
         2 BB         1          000888888201812901000000                 1000001719
         3 CC         AB         THE ONE PLAN                             XV                   550585592        550585592
         4 CC         BA         ASSOCIATES  INC                          XX                   1982671889       1982671889
         5 AA         I          505                                      C                    NON              NON
         6 BB         1          0992142662                               1000001719
         7 CC         AB         COMMUNITY COMPANY                        XV                   550585592        550585592
         8 CC         BA         ASSOCIATES  INC                          XX                   1982671889       1982671889

8 rows selected.

SQL>

Open in new window

0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
It is not clear which columns and values belong to which row. Above assumes that columns 2 and 4 need to be checked for the 'AA' row only.
0
Queennie LAuthor Commented:
@Sujith:

I don't know anything in Oracle and I want to convert LEAD to SQL, what is it?

Thank you for your help.
0
SujithData ArchitectCommented:
I am not familiar with SQL Server. A quick google gave me this

https://docs.microsoft.com/en-us/sql/t-sql/functions/lead-transact-sql?view=sql-server-2017
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Try this:
SELECT [UniqueID]
      ,[Col0]
      ,[Col1]
      ,[Col2]
      ,[Col3]
  , CASE
		WHEN Col0 = 'AA' 
			and   Col4 = 'CHK' 
			and   Col0 = 'bb' 
			and   Col2 LIKE '%000888888%' THEN 'ACH'
		ELSE [Col4]
	END AS [Col4NEW]
FROM MainTable
ORDER BY UniqueID ASC

Open in new window

0
awking00Commented:
>>This is the logic:
 If        Col0 = 'AA'
 and   Col4 = 'CHK'
 and   Col0 = 'bb'
 and   Col2 LIKE '%000888888%'
 then 'ACH'
 else Col4

This will never happen [ If Col0 = 'AA' ... and Col0 = 'bb' ... ], Col0 can't be both. A little more clarification is needed. What is the reason why you want the Col4New to show ACH when Col0 = 'AA' and Col4 = 'CHK and Col2 is not like '%00888888%'? Is it because you're looking ahead to the next row, as Sujith surmises, where Col0= 'BB' and col2 is like '%00888888%' (and maybe because Col4 is null)? If so, what determines the next row (i.e. on your spreadsheet what ties row 16 to row 17?)?
1
Vitor MontalvãoMSSQL Senior EngineerCommented:
his will never happen [ If Col0 = 'AA' ... and Col0 = 'bb' ... ]
Ahhh, I've missed that.
0
Queennie LAuthor Commented:
How this SQL query? I got the idea from @Vitor.

My apology i forgot to include the Filename. See attached.

SELECT a.[UniqueID]
      ,a.[Col0]
      ,a.[Col1]
      ,a.[Col2]
      ,a.[Col3]
	  ,a.[Col4]
  , CASE
		WHEN a.[Col0] = 'BPR' 
			and   a.[Col4] = 'CHK' 
			and   b.[Col0] = 'TRN' 
			and   b.[Col2] LIKE '%000888888%' 
		THEN 'ACH'
		ELSE a.[Col4]
	END AS [Col4NEW]


FROM MainTable a

 LEFT JOIN (SELECT UniqueID, [Col0], [Col1],[Col2], [Col3], [Col4]
             FROM MainTable WHERE [Col0] = 'BB' ) b
                                              ON a.Filename = b.Filename
											

ORDER BY a.UniqueID ASC

Open in new window


Thank you again.
SQLCaseTest.xlsx
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Nope, in the "b" subquery you restrict to [col0] = 'BB', so you cannot check for b.[Col0] = 'TRN' (which is wrong anyway). b.[Col0] is always 'BB'.

Your new conditions are not the same as the initial ones - please be precise with such data, it really makes a big difference; changing conditions is confusing.
1
Queennie LAuthor Commented:
@Qlemo:

That's BB not TRN.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
That is? As said, please be precise. You also used a.[col0] = 'BPR', not a.[col0] = 'AA' as before.
0
Queennie LAuthor Commented:
@Qlemo:

That's my mistake. That's not BPR but AA. Sorry.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
So basically yes, if setting the conditions correctly, it should work.
SELECT a.[UniqueID]
      ,a.[Col0]
      ,a.[Col1]
      ,a.[Col2]
      ,a.[Col3]
      ,a.[Col4]
      ,CASE
         WHEN a.[Col0] = 'AA' and a.[Col4] = 'CHK'
          and b.[Col0] = 'BB' and b.[Col2] LIKE '%000888888%' 
         THEN 'ACH'
         ELSE a.[Col4]
	END AS [Col4NEW]
FROM MainTable a
LEFT JOIN (SELECT UniqueID, [Col0], [Col1],[Col2], [Col3], [Col4]
             FROM MainTable WHERE [Col0] = 'BB' ) b
          ON a.Filename = b.Filename
ORDER BY a.UniqueID ASC

Open in new window

The case condition and b.[Col0] = 'BB' is redundant, as b.Col0 is already known to be 'BB'. If there is no 'BB' row, b.[Col2] like ... cannot be true (the column value is NULL). I've left that in for clarity.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
awking00Commented:
A slightly shorter variation. -
select m.uniqueid, m.col0, m.col1, m.col2, m.col3, m.col4, coalesce(b.col4new, m.col4) col4new, m.filename
from maintable m
left join
(select filename, 'ACH' col4new
 from maintable
 where col0 = 'BB'
 and col2 like '%00888888%') b
on m.filename = b.filename
and m.col0 = 'AA'
and m.col4 = 'CHK'
order by m.uniqueid;
0
Queennie LAuthor Commented:
@awking00 and @Qlemo:

I will test these queries and I will let you know as soon as I finish.

Thank you again.
0
Queennie LAuthor Commented:
How to distribute points? There is no options available. Thanks.
0
Queennie LAuthor Commented:
Thank you for all your help.

I really appreciate it.

Have a good weekend!
0
Queennie LAuthor Commented:
Thank you for all your help again.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.