Using case statement to view modify value within same column.

Hi,
How to use case only to change several value in the column but still preserve the rest of the column values? The problem with the syntax below, it produce additional column as name [Vw-Model] but what I want is on existing column when the query found 'Kitara' then it will turn to 'Kitara-2'

seelct *, case when [Model18]='Kitara'  then 'Kitara-2' ELSE 'Group Error'
END AS  [Model_Grouping]  from [Vw-Model]
where group_name ='CD3008'

Open in new window

motioneyeAsked:
Who is Participating?
 
PortletPaulfreelancerCommented:
You CANNOT refer to a new column alias in the same select clause (the yellow bit below is not possible)

select ...
  , ltrim(stuff(t.Model_Name,1,3,'')) as [Model 2]
...
  ,case when  [Model 2] ='Kitara'  then 'Kitara-2' ELSE [Group_Name_2]

But you can refer to an alias created in a subquery:
SELECT
    ROW_NUMBER() OVER (ORDER BY t.[GROUP_NAME]) AS Number
  , '-' ' '
  , t.[Model 2]
  , REPLACE(t.[MODEL_NAME], 'Kitara', 'Kitara-2') AS [Model_Name]
FROM (
      SELECT DISTINCT
          [MODEL_NAME]
        , LTRIM(STUFF(MODEL_NAME, 1, 3, '')) AS [Model 2]
      FROM [Vw-Model]
     ) t

Open in new window

1
 
OMC2000Commented:
If I properly understand your question, you could just list all columns and put case expression for  [Model18]:

seelct group_name, [Model1], [Model2], [Model3], case when [Model18]='Kitara'  then 'Kitara-2' ELSE  [Model18]
END AS  [Model18]  from [Vw-Model]
where group_name ='CD3008'

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I didn't understand the question. Can you support it with sample results?
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.

 
motioneyeAuthor Commented:
Hi,
Maybe I must use replace function instead of  using case expression,

select 
  row_number() over (order by t.[GROUP_NAME]) as Number
 ,'-' ' '
 ,t.[MODEL_NAME] 
 ,ltrim(stuff(t.Model_Name,1,3,'')) as [Model 2] 
  ,[Model_Name]=replace (t.[MODEL_NAME],'Kitara','Kitara-2')
from

(select distinct
  gs.[MODEL_NAME]
  from Vw-Model gs

) as t

order by t.[MODEL_NAME]

Open in new window


The above query will  includes  additional column [Model_NAME], but what I want is the query that will replace the value  within [Model 2] without creating additional column

Number             MODEL_NAME      [MODEL 2]      MODEL_NAME
1      -      TSV Access Apps      Access App Analytics      Access Apps
2      -      TSV  Virana 2 Production      Virana 2 Production      Virana 2 Production
3      -      TSV  BRIM Application      BRIM Application       BRIM Application
4      -      TSV  Kitara      Kitara      Kitara-2
0
 
PortletPaulfreelancerCommented:
Not sure I really follow the question, but if you wish to alter the output of just those models containing "Kitara" and leave anything else "as is" then try:
case when [MODEL_NAME] like '%Kitara%' then 'Kitara-2' else [MODEL_NAME] end

Open in new window


---
fyi
an "expression" evaluates to a single value (per row)
so the more correct term to describe using case within a select or update statement is "case expression"

A "case statement" doesn't really exist in T-SQL but it does in Oracle. This is the use of syntax similar to case used in a select statment, but used as a branching mechanism to execute different stored procedures.
0
 
Mark WillsTopic AdvisorCommented:
just use the same column name again

so
select case when [model] = 'Kitara' then 'Kitara-2' else [model] end as [model]

Open in new window


Does that make sense ?
0
 
motioneyeAuthor Commented:
Hi Marks,
yes I thought that it will works, apparently it was not, the query added extra column to the end. But do you know how do I combine case with Ltrim function below ? if I  comment out the case syntax it turn error , I guess due to non-existance of [Model_2]

select 
  row_number() over (order by t.[GROUP_NAME]) as Number
 ,'-' ' '
 ,t.[MODEL_NAME] 
 ,ltrim(stuff(t.Model_Name,1,3,'')) as [Model 2] 
  ,[Model_Name]=replace (t.[MODEL_NAME],'Kitara','Kitara-2')
  --,case when [Model 2]='Kitara'  then 'Kitara-2' ELSE [Group_Name_2]
from

(select distinct
  gs.[MODEL_NAME]
  from Vw-Model gs

) as t

order by t.[MODEL_NAME]

Open in new window

0
 
Mark WillsTopic AdvisorCommented:
Well there is a whole pile of [model], [model 2] [model name] [group name] columns, and a little unsure of which column needs to be used, so in my example, I used a generic [model].

You could use either a replace or a case within your ltrim(stuff(<column>,1,3,'')) by substituting  <column> with the required definition

Also note that your row_number() uses t.[GROUP_NAME] but isnt in the derived table / subquery denoted by t

But looking at your query, it would be something like
select 
  row_number() over (order by t.[GROUP_NAME]) as Number
 ,'-' ' '
 ,t.[MODEL_NAME] 
 ,ltrim(stuff(replace(t.model_name,'Kitara','Kitara-2'),1,3,'')) as [Model 2] 
  from

(select distinct
  gs.[MODEL_NAME]
  from Vw-Model gs

) as t

order by t.[MODEL_NAME]

Open in new window

Or using a CASE
select 
  row_number() over (order by t.[GROUP_NAME]) as Number
 ,'-' ' '
 ,t.[MODEL_NAME] 
 ,ltrim(stuff((case when [Model_Name] like '%Kitara'  then '   Kitara-2' ELSE [Model_Name]),1,3,'')) end as [Model 2] 
