AR AGEING - Query works perfectly; stops working when changed into Subquery

[leon_laikan]

Dear All,

I got this query from SAP B1 SQL TIPS AND TRICKS. It's called:

SAP B1 SQL B-FN Debtors Aging Report by date

http://wiki.sdn.sap.com/wiki/display/B1/SAPB1SQLB-FNDebtorsAgingReportbydate

This query works perfectly by itself. But when I turn it into a subquery, it stops working. Why?

It will work, however if I remove all those lines beginning with ,ISNULL, but I need to retain these.

How can I make the query work together with the subquery?

THIS QUERY WORKS

SELECT T1.CardCode, T1.CardName, T1.CreditLine, T0.RefDate, T0.Ref1 'Document Number',
     CASE  WHEN T0.TransType=13 THEN 'Invoice'
          WHEN T0.TransType=14 THEN 'Credit Note'
          WHEN T0.TransType=30 THEN 'Journal'
          WHEN T0.TransType=24 THEN 'Receipt'
          END AS 'Document Type',
     T0.DueDate, (T0.Debit- T0.Credit) 'Balance'
     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,'[%1]')<=-1),0) 'Future'
     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,'[%1]')>=0 and DateDiff(day, T0.DueDate,'[%1]')<=30),0) 'Current'
     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,'[%1]')>30 and DateDiff(day, T0.DueDate,'[%1]')<=60),0) '31-60 Days'
     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,'[%1]')>60 and DateDiff(day, T0.DueDate,'[%1]')<=90),0) '61-90 Days'
     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,'[%1]')>90 and DateDiff(day, T0.DueDate,'[%1]')<=120),0) '91-120 Days'
     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,'[%1]')>=121),0) '121+ Days'
FROM JDT1 T0 INNER JOIN OCRD T1 ON T0.ShortName = T1.CardCode
WHERE  T1.CardType = 'C'

THIS QUERY + SUBQUERY DOES NOT WORK!

I enclose the query between () , then I put an alias name SUB at the end.

Then at the beginning, I write SELECT * FROM

Everything stops working.

SELECT * FROM

(SELECT T1.CardCode, T1.CardName, T1.CreditLine, T0.RefDate, T0.Ref1 'Document Number',
     CASE  WHEN T0.TransType=13 THEN 'Invoice'
          WHEN T0.TransType=14 THEN 'Credit Note'
          WHEN T0.TransType=30 THEN 'Journal'
          WHEN T0.TransType=24 THEN 'Receipt'
          END AS 'Document Type',
     T0.DueDate, (T0.Debit- T0.Credit) 'Balance'
     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,'[%1]')<=-1),0) 'Future'
     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,'[%1]')>=0 and DateDiff(day, T0.DueDate,'[%1]')<=30),0) 'Current'
     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,'[%1]')>30 and DateDiff(day, T0.DueDate,'[%1]')<=60),0) '31-60 Days'
     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,'[%1]')>60 and DateDiff(day, T0.DueDate,'[%1]')<=90),0) '61-90 Days'
     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,'[%1]')>90 and DateDiff(day, T0.DueDate,'[%1]')<=120),0) '91-120 Days'
     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,'[%1]')>=121),0) '121+ Days'
FROM JDT1 T0 INNER JOIN OCRD T1 ON T0.ShortName = T1.CardCode
WHERE T1.CardType = 'C' ) SUB

thanks

LEON LAI

[former_member206488]

Hi,

What is the requirement to create subquery for above report?

Thanks,

Neetu

[leon_laikan]

Dear Neetu,

Gordon has replied at the same time, and asking me the same question.

My reply is on the thread.

If you need more clarification, please ask.

Thanks

Leon Lai

[former_member204969]

The problem is the parameter inside the subquery.

Try this:

Declare @d datetime
set @d
/*Select 1 from jdt1 t where t.duedate*/=[%1]

select * from
(
SELECT T1.CardCode, T1.CardName, T1.CreditLine, T0.RefDate, T0.Ref1 'Document Number',
     CASE  WHEN T0.TransType=13 THEN 'Invoice'
          WHEN T0.TransType=14 THEN 'Credit Note'
          WHEN T0.TransType=30 THEN 'Journal'
          WHEN T0.TransType=24 THEN 'Receipt'
          END AS 'Document Type',
     T0.DueDate, (T0.Debit- T0.Credit) 'Balance'
     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,@d)<=-1),0) 'Future'
     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,@d)>=0 and DateDiff(day, T0.DueDate,'[%1]')<=30),0) 'Current'
     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,@d)>30 and DateDiff(day, T0.DueDate,'[%1]')<=60),0) '31-60 Days'
     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,@d)>60 and DateDiff(day, T0.DueDate,'[%1]')<=90),0) '61-90 Days'
     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,@d)>90 and DateDiff(day, T0.DueDate,'[%1]')<=120),0) '91-120 Days'
     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,@d)>=121),0) '121+ Days'
FROM JDT1 T0 INNER JOIN OCRD T1 ON T0.ShortName = T1.CardCode
WHERE  T1.CardType = 'C'
) sub

[former_member206488]

Hi,

try this:

Declare @d datetime
set @d
/*Select 1 from jdt1 t where t.duedate*/=[%1]
 
select * from
(
SELECT T1.CardCode, T1.CardName, T1.CreditLine, T0.RefDate, T0.Ref1 'Document Number',
     CASE  WHEN T0.TransType=13 THEN 'Invoice'
          WHEN T0.TransType=14 THEN 'Credit Note'
          WHEN T0.TransType=30 THEN 'Journal'
          WHEN T0.TransType=24 THEN 'Receipt'
          END AS 'Document Type',
     T0.DueDate, (T0.Debit- T0.Credit) 'Balance'
     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,@d)<=-1),0) 'Future'
     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,@d)>=0 and DateDiff(day, T0.DueDate,@d)<=30),0) 'Current'
     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,@d)>30 and DateDiff(day, T0.DueDate,@d)<=60),0) '31-60 Days'
     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,@d)>60 and DateDiff(day, T0.DueDate,@d)<=90),0) '61-90 Days'
     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,@d)>90 and DateDiff(day, T0.DueDate,@d)<=120),0) '91-120 Days'
     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,@d)>=121),0) '121+ Days'
FROM JDT1 T0 INNER JOIN OCRD T1 ON T0.ShortName = T1.CardCode
WHERE  T1.CardType = 'C'
) sub
 

Thnks,

Neetu

[former_member204969]

Hi Neetu,

Isn't this the copy of my posting?

[former_member206488]

Hi Istvan,

you chnaged [%1] to @d in first part of

(day, T0.DueDate,@d)>90 and DateDiff(day, T0.DueDate,'[%1]')<=120),0

,

I modified it slighly to change [%1] variable with @d in complete as

(day, T0.DueDate,@d)>90 and DateDiff(day, T0.DueDate,@d)<=120),,0

part of query/

Thanks,

Neetu

[former_member204969]

Hi Neetu, you are right.

Iu2019ve forgotten to change the parameters at the second place.

It is funny, that changing the fist ones was enough.

Regards

Istvá

[leon_laikan]

Dear István,

I am overjoyed! Your suggestion, as amended by Neetu completely solved my problem (up to this stage).Wow! This is wonderful!

Now, we have an EXACT replica of the Official SAP B1's Debtors Aged Analysis (not showing reconciled items).

If other people ask similar questions, you may wish to refer them to this thread.

This SQL is now exactly the same as the Official SAP B1 Report.

(1) It gives exactly the same Total Amount

(2) Same number of Rows

(3) Same Amount for each row

(4) Excludes all fully reconciled items (Invoices, Credit Notes, Payments, or Journals)

(5) No rows with zero amounts

It remains to Order and Group the SQL same way as SAP's.

Those who wish to change the Aging bands may do so to their heart's content.

A Suggestion to all the experts watching this thread:

Our solution works perfectly, but is rather untidy. In particular, I cannot understand why "I should shout twice before SQL hears me".

You, experts can attempt a more elegant solution, and post it in FAQ - SQL Tips & Tricks.

Thanks a lot, and a hearty thank you to all the other experts who replied to my question.

Leon Lai

Here is the SQL:

DECLARE @d datetime
SET @d
/*Select 1 from jdt1 t where t.duedate*/=[%1]


SELECT * FROM

