IF THEN ELSE SQL STATEMENT

Question

IF THEN ELSE SQL STATEMENT

bpowers

SSCertifiable

Points: 5483
More actions
January 14, 2009 at 1:30 pm

#205107

What am I doing wrong?
IF Cust_Address_v.ID IS NULL
THEN
SELECT ID, NAME, ADDR_1, ADDR_2, ADDR_3, CITY, STATE, ZIPCODE, COUNTRY,
FROM CUSTOMER
WHERE ID = 'S28171'
ELSE
SELECT ID, Addr_No, Name, Addr_1, Addr_2, City, State, Zipcode, Country, Addr_3
FROM Cust_Address_v
WHERE ID = 'S28171'

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

bpowers (1/14/2009)
What am I doing wrong?
IF Cust_Address_v.ID IS NULL
THEN
SELECT ID, NAME, ADDR_1, ADDR_2, ADDR_3, CITY, STATE, ZIPCODE, COUNTRY,
FROM CUSTOMER
WHERE ID = 'S28171'
ELSE
SELECT ID, Addr_No, Name, Addr_1, Addr_2, City, State, Zipcode, Country, Addr_3
FROM Cust_Address_v
WHERE ID = 'S28171'

Cust_Address_v.ID should be a variable like @Cust_Address_id not a column...

* Noel

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

Where does Cust_Address_v.ID come from? I assume you it si from a view. You would need to do something like this:

If Exists(Select 1 from Cust_Address_v Where ID is null) Then

Begin

End

Else

Begin

End

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

bpowers SSCertifiable Points: 5483 More actions · Answer 3

I am trying to build a view that looks at our cust_address table data. However, there will be times when this information will be null. In that case I want it to pull in the data from the customer table. What is the best approach?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

IF Cust_Address_v.ID IS NULL

Which row?

It's not immediately apparent from the code what you're trying to do here, because depending upon what your result is from this conditional - which depends upon the value from a row of a table, you select from one of two tables.

Can you explain in words what you're trying to achieve?

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

GSquared SSC Guru Points: 260824 More actions · Answer 5

You can't use flow-control like If...Else in views. You can in procs and user-defined functions. Are you actually trying to do this in a view?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

bpowers SSCertifiable Points: 5483 More actions · Answer 6

I was, but from your post it sounds as if it is not possible. I'm learning as I go.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 7

bpowers (1/14/2009)
I am trying to build a view that looks at our cust_address table data. However, there will be times when this information will be null. In that case I want it to pull in the data from the customer table. What is the best approach?

If you post your table defintions and what you need we may be able to help.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

noeld SSC Guru Points: 96590 More actions · Answer 8

bpowers (1/14/2009)
I am trying to build a view that looks at our cust_address table data. However, there will be times when this information will be null. In that case I want it to pull in the data from the customer table. What is the best approach?

may be with this "hack"?

CREATE VIEW hacked_view

AS

SELECT ID, NAME, ADDR_1, ADDR_2, ADDR_3, CITY, STATE, ZIPCODE, COUNTRY,

FROM CUSTOMER

WHERE ID = 'S28171'

UNION

SELECT ID, Addr_No, Name, Addr_1, Addr_2, City, State, Zipcode, Country, Addr_3

FROM Cust_Address_v

WHERE ID = 'S28171'

I am not entirely sure what your "object" is (view, stored procedure, function ) though.

* Noel

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 9

bpowers (1/14/2009)
I was, but from your post it sounds as if it is not possible. I'm learning as I go.

If it's within a view - then you're looking for the CASE statement. Might be worth the read.....

select blah,

CASE when blah is null then '123' ELSE blah END DefaultBlah

from

.....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

GSquared SSC Guru Points: 260824 More actions · Answer 10

How are you planning on using this? Is it something that a web page or other application will use to look up the address(es) for a specific customer? Or something that will be used to look up all customers for an address book? Or something that will be used by other stored procedures to pull data that will be used for a variety of other things? Or something else that I haven't thought of?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon