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?
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
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'.