CODEWITHSUNDEEP
sqlsql-server
Ruwan Ratnayake
Ruwan Ratnayake

Reputation: 1

SQL Subquery CASE

SELECT 
  ac.ac_code,    
  (SELECT CASE
              WHEN SUM(L.l_valuedr) - SUM(L.l_valuecr) >= 0 THEN SUM(L.l_valuedr) - SUM(L.l_valuecr)
              ELSE 0
          END AS dr,
          CASE
              WHEN SUM(L.l_valuedr) - SUM(L.l_valuecr) < 0 THEN SUM(L.l_valuecr) - SUM(L.l_valuedr)
              ELSE 0
          END AS cr
   FROM Ledger AS L
   WHERE (l_date < '09/08/2014')
     AND (l_accode = ac.ac_code)
  ) AS bf,
  CASE
    WHEN SUM(Ledger_2.l_valuedr) - SUM(Ledger_2.l_valuecr) >= 0 THEN SUM(Ledger_2.l_valuedr) - SUM(Ledger_2.l_valuecr)
    ELSE 0
  END AS dr,
  CASE
    WHEN SUM(Ledger_2.l_valuedr) - SUM(Ledger_2.l_valuecr) < 0 THEN SUM(Ledger_2.l_valuecr) - SUM(Ledger_2.l_valuedr)
    ELSE 0
  END AS cr
FROM Ac_Accounts AS ac
INNER JOIN Ac_Costelements ON ac.ac_costelement = Ac_Costelements.ce_code
INNER JOIN Ledger AS Ledger_2 ON ac.ac_code = Ledger_2.l_accode
WHERE (Ledger_2.l_date >= '09/08/2014')
  AND (Ledger_2.l_date <= '09/15/2014')
  AND (Ledger_2.l_accode = ac.ac_code)
GROUP BY ac.ac_code

Showing this Error:

Msg 116, Level 16, State 1, Line 5
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Upvotes: 0

Views: 85

Answers (2)

GarethD
GarethD

Reputation: 69769

I think you can simplify your query as follows:

WITH CTE AS
(   SELECT  ac.ac_code,
            Value1 = SUM(CASE WHEN l.l_date < '20140908' THEN l.l_valuedr - l.l_valuecr ELSE 0 END),
            Value2 = SUM(CASE WHEN l.l_date >= '20140908' THEN l.l_valuedr - l.l_valuecr ELSE 0 END)
    FROM    Ac_Accounts AS ac
            INNER JOIN Ac_Costelements AS c
                ON ac.ac_costelement = c.ce_code
            INNER JOIN Ledger AS l 
                ON ac.ac_code = l.l_accode
    WHERE   l.l_date <= '20140915'
    GROUP BY ac.ac_code
)
SELECT  ac_code,
        dr = CASE WHEN Value1 >= 0 THEN Value1 END,
        cr = CASE WHEN Value1 < 0 THEN Value1 * -1 END,
        dr = CASE WHEN Value1 >= 0 THEN Value2 END,
        cr = CASE WHEN Value1 < 0 THEN Value2 * -1 END
FROM    CTE;

Rather than using a correlated subquery to get the amounts before a given date, and get the amount after that date in the main query, you can get both amounts within the same query by using CASE inside the SUM.

Then by moving some of the logic to a subquery you can simplify the query by reducing the number of sums done.

Upvotes: 1

ItalianStallion
ItalianStallion

Reputation: 296

Instead of doing two case statements, why not move them together? Go from this:

(SELECT CASE
              WHEN SUM(L.l_valuedr) - SUM(L.l_valuecr) >= 0 THEN SUM(L.l_valuedr) - SUM(L.l_valuecr)
              ELSE 0
          END AS dr,
          CASE
              WHEN SUM(L.l_valuedr) - SUM(L.l_valuecr) < 0 THEN SUM(L.l_valuecr) - SUM(L.l_valuedr)
              ELSE 0
          END AS cr
   FROM Ledger AS L
   WHERE (l_date < '09/08/2014')
     AND (l_accode = ac.ac_code)) AS bf

To something like this:

(SELECT CASE
         WHEN SUM(L.l_valuedr) - SUM(L.l_valuecr) >= 0 
         THEN SUM(L.l_valuedr) - SUM(L.l_valuecr)
         ELSE SUM(L.l_valuecr) - SUM(L.l_valuedr)
        END AS newcolumn
   FROM Ledger AS L
   WHERE (l_date < '09/08/2014')
     AND (l_accode = ac.ac_code)) AS bf

Upvotes: 0

Related Questions