(
SELECT
T1.CardCode,
T1.CardName,
T0.RefDate,

CASE
	WHEN T0.TransType = 13 THEN 'IN'
	WHEN T0.TransType = 14 THEN 'CN'
	WHEN T0.TransType = 30 THEN 'JE'
	WHEN T0.TransType = 24 THEN 'RC'
	WHEN T0.TransType = 46 THEN 'PS'
	ELSE 'Error ! ! !'
END AS 'Doc Type',

T0.Ref1 'Doc. Number',


T0.Account,
(T0.BalDueDeb - T0.BalDueCred) AS 'Balance',


ISNULL((SELECT T0.BalDueDeb -T0.BalDueCred WHERE DateDiff(day, T0.RefDate, @d)<= -1),0) AS 'Future',
ISNULL((SELECT T0.BalDueDeb -T0.BalDueCred WHERE DateDiff(day, T0.RefDate,@d)>= 0 and DateDiff(day, T0.RefDate,@d)<=30),0) AS 'Current',
ISNULL((SELECT T0.BalDueDeb -T0.BalDueCred WHERE DateDiff(day, T0.RefDate,@d)> 30 and DateDiff(day, T0.RefDate,@d)<=60),0) AS '31-60 Days',
ISNULL((SELECT T0.BalDueDeb -T0.BalDueCred WHERE DateDiff(day, T0.RefDate,@d)> 60 and DateDiff(day, T0.RefDate,@d)<=90),0) AS '61-90 Days',
ISNULL((SELECT T0.BalDueDeb -T0.BalDueCred WHERE DateDiff(day, T0.RefDate,@d)> 90 and DateDiff(day, T0.RefDate,@d)<=120),0) AS '91-120 Days',
ISNULL((SELECT T0.BalDueDeb -T0.BalDueCred WHERE DateDiff(day, T0.RefDate,@d)>= 121),0) AS '121+ Days'

FROM JDT1 T0 INNER JOIN OCRD T1 ON T0.ShortName = T1.CardCode

WHERE 
T1.CardType = 'C' and Balance != 0
) Subqry

WHERE
Balance !=0

[Former Member]

Dear Leon,

Why do you need it in the subquery? If you require part of the columns, just change this query, no need for sub.

Thanks,

Gordon

[leon_laikan]

Dear Gordon,

It's still me ! Sorry for being a nuisance. I am still working on my project of writing SQL which mimics the SAP B1 Debtors Aged Analysis.

I have 3 problems, and I hope you understand:

1 - I am a newbie, baby newbie, but learning tremendously from this forum.

2 - I am allowed to ask only 1 question at a time.

3 - You, experts are helping me a lot, but I am developing my SQL one step at a time, and new problems keep cropping in.

I recapitulate what I want.

1. I need to wriite an SQL for Debtors Aging because many other fields are needed by management.

2. I've searched the whole forum, and it appears that none of the SQLs availaible comes close to the official Ageing. I have spent much time, but maybe I missed the correct solution? Maybe this is why so many people keep asking again and again.

3. Yes, they all give the same TOTAL amount, but they also list those invoices that have been reconciled. What I need are only invoices ... etc that are still pending, and no available SQL seems to do the job.

4.I accidentally hit upon a near correct solution. My SQL gives exacly what I want, namely ONLY pending invoices, credit notes and journals, but unfortunately also lists a huge number of zero rows. Although I put a WHERE Balance !=0 clause, the SQL does not seem to eliminate these useless zero rows.

5. It dawned on me to try treating this SQL as a SUBQUERY inside a Main Query which also has a WHERE Balance !=0 clause to eliminate the 0 rows.

6. And Lo! Magically all the zero rows disappeared. I got EXACTLY what I wanted: Same No. of Rows as SAP'S Aging, Same Row Total, Same Grand Total. SQL was behaving like a deaf man. You had to shout twice before he understands.

7. Now, I can continue my SQL. I need only to add those Ageing columns, i.e Current, 30 days, 60 days, etc.

8. I thought I just needed to copy the commands available here:

http://wiki.sdn.sap.com/wiki/display/B1/SAPB1SQLB-FNDebtorsAgingReportbydate

and my SQL would be done.

9. But I'm in for a nasty surprise. It stops working. That's why I am asking the question on this forum.

My Previous thread is here:

Thanks

Leon

[Former Member]

You have done a great job to reach this far. The query you are trying is one of the most difficult queries to match system report.

It may disappoint you that the subquery has more strict rule in using parameter and functions. Isnull is one of the function. It may not work in the subquery. You can try without using this function.

[leon_laikan]

Dear Gordon,

I know it's a great challenge, but your wonderful book Mastering SQL Queries for SAP Business One has helped a newbee reach that far. The successful outcome would be a great plus for my enterprise, and I'm working hard to achieve my aim.

Concerning the NULLs, I don't really need them as they fill my screen with zeros, and I'll rather have a clean screen.

But even with the NULLs removed, the query with subquery stops working.

There's an error message: Wrong syntax near SELECT.

Thanks

Leon Lai

[Former Member]

Work with subquery is a delicate job. You have to be patient and go through test and error period. The other experts show very good hits. You can follow the examples and try again.