Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

If Statement Expand / Collapse
Author
Message
Posted Tuesday, January 28, 2014 3:38 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 27, 2014 10:04 PM
Points: 60, Visits: 71
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.
Post #1535365
Posted Tuesday, January 28, 2014 4:25 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:44 AM
Points: 42,822, Visits: 35,953
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 2008, MVP
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

Post #1535376
Posted Tuesday, January 28, 2014 5:07 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 27, 2014 10:04 PM
Points: 60, Visits: 71
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.
Post #1535388
Posted Tuesday, January 28, 2014 5:54 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 27, 2014 10:04 PM
Points: 60, Visits: 71
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)


Post #1535396
Posted Tuesday, January 28, 2014 5:59 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:44 AM
Points: 42,822, Visits: 35,953
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 2008, MVP
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

Post #1535398
Posted Tuesday, January 28, 2014 6:08 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 27, 2014 10:04 PM
Points: 60, Visits: 71
Yeah that worked thanks, its not selecting that column(ADR3) anymore. But its not giving me any data, just showing the column titles.
Post #1535401
Posted Tuesday, January 28, 2014 6:41 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:44 AM
Points: 42,822, Visits: 35,953
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 2008, MVP
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

Post #1535410
Posted Tuesday, January 28, 2014 8:21 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 9:11 AM
Points: 1,232, Visits: 6,641
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?
Post #1535482
Posted Tuesday, January 28, 2014 11:50 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:11 AM
Points: 36,995, Visits: 31,516
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1535575
Posted Tuesday, January 28, 2014 10:46 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 27, 2014 10:04 PM
Points: 60, Visits: 71
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.
Post #1535707
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse