Complex Select Case - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Complex Select Case

Author

Topic

Mallen
Starting Member

27 Posts

Posted - 2011-01-05 : 16:02:43

I am unsure of the syntax for what I am trying to do. I need to select several address fields based on conditions.
This is what I have. The logic is solid but it seems the syntax is incorrect. It is giving me errors with 'Incorrect syntax near the keyword 'Case', 'Else', 'Else'.
The idea is to insert the address into the table if isprimary=1, if there is no primary insert the most recent address that isactive=1, if there is no active then insert the most recent address.

Please Help

-------------------------
-- Select One best address for Record 02
DECLARE @ygcaddress TABLE
(
[AccountID] varchar(10),
[Address1] varchar(25),
[City] varchar(22),
[State] varchar(3),
[Zip] varchar(9)
)
INSERT INTO @ygcaddress
CASE
WHEN Address.IsPrimary=1
THEN
SELECT
LEFT(Address.AddressLine1,25),
Address.City,
[Lookup].LookupValue,
Address.Zip
FROM (((AccountPerson INNER JOIN Account ON AccountPerson.AccountID=Account.AccountID)
INNER JOIN Address ON AccountPerson.PersonID=Address.PersonID)
INNER JOIN @ygcaddress y ON Account.AccountID=y.AccountID)
INNER JOIN [Lookup] ON Address.StateID=[Lookup].LookupID
WHERE Address.IsPrimary=1
ELSE
CASE
WHEN Address.IsActive=1
THEN
SELECT TOP 1
LEFT(Address.AddressLine1,25),
Address.City,
[Lookup].LookupValue,
Address.Zip
FROM (((AccountPerson INNER JOIN Account ON AccountPerson.AccountID=Account.AccountID)
INNER JOIN Address ON AccountPerson.PersonID=Address.PersonID)
INNER JOIN @ygcaddress y ON Account.AccountID=y.AccountID)
INNER JOIN [Lookup] ON Address.StateID=[Lookup].LookupID
WHERE Address.IsActive=1
ELSE
SELECT TOP 1
LEFT(Address.AddressLine1,25),
Address.City,
[Lookup].LookupValue,
Address.Zip
FROM (((AccountPerson INNER JOIN Account ON AccountPerson.AccountID=Account.AccountID)
INNER JOIN Address ON AccountPerson.PersonID=Address.PersonID)
INNER JOIN @ygcaddress y ON Account.AccountID=y.AccountID)
INNER JOIN [Lookup] ON Address.StateID=[Lookup].LookupID
END
END

bobmcclellan
Starting Member

46 Posts

Posted - 2011-01-05 : 20:08:59

Something like this should get you close...

;with Primry as (
SELECT ap.AccountID,
Addr = LEFT(ad.AddressLine1,25),
ad.City,
L.LookupValue,
ad.Zip
FROM AccountPerson ap
--Inner Join Account a ON ap.AccountID=a.AccountID -- not needed here
Inner Join [Address] ad ON ap.PersonID=ad.PersonID
--Inner Join @ygcaddress y ON a.AccountID=y.AccountID -- not logical. there is no values in this table if was just created.
Inner Join [Lookup] L ON ad.StateID=L.LookupID
WHERE ad.IsPrimary=1
)

, LastActv as (
Select MaxId = max(AccountID)
from [Address]
group by AccountID
where IsActive =1
)

, Actv as (
SELECT ap.AccountID,
addr = LEFT(ad.AddressLine1,25),
ad.City,
L.LookupValue,
ad.Zip
FROM AccountPerson ap
Inner Join [Address] ad ON ap.PersonID=ad.PersonID
Inner Join [Lookup] L ON ad.StateID=L.LookupID
Inner join LastActv la on ad.AccountID = la.MaxID
)

, Bal as (
SELECT ap.AccountID,
LEFT(ad.AddressLine1,25),
ad.City,
L.LookupValue,
ad.Zip
FROM AccountPerson ap
Inner Join [Address] ad ON ap.PersonID=ad.PersonID
Inner Join [Lookup] L ON ad.StateID=L.LookupID
WHERE IsNull(ad.IsPrimary,0) <>1
and IsNull(IsActive,0) <> 1
)

Select AccountID,
Addr = case when p is not null then p.Addr
when ac is not null then ac.addr
when b is not null then b.addr
end,
City = case when p is not null then p.City
when ac is not null then ac.City
when b is not null then b.City
end,
ST = case when p is not null then p.LookupValue
when ac is not null then ac.LookupValue
when b is not null then b.LookupValue
end,
Zip = case when p is not null then p.Zip
when ac is not null then ac.Zip
when b is not null then b.Zip
end,

From Account a
left join Primry p on a.accountID = p.accountID
left join Actv ac on a.accountID = ac.accountID
left join Bal b on a.accountID = b.accountID

Mallen
Starting Member

27 Posts

Posted - 2011-01-06 : 17:07:00

Thank you very much. Just to be sure, do I start that after the declare table? I am confused by the ; at the beginning. So declare the table and the insert into @ygcaddress <your code here>

Thank you again for the help.

bobmcclellan
Starting Member

46 Posts

Posted - 2011-01-07 : 08:18:55

Hello Mallen,
The ; at the beginning is only there because using a CTE requires the <With> to be the first part of the code
in the scope that it is working in. It is not required if you start out with it.

hth,
..bob

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-01-07 : 10:14:28

Or maybe something like:


-- Select One best address for Record 02
CREATE TABLE #ygcaddress
(
	AccountID varchar(10)
	,Address1 varchar(25)
	,City varchar(22)
	,[State] varchar(3)
	,Zip varchar(9)
);

WITH AddrPriority
AS
(
	SELECT P.AccountID
		,LEFT(A.AddressLine1,25) AS AddressLine1
		,A.City
		,L.LookupValue AS [State]
		,A.Zip
		-- Need soome way of getting most recent address
		-- Either a date or an identity column
		-- Assuming an identity called AddressID here - PROVIDE DDL IN FUTURE!
		,ROW_NUMBER() OVER
		(
			PARTITION BY AccountID
			ORDER BY
					CASE
						WHEN A.IsPrimary = 1 THEN 1
						WHEN A.IsActive = 1 THEN 2
						ELSE 3
					END
					,A.AddressID DESC
		) AS RowNum
	FROM AccountPerson P
		JOIN [Address] A
			ON P.PersonID=A.PersonID
		JOIN [Lookup] L
			ON A.StateID=L.LookupID
)
INSERT INTO #ygcaddress
SELECT AccountID, AddressLine1, City, [State], Zip
FROM AddrPriority
WHERE RowNum = 1;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources