Referencing a database in if statements-CodePudding

I am currently running the below code through SQL Server Management Studio queries before I implement it in my Django-Python application.

I am having trouble figuring out how to reference the database in question before the if statement begins:

Query:

If Age1 IS NULL 
    set @Current = DCBalance
    Select Account, Name, E-mail , Age1 , Age2 , Age3 , Age4 , Age5 , @Current ,DCBalance from [Graceland Estates BC].[dbo].[_avCustomerAging]
Else  Age1 IS NOT NULL
    If UADebit <> '0' and DCBalance > 0 
        Set @Current = DCBal - Sum(Age1, Age2, Age3 , Age4, Age5)
        Select Account , Name , E-mail , Age1 , Age2 , Age3 , Age4 , Age5 , @Current , DCBalance from [Graceland Estates BC].[dbo].[_avCustomerAging]
    Else  UADebit = '0'  and DCBalance < 0 
        Set @Age1X = fForeignBalance   Age1 - FCAge1
        Set @Age2X = fForeignBalance   Age2 - FCAge2
        Set @Age3X = fForeignBalance   Age3 - FCAge3
        Set @Age4X = fForeignBalance   Age4 - FCAge4
        Set @Age5X = fForeignBalance   Age5 - FCAge5
        Set @Current = 0
        Select Account , Name , E-mail , @Age1X , @Age2X , @Age3X , @Age4X , @Age5X , @Current , DCBalance
    Else  UADebit = '0'  and DCBalance > 0 
        Set @Ages = '0'
        Select Account , Name , E-mail , @Ages , @Ages , @Ages , @Ages , @Ages , DCBalance as Current , DCBalance

In the above code, it is meant to determine if the data matches what is represented in the if statement and do a calculation based on the way the data is displayed.

Here is the code when I try the same thing with a CASE statement included :

Select Account, Name , EMail, Age1 AS Cur, Age2 , Age3 , Age4, Age5 ,DCBalance,
Case 
    When AccountLink = NULL Then Age1 = DCBalance and Age2 = 0 and Age3 = 0 and Age4 = 0 and Age5= 0
    When UADebit <> 0 and DCBalance > 0 Then Age1 = DCBalance - (Age2   Age3   Age4   Age5)
    When UADebit = 0 and DCBalance < 0 Then Age1 = 0 and Age2 = fForeignBalance   Age2 - FCAge2 and Age3 = fForeignBalance   Age3 - FCAge3 and Age4 = fForeignBalance   Age4 - FCAge4 and Age5 = fForeignBalance   Age5 - FCAge5
    When UADebit = 0 and DCBalance > 0 Then Age1 = DCBalance and Age2 = 0 and Age3 = 0 and Age4 = 0 and Age5= 0
End 
From [Kyle].[dbo].[_avCustomerAging]

The error Incorrect syntax near '='. is returned when this is executed

CodePudding user response：

CASE is an expression NOT a statement, which is made clear when referencing the official documentation i.e. a single case expression, regardless of number of branches, can return a single value. It does not allow you to run conditional parts of your query.

In addition you cannot compare NULL with = you use IS NULL.

I think the following contains the same logic you are aiming for, but it should trivial to tweak it now that you can see where you were going wrong.

SELECT Account, [Name], EMail, Age1, Age2, Age3, Age4, Age5, DCBalance,

    CASE 
        WHEN AccountLink IS NULL THEN DCBalance
        WHEN UADebit <> 0 AND DCBalance > 0 THEN DCBalance - (Age2   Age3   Age4   Age5)
        WHEN UADebit = 0 AND DCBalance < 0 THEN 0
        WHEN UADebit = 0 AND DCBalance > 0 THEN DCBalance
    END AS Age1_New
    ,
    Case 
        WHEN AccountLink IS NULL THEN 0
        WHEN UADebit = 0 AND DCBalance < 0 THEN fForeignBalance   Age2 - FCAge2
        WHEN UADebit = 0 AND DCBalance > 0 THEN 0
    END Age2_New
    ,
    CASE 
        WHEN AccountLink IS NULL Then 0
        WHEN UADebit = 0 AND DCBalance < 0 THEN fForeignBalance   Age3 - FCAge3
        WHEN UADebit = 0 AND DCBalance > 0 THEN 0
    END Age3_New
    ,
    CASE 
        WHEN AccountLink IS NULL THEN 0
        WHEN UADebit = 0 AND DCBalance < 0 THEN fForeignBalance   Age4 - FCAge4
        WHEN UADebit = 0 AND DCBalance > 0 THEN 0
    END Age4_New
    ,
    CASE 
        WHEN AccountLink IS NULL THEN 0
        WHEN UADebit = 0 AND DCBalance < 0 THEN fForeignBalance   Age5 - FCAge5
        WHEN UADebit = 0 AND DCBalance > 0 THEN 0
    END Age5_New

FROM _avCustomerAging;