SQLTeam.com | Weblogs | Forums

SQL Beginner - Assistance with Multiple Column assigned to Variable Based on Column Value & Multiple Case Instances

Hi! I am new to SQl. My programming background is in SAS. I am currently using SQL Server Management Studio 18.5. I am trying to determine how to solve a couple of problems:

(1) I want to assign new variables to existing columns, depending on the value of those columns. I have read about declaring variables using set and select, but I am having trouble translating that information to what I need to do. I have a view which contains several thousand records. I want the variable logic applied to each record in the view. The desired result is that I create a new view with just the new variable name and other associated data, excluding the original column names.

In SAS, my code would look like:

If newctry<>' ' then countryname=newctry; else countryname=mailingcountry;

Both newctry and mailingcountry are columns in the view I am pulling data from.

I have different instances of this type of situation. Do I need to create subqueries for each of these instances? If someone could provide an example of how this might work in SQL, I would really appreciate it.

An example of the SQL code I have tried unsuccessfully is:

Declare @countryname VARCHAR(50)
Select recordid, recorddate, newctry, mailingcountry
from viewname
Select @countryname=newctry from viewname where newctry<>' '
Select @countryname=mailingcountry from viewname where newctry=' '

(2) The other question I have is that I have received errors when I have included multiple CASE statements with different END names in in Select statement. Do I need subqueries for each CASE statement?

Thanks!

Select case when newctry  <> ' '   then @countryname=newctry 
            when newctry   =  ' '  then @countryname=mailingcountry 
        end 
from viewname 

i have not tested this SQL .. hope it works ..

you cannot assign multiple rows to a variable @countryname
in the instance where from viewname where newctry<>' ' beings back 5 rows

Select @countryname=newctry from viewname where newctry<>' '

will give you a wrong result. What are you trying to achieve, what is your end game in these queries?

create table #sas(newctry varchar(50), mailingcountry varchar(50))

insert into #sas
select ' ', 'Abyssinia' union
select 'Sicily', 'Italia' union
select 'Crete', 'Greece' union
select ' ', 'sds' 


Select @countryname=newctry from #sas where newctry<>' '

select @countryname;
--in this case it picks up Sicilly

drop table #sas

Thanks for your reply! I have several thousands of records that I am processing for data cleansing. The end result that I need is a table or view that has only columns with valid data. I am trying to create a new column which would contain either the values from the existing newctry column or the mailingcountry column based on whether the newctry value is blank or not. This logic would need to be applied to every record in the existing view. I have several different fields (columns) that need some evaluation of the values they contain to transform the data to a new column with valid information. Please let me know what you would recommend. I appreciate your assistance.

use sqlteam
go

declare @countryname varchar(1500)

create table #sas(newctry varchar(50), mailingcountry varchar(50))

insert into #sas
select ' ', 'Abyssinia' union
select 'Sicily', 'Italia' union
select 'Crete', 'Greece' union
select ' ', 'sds' 

--Maybe this is what you want?
Select newctry,
       mailingcountry,
       case when newctry  <> ' '   then newctry 
            when newctry   =  ' '  then mailingcountry 
        end _check
from #sas 

drop table #sas
1 Like

Thanks so much! I think the case section you provided is what I needed. I really appreciate the help. I have a follow-up question. I will need multiple case statements in my query. When I include a second case statement, I receive a syntax error. Do multiple case statements need to be formatted a certain way? Below is an example:

select

FIRSTNAME,
LASTNAME,
MAILINGCOUNTRY,
homenewctry,

case
when homenewctry<>' ' and homenewctry is not Null then homenewctry
when homenewctry=' ' then MAILINGCOUNTRY
else ' '
end newctry

case
when FIRSTNAME<>' ' then FIRSTNAME
when FIRSTNAME=' ' and LASTNAME<>=' ' then LASTNAME
else ' '
end name

from tablename

The error message I receive is:

Msg 156, Level 15, State 1, Line 16
Incorrect syntax near the keyword 'case'.
Msg 102, Level 15, State 1, Line 20
Incorrect syntax near 'name'.

Thanks for your help!

I think you need a comma after end newctry,

1 Like

Thanks, but that didn't work. I received the following error for a different location:

Msg 102, Level 15, State 1, Line 16
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Line 18
Incorrect syntax near 'name'.

I appreciate your help! I will do some additional research to see if I can find examples of multiple cases in select statement.

Hi! Ignore my previous comment. The problem was in the syntax within the case statement. The comma worked as you suggested.

I really appreciate your quick response and assistance!!!!

There a couple of things you can do to 'simplify' the expressions...

SELECT ...
     , homenewctry = iif(homenewctry <> '', homenewctry, mailingcountry)
     , [name] = iif(firstname = '' and lastname <> '', lastname, '')
FROM ...

IIF is really just a shortcut to a case expression - but it can simplify the code and make it a bit easier to read.

Note: if you check a column that can be NULL to be not equal to '' - you don't also need to check for it being NULL.

Another method you can use is to force blank values to NULL and then return the first non-null value:

SELECT ...
     , homenewctry = coalesce(nullif(homenewctry, ''), mailingcountry)
     , [name] = coalesce(nullif(firstname, ''), nullif(lastname, ''), '')
FROM ...

NULLIF will return a null value if the actual value is NULL or empty string - and COALESCE will then return the first non-null value.

Personally - I prefer the latter method using coalesce and nullif when possible.

1 Like

Thanks! I appreciate the recommendation. I was not familiar with the iif and nullif options. I do like the simplified expression.

Thanks again for taking the time to help!