SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


If Statement


If Statement

Author
Message
crazy_new
crazy_new
Old Hand
Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)

Group: General Forum Members
Points: 391 Visits: 375
Not to sure. There are more than 1 records but there are not duplicates.

Its just that there can be multiple entries for each address.

I need to create a view so that the view can be queried.

I assume that the criteria of which record of the bunch will be specified when querying the view.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87131 Visits: 45267
crazy_new (1/28/2014)
Not to sure. There are more than 1 records but there are not duplicates.

Its just that there can be multiple entries for each address.


Ok, so when an address is specified and there are multiple rows with that value for ADR1, what determines which row's ADR3 you want?

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


crazy_new
crazy_new
Old Hand
Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)

Group: General Forum Members
Points: 391 Visits: 375
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.
crazy_new
crazy_new
Old Hand
Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)

Group: General Forum Members
Points: 391 Visits: 375
HI,

I have changed my code up so get only the specific record needed.

Here is what it look like now.

Once again I don't get any errors, but its like only the bottom IF works, because if ADR3 is null, it still selects the column even if its not in my if statement.



DECLARE @ADR1 VARCHAR(100), @ADR2 VARCHAR(100), @ADR3 VARCHAR(100), @POSTAL_CODE VARCHAR(100), @CURRENT_ONE SMALLINT


SET @ADR1 = 'P O Box 2258'
SET @ADR2 = 'Johannesburg'
SET @ADR3 = 'null'
SET @POSTAL_CODE = '2000'
SET @CURRENT_ONE = '1'



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)

ELSE IF @ADR3 IS NOT NULL

SELECT ADR1, ADR2, ADR3, 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)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87131 Visits: 45267
SET @ADR3 = 'null'

That's setting the variable to the string value n-u-l-l. If you want to check whether a variable has the string value "null", then you'd need IF @Adr3 = "Null"

If you want to set and check for the absence of a value (NULL), then it's
SET @Adr3 = NULL
IF @Adr3 IS NULL ...

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


crazy_new
crazy_new
Old Hand
Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)

Group: General Forum Members
Points: 391 Visits: 375
Yeah that worked thanks, its not selecting that column(ADR3) anymore. But its not giving me any data, just showing the column titles.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87131 Visits: 45267
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
Greg Edwards-268690
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1522 Visits: 8485
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
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85861 Visits: 41091
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
crazy_new
crazy_new
Old Hand
Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)

Group: General Forum Members
Points: 391 Visits: 375
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search