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 2:53 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:01 AM
Points: 81, Visits: 101
The following is an example of an IF statement im busy with. It has to check if ADR3 is null, if it is it should only select ADR1, ADR2 and postal code. If its not null it should also select ADR3. There are no syntax errors or anything, it just looks at the first IF, so it doesn't return ADR3, whether it is Null or not.



DECLARE @ADR3 VARCHAR(100), @ADDRESS VARCHAR(100)

SET @ADR3 = (SELECT ADR3
FROM PAT_NAMES_ADDRESS
WHERE ADR1 = @ADDRESS)

SET @ADDRESS = 'P O Box 2258'

IF @ADR3 IS NULL


SELECT ADR1, ADR2, POSTAL_CODE
FROM PAT_NAMES_ADDRESS
WHERE ADR1 = @ADDRESS


ELSE IF @ADR3 IS NOT NULL

SELECT ADR1, ADR2,ADR3 , POSTAL_CODE
FROM PAT_NAMES_ADDRESS
WHERE ADR1 = @ADDRESS


How can I fix this if it doesn't give me an error?
Post #1535345
Posted Tuesday, January 28, 2014 3:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:01 AM
Points: 5,382, Visits: 9,948
The first thing to try is to put a SELECT @ADR3 statement in just after you set the value of the variable. Then you can see whether the problem is that the variable is not being set correctly, or that your IF logic is not working as expected.

John
Post #1535348
Posted Tuesday, January 28, 2014 3:13 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:01 AM
Points: 81, Visits: 101
Hi John,

I di what you said: SET @ADR3 = (SELECT ADR3
FROM PAT_NAMES_ADDRESS
WHERE ADR1 = @ADDRESS)
SELECT @ADR3

The value it returns is null.
But it doesn't make sense because when I check it outside of an if statement:

SELECT ADR1, ADR2,adr3, POSTAL_CODE
FROM pat_names_address
WHERE ADR3 is not null
AND ADR1 = 'M & B Pumps Division'

It does return a value.

Why is that?
Post #1535350
Posted Tuesday, January 28, 2014 3:17 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 8:20 AM
Points: 39,960, Visits: 36,315
Where are you setting the value of @ADDRESS?

In your code, you have:

DECLARE @ADR3 VARCHAR(100), @ADDRESS VARCHAR(100)

SET @ADR3 = (SELECT ADR3
FROM PAT_NAMES_ADDRESS
WHERE ADR1 = @ADDRESS)

So you're declaring @ADDRESS then using it before setting a value. By default it's null, so unless you left out the code where you give @ADDRESS a value, your set boils down to this:

SET @ADR3 = (SELECT ADR3
FROM PAT_NAMES_ADDRESS
WHERE ADR1 = NULL)

Which will never return rows.



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 #1535352
Posted Tuesday, January 28, 2014 3:21 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:01 AM
Points: 81, Visits: 101
Just below that I set the value.


SET @ADR3 = (SELECT ADR3
FROM PAT_NAMES_ADDRESS
WHERE ADR1 = @ADDRESS)

SET @ADDRESS = 'M & B Pumps Division'
Post #1535354
Posted Tuesday, January 28, 2014 3:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:01 AM
Points: 5,382, Visits: 9,948
You need to do it before, not after.

John
Post #1535358
Posted Tuesday, January 28, 2014 3:26 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:01 AM
Points: 81, Visits: 101
When I do it before then I get the following error.

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Post #1535359
Posted Tuesday, January 28, 2014 3:27 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 8:20 AM
Points: 39,960, Visits: 36,315
crazy_new (1/28/2014)
Just below that I set the value.


SET @ADR3 = (SELECT ADR3
FROM PAT_NAMES_ADDRESS
WHERE ADR1 = @ADDRESS)

SET @ADDRESS = 'M & B Pumps Division'


So search for all rows matching a specific address, then after it's finished the search set the value to the address that should have been searched for?



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 #1535360
Posted Tuesday, January 28, 2014 3:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:01 AM
Points: 5,382, Visits: 9,948
OK, so fix that. When you set the value of a scalar variable, you can only pass a single value to it. If the SELECT statement you use to set it returns more than one row, you'll get that error.

John
Post #1535361
Posted Tuesday, January 28, 2014 3:30 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 8:20 AM
Points: 39,960, Visits: 36,315
crazy_new (1/28/2014)
When I do it before then I get the following error.

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.


That means you have more than one row which matches that value. Should you have duplicate rows? If you do have multiple rows which match that address, which one do 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 #1535362
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse