CODEWITHSUNDEEP
sqlsql-servernestedsubquerycase
Jay Marz
Jay Marz

Reputation: 1912

What is the proper structure for Nested Case statement with Subquery Sql Statement?

I have here a complicated query here where I have nested case with subquery. I am having trouble with getting the right structure of it. Here is my code:

 select
    AccountNo,
    Case when Datepart (month,TranDate) = 1 Then
    Case ISNULL(RemainingBalance,'NULLVALUE') 
    When 'NULLVALUE' Then
    (select top 1 RemainingBalance From tempAccountBalance
    where DATEPART (YEAR,TranDate)=@FiscalYear-1
    order by TranDate desc)
    else
    MIN(Case when Datepart (month,TranDate) <= 3 Then
    RemainingBalance END) End Q1
FROM tempAccountBalance
WHERE Datepart (year,TranDate) = @FiscalYear and AccountNo=@AccountNo
Group By AccountNo

I get an error which says, Msg 102, Level 15, State 1, Line 11 Incorrect syntax near 'Q1'.

Upvotes: 0

Views: 1210

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

I want to point out that you don't need nested case statements for this query:

select AccountNo,
       (Case when Datepart(month, TranDate) = 1 and
                  RemainingBalance is null
             Then (select top 1 RemainingBalance
                   From tempAccountBalance
                   where DATEPART(YEAR, TranDate) = @FiscalYear-1
                   order by TranDate desc
                  )
             when Datepart(month, TranDate) = 1 
             then MIN(Case when Datepart(month, TranDate) <= 3 Then RemainingBalance END)
        End) as Q1
FROM tempAccountBalance
WHERE Datepart(year,TranDate) = @FiscalYear and AccountNo=@AccountNo
Group By AccountNo;

Upvotes: 0

StuartLC
StuartLC

Reputation: 107267

If you indent your code, you'll spot errors like this more easily (below). Note however that your query still has grouping issues - you'll either need to add TranDate and RemainingBalance to the GROUP BY, or use aggregates on them. I've taken a guess below without any understanding of your query:

select
    AccountNo,
    Case 
       when Datepart(month,TranDate) = 1 
            Then
                Case ISNULL(Min(RemainingBalance), 'NULLVALUE')  -- Added Min
                    When 'NULLVALUE' 
                    Then (select top 1 RemainingBalance From tempAccountBalance
                         where DATEPART (YEAR,TranDate)=@FiscalYear-1
                         order by TranDate desc)
                    else
                        MIN(
                           Case 
                                when Datepart (month,TranDate) <= 3 
                                Then RemainingBalance 
                           END)
                end -- Missing
    End Q1
FROM tempAccountBalance
WHERE Datepart(year,TranDate) = @FiscalYear and AccountNo=@AccountNo
Group By AccountNo, Datepart(month,TranDate); -- Added to Group By

Upvotes: 4

Rida BENHAMMANE
Rida BENHAMMANE

Reputation: 4129

You need an END before the Q1 :

select
AccountNo,
Case when Datepart (month,TranDate) = 1 Then
Case ISNULL(RemainingBalance,'NULLVALUE') 
When 'NULLVALUE' Then
(select top 1 RemainingBalance From tempAccountBalance
where DATEPART (YEAR,TranDate)=@FiscalYear-1
order by TranDate desc)
else
MIN(Case when Datepart (month,TranDate) <= 3 Then
RemainingBalance END) End End Q1
FROM tempAccountBalance
WHERE Datepart (year,TranDate) = @FiscalYear and AccountNo=@AccountNo
Group By AccountNo

Upvotes: 3

Related Questions