February 5, 2014 at 2:42 am
Hi, can someone please explain something to me.
SET @NAMEID = '14359'
IF @ADR2 IS NULL AND @ADR3 IS NULL
SELECT ADR1, POSTAL_CODE, (STATE_NAME) AS COUNTRY
FROM PAT_NAMES_ADDRESS P, STATE_NAME S
WHERE P.NAME_ID = @nameid
AND P.STATE_ID = S.STATE_ID
AND P.CURRENT_ONE = '1'
AND P.LANGUAGE_ID = '3'
In the Pat_names_address table, only one record can be current, if there are more the current = 0.
When i run the folowing code, I get 10 records back instead of one, all showing different countries, but the same addresses.
But I want the record where current one = 1, so obviously it should only return one record as in my code.
Why am I getting 10 different coutries?
February 5, 2014 at 3:01 am
At a guess I'd say you had multiple records in the STATE_NAME table with the same STATE_ID
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
February 5, 2014 at 3:13 am
SET @NAMEID = '14359'
IF @ADR2 IS NULL AND @ADR3 IS NULL
SELECT ADR1,
POSTAL_CODE,
(STATE_NAME) AS COUNTRY
FROM PAT_NAMES_ADDRESS AS P
INNER JOIN STATE_NAME AS S ON P.STATE_ID = S.STATE_ID
WHERE P.NAME_ID = @nameid
AND P.CURRENT_ONE = '1'
AND P.LANGUAGE_ID = '3'
Have a look at your join on the 2 tables
February 5, 2014 at 3:22 am
Thanks guys I figured it out.
I set the language_id from the wrong table to 3.
February 5, 2014 at 3:48 am
can someone please tell me how I can set the @ADR2 in the if statement equals to the actual column name?
February 5, 2014 at 3:54 am
Sorry not sure I follow the question... do you want to do something like
IF(@ADR2 = 'ADR2')
BEGIN
some code...
END
??
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
February 5, 2014 at 3:57 am
or are you after
SELECT CASE
WHEN COALESCE(@ADR2,'itsnull') <> 'itsnull' THEN ADR2
WHEN COALESCE(@ADR3,'itsnull') <> 'itsnull' THEN ADR3
ELSE ADR1
END AS 'ADDRESS'
, --the rest of your query
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
February 5, 2014 at 5:08 am
It still says iunvalid column name
February 5, 2014 at 5:09 am
which column name is invalid? is it definitely in your table?
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
February 5, 2014 at 5:13 am
Adr2 and Adr3.
I want to use the column name instead of the variable in the if statement, because i have to set the variable = to the column anyway.
Bit I cant say If ADR2 is null because it says invalid column name.
I dont get errors if I say IF @ADR2, but I dont get the correct output because I havent set @ADR2 = to the column.
February 5, 2014 at 5:19 am
If I set the variable to a value in the column, and then set the var = to the column it also work, but i dont want to use it like that, because i will only use the nameid to search and not the address as well
February 5, 2014 at 5:26 am
ok lets see if this is what you're after...
IF (EXISTS(
SELECT TOP 1 1
FROM PAT_NAMES_ADDRESS
WHERE NAME_ID = @NAMEID
AND ADR2 IS NULL
AND ADR3 IS NULL
))
BEGIN
SELECT ADR1,
POSTAL_CODE,
(STATE_NAME) AS COUNTRY
FROM PAT_NAMES_ADDRESS AS P
INNER JOIN STATE_NAME AS S ON P.STATE_ID = S.STATE_ID
WHERE P.NAME_ID = @NAMEID
AND P.CURRENT_ONE = '1'
AND P.LANGUAGE_ID = '3'
END
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
February 5, 2014 at 5:34 am
Let me explain a bit better what I need to do and then post my whole code.
I need to write a script, where i can insert a name id, and the it will return the adr1, adr2,adr3 postal code and country.
If any of those are null, it should not select that column i.e. if adr3 is null, it should only return the other columns. If adr2 and adr3 is null, it should select all the other columns except those to.
Here is all me code:
DECLARE @NAMEID int, @ADR2 VARCHAR(100), @ADR3 VARCHAR(100)
SET @NAMEID = '14388'
IF @ADR2 IS NULL AND @ADR3 IS NULL
SELECT ADR1, POSTAL_CODE, (STATE_NAME) AS COUNTRY
FROM PAT_NAMES_ADDRESS P, STATE_NAME S
WHERE P.NAME_ID = @nameid
AND P.STATE_ID = S.STATE_ID
AND P.CURRENT_ONE = '1'
AND S.LANGUAGE_ID = '3'
ELSE IF @ADR3 IS NULL AND @ADR2 IS NOT NULL
SELECT ADR1,ADR2, POSTAL_CODE, (STATE_NAME) AS COUNTRY
FROM PAT_NAMES_ADDRESS P, STATE_NAME S
WHERE P.NAME_ID = @nameid
AND P.STATE_ID = S.STATE_ID
AND P.CURRENT_ONE = '1'
AND S.LANGUAGE_ID = '3'
IF @ADR2 IS NULL AND @ADR3 IS NOT NULL
SELECT ADR1,ADR3, POSTAL_CODE, (STATE_NAME) AS COUNTRY
FROM PAT_NAMES_ADDRESS P, STATE_NAME S
WHERE P.NAME_ID = @nameid
AND P.STATE_ID = S.STATE_ID
AND P.CURRENT_ONE = '1'
AND S.LANGUAGE_ID = '3'
ELSE IF @ADR2 IS NOT NULL AND @ADR3 IS NOT NULL
SELECT ADR1, ADR2, ADR3, POSTAL_CODE, (STATE_NAME) AS COUNTRY
FROM PAT_NAMES_ADDRESS P, STATE_NAME S
WHERE P.NAME_ID = @nameid
AND P.STATE_ID = S.STATE_ID
AND P.CURRENT_ONE = '1'
AND S.LANGUAGE_ID = '3'
February 5, 2014 at 5:37 am
If I exclude the if statement, the SELECT works perfectly.
I just need to know if and how I can change the IF statements, so the conditions the the IF will work.
February 5, 2014 at 5:48 am
ah I see.
This should be easier than you think.
DECLARE @NAMEID int, @ADR2 VARCHAR(100), @ADR3 VARCHAR(100)
SET @NAMEID = '14388'
--just add this line
SELECT @ADR2 = ADR2, @ADR3 = ADR3 FROM PAT_NAMES_ADDRESS WHERE NAME_ID = @NAMEID
IF @ADR2 IS NULL AND @ADR3 IS NULL
SELECT ADR1, POSTAL_CODE, (STATE_NAME) AS COUNTRY
FROM PAT_NAMES_ADDRESS P, STATE_NAME S
WHERE P.NAME_ID = @nameid
AND P.STATE_ID = S.STATE_ID
AND P.CURRENT_ONE = '1'
AND S.LANGUAGE_ID = '3'
ELSE IF @ADR3 IS NULL AND @ADR2 IS NOT NULL
SELECT ADR1,ADR2, POSTAL_CODE, (STATE_NAME) AS COUNTRY
FROM PAT_NAMES_ADDRESS P, STATE_NAME S
WHERE P.NAME_ID = @nameid
AND P.STATE_ID = S.STATE_ID
AND P.CURRENT_ONE = '1'
AND S.LANGUAGE_ID = '3'
IF @ADR2 IS NULL AND @ADR3 IS NOT NULL
SELECT ADR1,ADR3, POSTAL_CODE, (STATE_NAME) AS COUNTRY
FROM PAT_NAMES_ADDRESS P, STATE_NAME S
WHERE P.NAME_ID = @nameid
AND P.STATE_ID = S.STATE_ID
AND P.CURRENT_ONE = '1'
AND S.LANGUAGE_ID = '3'
ELSE IF @ADR2 IS NOT NULL AND @ADR3 IS NOT NULL
SELECT ADR1, ADR2, ADR3, POSTAL_CODE, (STATE_NAME) AS COUNTRY
FROM PAT_NAMES_ADDRESS P, STATE_NAME S
WHERE P.NAME_ID = @nameid
AND P.STATE_ID = S.STATE_ID
AND P.CURRENT_ONE = '1'
AND S.LANGUAGE_ID = '3'
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply