Solved

How to write a case  statement in sql server for the status change

Posted on 2011-09-20
4
353 Views
Last Modified: 2012-06-27
Hi Please help me in writing the query for the following:

I have a scenario where i need to write the query for status change.

we have  2 columns one is Id column which populates the parent and child id c1, c2, c3, c4 values and Status with Yes or NO. If there is any change in parent Id value, then it populates with the  5 alpha numeric value like '1ad2h' in a row and if there is a change in child value ' then it populates with the value which contains alphabet 'v' in it in one of the 5 alpha numeric values  like for eg ' 2V3w3'. We need to identify the change in the child values and if there is any change as  the child value is identified by 'v' alphabet I guess in case statement we have to use wild characters to get the change value then the status column should be updated with Yes or Y.
Similiarly if Parent value and child values does not have any v value in it then it should return N or no

     P1                              c1                 c2                     c3                   status
    'a2bd3'                     'a2cdv'             2ah88'            'dk23k'               'Y'
     'd2ks2'                      'ekdkd'           'dkks2'           'd2kdk'                'N'

Like wise there are different elements like java , cobol etc each have different parent values and within the parent values we have 3 Child values. and the scenario will be the same as stated above....

Please gimme some idea how to write the query for this scenario...





0
Comment
Question by:srionline2k6
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 32

Assisted Solution

by:Ephraim Wangoya
Ephraim Wangoya earned 500 total points
ID: 36570763
try
select P1, c1, c2, c3, case when charindex('v', StatusStr, 1) > 0 then 'Y' else 'N' end [Status]
from
(
	select P1, c1, c2, c3, 
		ISNULL(P1, '') + ISNULL(c1, '') + ISNULL(c2, '') + ISNULL(c3, '') [StatusStr]
	from table1
) A

Open in new window

0
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 500 total points
ID: 36570765
if you prefer cte then


;with cte as
(
        select P1, c1, c2, c3, 
		ISNULL(P1, '') + ISNULL(c1, '') + ISNULL(c2, '') + ISNULL(c3, '') [StatusStr]
	from table1
)

select P1, c1, c2, c3, case when charindex('v', StatusStr, 1) > 0 then 'Y' else 'N' end [Status]
from cte

Open in new window

0
 
LVL 54

Expert Comment

by:Huseyin KAHRAMAN
ID: 36570884
read many times, but could not understand :)
are these two lines from your table? if yes, it already has status column... do you want select or update query?
0
 

Author Closing Comment

by:srionline2k6
ID: 36571439
Thanks for the answers
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question