from

(select distinct
  gs.[MODEL_NAME]
  from Vw-Model gs

) as t

order by t.[MODEL_NAME]

Open in new window

Or better still, as PortletPaul suggests (maybe confusing column names ie the introduced column for Kitara-2 s/b [model 2] ), get those ltrim() + stuff() manipulations out of the way first in the subquery
SELECT
    ROW_NUMBER() OVER (ORDER BY t.[GROUP_NAME]) AS Number
  , '-' ' '
  , t.[Model_Name]
  , case when  t.[Model 2] ='Kitara'  then 'Kitara-2' ELSE t.[Model 2] end AS [Model 2]
FROM (
      SELECT DISTINCT
          [MODEL_NAME]
        , LTRIM(STUFF(MODEL_NAME, 1, 3, '')) AS [Model 2]
      FROM [Vw-Model]
     ) t

Open in new window

0
 
motioneyeAuthor Commented:
Hi Marks,
 The only  stopper with PortletPaul suggested code is  the trimmed column will not display within the result set, simply because its was   subquery, so I'm trying to combine the ltrim and the case together so that  teh result will on row based operation instead of another column has to be introduced.

I'm trying with your suggested code below. but it returned me some error message

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ')'.
Msg 156, Level 15, State 1, Line 15
Incorrect syntax near the keyword 'as'.


select 
  row_number() over (order by t.[GROUP_NAME]) as Number
 ,'-' ' '
 ,t.[MODEL_NAME] 
 ,ltrim(stuff((case when [Model_Name] like '%Kitara'  then '   Kitara-2' ELSE [Model_Name]),1,3,'')) as [Model 2] 
from

(select distinct
  gs.[MODEL_NAME]
  from Vw-Model gs

) as t

order by t.[MODEL_NAME]

Open in new window

0
 
PortletPaulfreelancerCommented:
The only  stopper with PortletPaul suggested code is  the trimmed column will not display within the result set, simply because its was   subquery

but that isn't true....

In the final result set the original [Model 2] CAN be displayed (if you want it to) e.g.:
SELECT
    ROW_NUMBER() OVER (ORDER BY t.[GROUP_NAME]) AS Number
  , '-' ' '
  , t.[Model_Name]
  , t.[Model 2]
  , case when  t.[Model 2] ='Kitara'  then 'Kitara-2' ELSE t.[Model 2] end AS [NEW Model 2]
FROM (
      SELECT DISTINCT
          [MODEL_NAME]
        , LTRIM(STUFF(MODEL_NAME, 1, 3, '')) AS [Model 2]
      FROM [Vw-Model]
     ) t

Open in new window

1
 
motioneyeAuthor Commented:
Hi PortletPaul
The  column that you were mentioned  [Model 2]  was actually non-existence column, it as introduce as part of the "AS [Model 2]" maybe u have missed look this from my previous reply. Right now I'm trying with Mark suggestion. #ID: 42484932 But I got error when I run the query...
0
 
PortletPaulfreelancerCommented:
Yes I  am fully aware that the new column is generated by the subquery. After that you can use and refer to that new column like any other column, that is the whole point really.

What you cannot do is create that column alias and then use that same alias in a single select clause. By using the subquery there are 2 select clauses.
1
 
Mark WillsTopic AdvisorCommented:
missing the 'end', so typed it up and tested - had to make some changes (annotated) for test in your t subquery.
select 
  row_number() over (order by t.[GROUP_NAME]) as Number
 ,'-' ' '
 ,t.[MODEL_NAME] 
 ,ltrim(stuff( (case when [Model_Name] like '%Kitara'  then '   Kitara-2' ELSE [Model_Name] end),1,3,'')) as [Model 2] 
from

(select distinct
  gs.[MODEL_NAME],[group_name]                 -- added group_name for testing
  from Vw_Model gs                                           -- changed to Vw_Model

) as t

order by t.[MODEL_NAME]

Open in new window

and a little disappointed you chose that one instead of trying
SELECT
    ROW_NUMBER() OVER (ORDER BY t.[GROUP_NAME]) AS Number
  , '-' ' '
  , t.[Model_Name]
  , case when  t.[Model 2] ='Kitara'  then 'Kitara-2' ELSE t.[Model 2] end AS [Model 2]
FROM (
      SELECT DISTINCT
          [MODEL_NAME]
        , LTRIM(STUFF(MODEL_NAME, 1, 3, '')) AS [Model 2]
      FROM [Vw-Model]
     ) t

Open in new window

as inspired by portletpaul
1
 
motioneyeAuthor Commented:
Hi PortletPaul,
 I have test and test again your suggested code ID: 42484936 and that is true it can be use, it was my mistakes and confused myself with the columns...
1
 
PortletPaulfreelancerCommented:
The first correct answer (to the modified question) was at ID:42484870 (then clarified via ID: 42484936) and that code ultimately acknowledged as tested at ID: 42491397
0
 
Mark WillsTopic AdvisorCommented:
@PortletPaul,

You were being awarded "Best" for the post as acknowledged by motioneye

My comment about "maybe confusing column names" has been amended to include the explanation "ie the introduced column for Kitara-2 s/b [model 2] ".  Something that motioneye also raised.

I do believe I assisted the solution - or is that what you are objecting to ?
0
 
PortletPaulfreelancerCommented:
No, I'm fine with shared contribution, just that that ID:42484870 should be "accepted answer" in my view, it will also me more meaningful as a PAQ item wheras 42484936 would not be so useful without first reading 42484870
0
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.

All Courses

From novice to tech pro — start learning today.