IF statement type Query needed

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

IF statement type Query needed

Author

Topic

crh88
Starting Member

14 Posts

Posted - 2015-01-08 : 10:16:39

Hello there,

I am looking for a query which will populate one of my columns that contains some blanks with a defined value based on a value in another column

For example in the below table I would like the blanks in serial number to be populated with "SN09"

Product_Name Serial_Number
PRODUCT1
PRODUCT1
PRODUCT2 SN10
PRODUCT3 SN11
PRODUCT3 SN11
PRODUCT1

So the query I am looking for would basically add some values to the results where they meet the right criteria.

I am running the query through a view so I dont actually want to add the values to the physical tables only to the view of the results if you understand what I mean?

Best Regards,

crh88
Starting Member

14 Posts

Posted - 2015-01-08 : 10:18:03

Just to be clear the criteria for the blank being "SN09" is PRODUCT1 In the first column

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-08 : 10:21:01

select product_name,
, isnull(serial_number,
CASE product_name
WHEN 'PRODUCT1' THEN 'SN09'
END
) as Serial_Number
from myView

crh88
Starting Member

14 Posts

Posted - 2015-01-08 : 10:52:38

thanks bud,

Is there a way I can also make any values which are NULL in the serial number field blanks as part of this query?

regards,

crh88
Starting Member

14 Posts

Posted - 2015-01-08 : 11:07:39

also bud - I'm wondering is there any way I can get the serial number column to be the first column or does the CASE Statement always have to have them on the end of the data?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-08 : 11:35:19

You can write:

CASE
WHEN Serial_Number IS NULL AND Product_Name = 'PRODUCT1' THEN 'SN09'
ELSE Serial_Number
END As Serial_Number

And, the case expression can be anywhere in the select list.

crh88
Starting Member

14 Posts

Posted - 2015-01-08 : 15:57:38

cheers bud, really appreciate your help with this.

crh88
Starting Member

14 Posts

Posted - 2015-01-09 : 09:02:11

Okay I tried that code today - but I'm still getting some "NULL" Returned in my new serial number column.

Is there a way I can tweak the code and make these appear as blanks instead?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-09 : 09:07:47

add a second WHEN clause
WHEN Serial_Number is NULL then ' '

crh88
Starting Member

14 Posts

Posted - 2015-01-09 : 09:28:28

No worries on that one bud - i have just managed to get this by splitting the WHEN clause into two seperate WHen clauses

CASE
WHEN Serial_Number IS NULL THEN ''
WHEN Product_Name = 'PRODUCT1' THEN 'SN09'
ELSE Serial_Number
END As Serial_Number

But I am still a little confused as to how I get the new Serial Number column created as the first column rather than at the end of the data?

regards,

crh88
Starting Member

14 Posts

Posted - 2015-01-09 : 09:28:53

sorry just seen your reply

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-09 : 09:42:19

[code]
select CASE ...
END AS Serial_Number
, Product_Name
from myView
[/code]

crh88
Starting Member

14 Posts

Posted - 2015-01-09 : 09:47:56

cheers bud