If Statement

Question

If Statement

Viewing 7 posts - 16 through 21 (of 21 total)

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

crazy_new SSCarpal Tunnel Points: 4804 More actions · Answer 1

Yeah that worked thanks, its not selecting that column(ADR3) anymore. But its not giving me any data, just showing the column titles.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Then the where clause you have matches no rows.

Probably because of this:

IF @ADR3 IS NULL

SELECT ADR1, ADR2, POSTAL_CODE

FROM PAT_NAMES_ADDRESS

WHERE CURRENT_ONE = RTRIM(@CURRENT_ONE)

AND ADR1 = RTRIM(@ADR1)

AND ADR2 = RTRIM(@ADR2)

AND ADR3 = RTRIM(@ADR3)

AND POSTAL_CODE = RTRIM(@POSTAL_CODE)

As mentioned earlier in this thread, Column = NULL will return no rows (because NULL is never = to anything). You've run an IF checking whether or not @ADR3 IS NULL and if it is you're running a select which looks for rows WHERE ADR3 = NULL, which is never true.

Might be worth doing some reading on NULL handling in SQL.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Greg Edwards-268690 SSC-Insane Points: 20680 More actions · Answer 3

crazy_new (1/28/2014)
Yeah there are more than one, but there aren't duplicates, one address can be used more than once, so I need to create a view that I can query later on with the specific criteria for each one.

Posting some sample data might help.

You say there aren't duplicates, but something else must make them unique.

If John and Sam share the same address, and one has ADD3 NULL and the other has a value, how do you know which one to retrieve?

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

crazy_new (1/28/2014)
Yeah there are more than one, but there aren't duplicates, one address can be used more than once...

Insofar as the address is concerned, those are duplicates.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

crazy_new SSCarpal Tunnel Points: 4804 More actions · Answer 5

Thanks for everyone who helped, I finally got it working just before end of the day yesterday.

Just one more question, does anyone know the syntax to use this in a view?

I have:

CREATE VIEW view_name

As

Declare..........

....................

This is where my code starts, but it says incorrect syntax near Declare.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

A view is a single select statement. It cannot have any other commands in it.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Dup.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass