asked on

SQL If/Then ?

Experts,

I've been searching the web for an example of what I'm trying to accomplish, but unfortunately have kept coming up blank. (This is where you guys come in :) )

I need to figure out an IF/THEN statement of SQL. Here's the scenario.

I have the following table concerning inventory.
ID LEFT(@@SERVERNAME,4) RoomNumber OffSiteCode

Basically, SQL is returning me values of stuff, including our 4 digit naming convention in this table. There's been a requirement to support off-site equipment, so I added the "OffSiteCode" column to the table. What I need to do, is the following: If ANY value is present in the "OffSiteCode", then use what's listed there instead of what's in the "LEFT(@@SERVERNAME,4)" column, but if the value is NULL then use what's in the "LEFT(@@SERVERNAME,4)" column.

Here's the current SQL query that's running it all:

SELECT     dbo.v_Site_PrinterSubNameCreation.Id, LEFT(@@SERVERNAME, 4) 
                      + '-' + dbo.v_Site_PrinterSubNameCreation.PrinterLocation + '-' + dbo.v_Site_PrinterSubNameCreation.ThreeDigitDesig + dbo.v_Site_PrinterSubNameCreation.ColorNomen
                       + dbo.v_Site_PrinterSubNameCreation.PrinterRole AS PrinterName, dbo.v_Site_PrinterSubNameCreation.PrinterLocation, dbo.Site_PrinterInventory.OffSiteCode
FROM         dbo.v_Site_PrinterSubNameCreation INNER JOIN
                      dbo.Site_PrinterInventory ON dbo.v_Site_PrinterSubNameCreation.Id = dbo.Site_PrinterInventory.Id

Open in new window

SOLUTION

Jim Horn

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

usslindstrom

ASKER

Reading over my question, I see it's probably pretty confusing. I'll give a better example:

Basically I have a table like so:

ID PrinterName OffSiteCode
1 ABCD-101-123AB
2 ABCD-102-234AB
3 ABCD-103-345AB ZYXW
4 ABCD-104-456AB

What I need to happen, is have the "PrinterName" column for ID #3 return the value "ZYXW-103-345AB" instead of the auto generated one. For all of the other records, I need them to use the auto generated name. If this makes sense.

So, the following values would be returned on that same table:

ID PrinterName OffSiteCode
1 ABCD-101-123AB
2 ABCD-102-234AB
3 ZYXW-103-345AB ZYXW
4 ABCD-104-456AB

Hope this makes a little more sense than my first question/explination.

usslindstrom

ASKER

Oh - just saw your guys' two posts (Was editing my last comment before I noticed them). Reading them now.

usslindstrom

ASKER

Thanks guys.

I see what you did there ACPerkins. Actually, it's EXACTLY what I needed to accomplish here.

I still don't understand it 100% yet on how that did what I needed, but a cut/paste into the table of your code there works perfectly! Thank you VERY much for the assistance.

I'm reading both your suggestions though, as I've always been trying to get a better understanding of SQL as a whole. It's a MUCH more complicated beast than something like VB. :)

In any case, thanks again to you both. I went with ACPerkin's solution in my case as it was exactly what I needed to do.