Q
Problem solve Get help with specific problems with your technologies, process and projects.

A subquery with a different WHERE clause

I have a problem on how to display a column subquery that has a different where clause. Here is my code:

SELECT T0.Account AS 'Account', 
T1.AcctName AS 'AccountName', 
SUM(ISNULL(T0.Debit,0)) AS 'Debet', 
SUM(ISNULL(T0.Credit,0)) AS 'Kredit', 
SUM(ISNULL(T0.Debit,0) 
- ISNULL(T0.Credit,0)) AS 'Balanse' 
FROM  JDT1 T0 INNER JOIN OACT T1 
ON T0.Account=T1.AcctCode 
WHERE T0.RefDate BETWEEN 
CONVERT(DATETIME, '01.01.04', 104) AND 
CONVERT(DATETIME, '31.12.04', 104) 
GROUP BY T0.Account, T1.AcctName 
ORDER BY T0.Account

This is my subquery I would include as a first column field. Not sure how to setup with (UNION, IN or ANY etc)

SELECT T0.Account AS 'Konto', 
T1.AcctName AS 'Kontonavn', 
SUM(ISNULL(T0.Debit,0) 
- ISNULL(T0.Credit,0)) AS 'Opening Balanse' 
FROM  JDT1 T0 INNER JOIN OACT T1 
ON T0.Account=T1.AcctCode 
WHERE T0.RefDate < '01.01.04' 
GROUP BY T0.Account, T1.AcctName 
ORDER BY T0.Account

This was the subquery which I would like to include as the first sum column. Like this: Account, AccountName, OpeningBalance, SumDebit, SumCredit, EndBalance. OpeningBalance is based on sum of all previous date values in the table. EndBalance is then based on (OpenBalance+(SumDebet-SumKredit)).

Aggregate functions like SUM ignore nulls, so it's not necessary to convert nulls to zeros using ISNULL, or the preferred equivalent COALESCE (preferred because it's the standard SQL function for that purpose).

Based on your use of CONVERT for the datetime values, your database system is SQL Server. In SQL Server, all datetime values carry a time component, so the use of BETWEEN with December 31 on the upper end of the range might actually omit all values for that date. The better approach is to select values that are less than January 1 of the following year.

The subquery that you want to use produces a derived table because it is located in the FROM clause of the query:

select T0.Account 
     , T1.AcctName as AccountName
     , TD.OpeningBalance
     , sum(T0.Debit) as Debet
     , sum(T0.Credit) as Kredit
     , TD.OpeningBalance
      +sum(T0.Debit) 
      -sum(T0.Credit)
                   as EndBalance 
  from JDT1 as T0 
inner 
  join OACT as T1 
    on T0.Account = T1.AcctCode 
left outer
  join ( 
       select Account
            , sum(Debit-Credit) 
                 as OpeningBalance
         from JDT1  
        where RefDate < '2004-01-01' 
       group
           by Account         
       ) as TD
    on T0.Account = TD.Account           
 where T0.RefDate >= '2004-01-01'
   and T0.RefDate  < '2005-01-01'
group 
    by T0.Account
     , T1.AcctName 
     , TD.OpeningBalance
order 
    by T0.Account

Notice how OpeningBalance is computed in the subquery, and is used both in the SELECT list and the GROUP BY clause as though it were a single column, which, in a derived table, it is.

This was last published in April 2005

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close