SQL - SELECT STATEMENT, if one field is true, get from collection of columns
Hi,
Here is what I want to accomplish:
I want to get shipping address , if exists, otherwise use the fields from regular address
Table:
ID
Addr
CITY
STATE
SHIPAddr
ShipCity
ShipSTATE
I need something like this:
SELECT ID,
(IF SHIPPADDR ISNOT NULL
ADDR = SHIPADDR,
CITY = ShipCity,
STATE = ShipSTATE
ELSE
ADDR = ADDR,
CITY = City,
STATE = STATE
)
FROM Table_1
This is a trunicated version, the actual query is a few pages so it would be hepful if i can write it as above
Here is what I want to accomplish:
I want to get shipping address , if exists, otherwise use the fields from regular address
Table:
ID
Addr
CITY
STATE
SHIPAddr
ShipCity
ShipSTATE
I need something like this:
SELECT ID,
(IF SHIPPADDR ISNOT NULL
ADDR = SHIPADDR,
CITY = ShipCity,
STATE = ShipSTATE
ELSE
ADDR = ADDR,
CITY = City,
STATE = STATE
)
FROM Table_1
This is a trunicated version, the actual query is a few pages so it would be hepful if i can write it as above
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks, thatws what i wrote, i just wanted to know if there is a less wordy way like i did above in pseudocode
Vitor's solution works as well if you're trying to get a lower read on the wordy-o-meter.
ASKER
Negative,
i have addr1 and addr2
if addr2ship is blank, it will take addr2,
and that is just wrong
same with city state, i need the entire group not just one field
example,
Mail:
123 Goo ST
APT 7
Happyville, GA, 99999
SHIP addr:
321 Ship Drive
NULL
PleasentVille, MD
His result would be
321 Ship Drive
APT 7
PleasentVille, MD, 99999
i have addr1 and addr2
if addr2ship is blank, it will take addr2,
and that is just wrong
same with city state, i need the entire group not just one field
example,
Mail:
123 Goo ST
APT 7
Happyville, GA, 99999
SHIP addr:
321 Ship Drive
NULL
PleasentVille, MD
His result would be
321 Ship Drive
APT 7
PleasentVille, MD, 99999
>if addr2ship is blank, it will take addr2 and that is just wrong
Then your initial requirements were wrong, as they stated that (IF SHIPPADDR ISNOT NULL was the only criteria for all columns.
If you wish to change the criteria to a field by field comparison (i.e. ShipAddr to ADDR, ShipCity to City, etc.) then you can change either of our codes to instead of always evaluate SHIPADDR, evaluate ShipADDR, ShipCity, and ShipState.
Then your initial requirements were wrong, as they stated that (IF SHIPPADDR ISNOT NULL was the only criteria for all columns.
If you wish to change the criteria to a field by field comparison (i.e. ShipAddr to ADDR, ShipCity to City, etc.) then you can change either of our codes to instead of always evaluate SHIPADDR, evaluate ShipADDR, ShipCity, and ShipState.
>i have addr1 and addr2
either add Addr in a similar way as the other columns, or if you wish to add it to the addr1 line...
Using CASE
either add Addr in a similar way as the other columns, or if you wish to add it to the addr1 line...
Using CASE
CASE WHEN ShipAddr IS NOT NULL THEN ShipAddr ELSE ADDR END + CASE WHEN ShipAddr2 IS NOT NULL THEN ', ' + ShipAddr2 ELSE '' END as ADDR, ASKER
Jim, Looks like we went off a tangent here.
You had the correct idea, which is what im using
I was just wondering if there is a way i can use a single IF statement instead of a CASE for each field
(As far as Victor's answer, it would evaluate each field individually, hence why it is incorrect)
You had the correct idea, which is what im using
CASE WHEN M.MemberMailingAddress1 IS NOT NULL THEN M.MemberMailingAddress1 + ' '+ISNULL(M.MemberMailingAddress2, ' ')
ELSE ISNULL(M.MemberAddress1,'')+ ' '+ISNULL(M.MemberAddress2, ' ') END AS MemberAddress,
CASE WHEN M.MemberMailingAddress1 IS NOT NULL THEN M.MemberMailingCity ELSE M.MemberCity END AS City,
CASE WHEN M.MemberMailingAddress1 IS NOT NULL THEN M.MemberMailingState ELSE M.MemberState END AS State,
CASE WHEN M.MemberMailingAddress1 IS NOT NULL THEN M.MemberMailingZip ELSE M.MemberZip END AS Zip, ...etcI was just wondering if there is a way i can use a single IF statement instead of a CASE for each field
(As far as Victor's answer, it would evaluate each field individually, hence why it is incorrect)
>I was just wondering if there is a way i can use a single IF statement instead of a CASE for each field
No, you can't use a single decision structure like IF to affect multiple columns.
No, you can't use a single decision structure like IF to affect multiple columns.
ASKER
In a way, it can be done in several ways,
one is in a innerjoin to the table,
IF membershipaddr is null
(select into #temp memberaddr....etc )
else
(select into #temp membershipaddr....etc )
end
select (mainquery) innerjoin #temp
second is to IF the entire query
third is to place all in temp table, and update if ship is not null
IF membershipaddr is not null
(update #temp memberaddr....etc )
end
Anyway, thanks for your thoughts.
one is in a innerjoin to the table,
IF membershipaddr is null
(select into #temp memberaddr....etc )
else
(select into #temp membershipaddr....etc )
end
select (mainquery) innerjoin #temp
second is to IF the entire query
third is to place all in temp table, and update if ship is not null
IF membershipaddr is not null
(update #temp memberaddr....etc )
end
Anyway, thanks for your thoughts.
Another solution is to use a UNION clause:
SELECT ID, SHIPADDR, ShipCity,ShipSTATE
FROM Table_1
WHERE SHIPPADDR IS NOT NULL
UNION
SELECT ID, ADDR, City, STATE
FROM Table_1
WHERE SHIPPADDR IS NULLASKER
yes good point. regardless, the actual query is huge, including quantity of tables and records, id rather just the if else block like i pseudocoded in the question, least resource consuming approach is the one credited points
Open in new window