January 28, 2014 at 2:53 am
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?
January 28, 2014 at 3:04 am
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
January 28, 2014 at 3:13 am
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?
January 28, 2014 at 3:17 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 28, 2014 at 3:21 am
Just below that I set the value.
SET @ADR3 = (SELECT ADR3
FROM PAT_NAMES_ADDRESS
WHERE ADR1 = @ADDRESS)
SET @ADDRESS = 'M & B Pumps Division'
January 28, 2014 at 3:23 am
You need to do it before, not after.
John
January 28, 2014 at 3:26 am
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.
January 28, 2014 at 3:27 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 28, 2014 at 3:29 am
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
January 28, 2014 at 3:30 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 28, 2014 at 3:38 am
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.
January 28, 2014 at 4:25 am
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
January 28, 2014 at 5:07 am
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.
January 28, 2014 at 5:54 am
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)
January 28, 2014 at 5:59 am
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
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply