需要帮助处理复杂的case语句SQL 2005

Question

我不确定我要做什么的语法。我需要根据条件选择几个地址字段。

逻辑是坚实的，但语法似乎不正确。它给我的错误与‘不正确的语法附近的关键字’大小写‘，’其他‘，’其他‘。这样做的目的是将地址插入到表(如果是isprimary=1 )中，如果没有主地址，则插入最近的isactive=1地址，如果没有活动地址，则插入最近的地址。

请帮帮忙

-为记录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

Answer 1

将括号添加到案例结果中，这应该有效(至少在语法上是这样)。

--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 
SELECT 
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

Answer 2

试一试，从我所看到的情况来看，在您最初的问题中，它看起来是一个语法错误：

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

        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

Answer 3

如果我已经正确地理解了这个问题，我认为您根本不需要一个CASE语句: CASE语句返回一个值表达式，而不是一个元组，因为您正在尝试这样做。(至少在我最熟悉的SQL数据库Server中是如此)

编辑删除我的第一个建议的解决方案使用UNION，我意识到这是不正确的。

再次编辑：

试着做这样的事情：

INSERT INTO @ygcaddress 
SELECT
     Account.AccountID
    ,LEFT(Address.AddressLine1,25)
    ,Address.City
    ,[Lookup].LookupValue
    ,Address.Zip 
FROM AccountPerson 
INNER JOIN Account ON AccountPerson.AccountID=Account.AccountID
INNER JOIN 
(
    SELECT TOP 1 
    A1.*
    CASE 
        WHEN A1.IsPrimary=1 THEN 10
        WHEN A1.IsPrimary=0 AND A1.IsActive=1 THEN 5
        ELSE 1
    END [Rank]
    FROM Address A1
    ORDER BY [Rank] DESC
) Address ON AccountPerson.PersonID=Address.PersonID 
INNER JOIN [Lookup] ON Address.StateID=[Lookup].LookupID