Customer Query showing aging with Invoice details error "Ambiguous Column Name"

[Former Member]

I want to add Invoice line details to this query so I added two tables OINV and INV1 which gave me an error "Ambiguous Column name Tax Date" Can anyone assist. SELECT T1.CardCode AS 'Cust Num', T1.CardName AS 'Cust Name', T0.SysDeb AS 'Debit Amt', T0.SysCred * -1 AS 'Credit Amt', CASE WHEN T0.TransType = 13 THEN 'AR Invoice' WHEN T0.TransType = 14 THEN 'AR Cred Memo' WHEN T0.TransType = 24 THEN 'Payment' ELSE 'Other' END AS 'Trans Type', T0.Ref1 AS 'Reference', FcCurrency AS 'Currency', CONVERT(VARCHAR(10), RefDate, 103) 'Posting Date', CONVERT(VARCHAR(10), DueDate, 103) 'Due Date', CONVERT(VARCHAR(10), TaxDate, 103) 'Doc Date' , CASE WHEN (DATEDIFF(DD,RefDate,Current_Timestamp)) +1 0 THEN SysCred * -1 ELSE SysDeb END END AS '0-30 Days', CASE WHEN (DATEDIFF(DD,RefDate,Current_Timestamp)) +1 > 30 AND (DATEDIFF(DD,RefDate,Current_Timestamp)) +1 < 61 THEN CASE WHEN SysCred < > 0 THEN SysCred * -1 ELSE SysDeb END END AS '31 to 60 Days', CASE WHEN (DATEDIFF(DD,RefDate,Current_Timestamp)) +1 > 60 AND (DATEDIFF(DD,RefDate,Current_Timestamp)) +1 < 91 THEN CASE WHEN SysCred < > 0 THEN SysCred * -1 ELSE SysDeb END END AS '61 to 90 days', CASE WHEN (DATEDIFF(DD,RefDate,Current_Timestamp)) +1 > 90 AND (DATEDIFF(DD,RefDate,Current_Timestamp)) +1 < 121 THEN CASE WHEN SysCred < > 0 THEN SysCred * -1 ELSE SysDeb END END AS '90 to 120 Days', CASE WHEN (DATEDIFF(DD,RefDate,Current_Timestamp)) +1 > 120 THEN CASE WHEN SysCred != 0 THEN SysCred * -1 ELSE SysDeb END END AS '120 Plus Days' FROM JDT1 T0 INNER JOIN OCRD T1 ON T0.ShortName = T1.CardCode AND T1.CardType = 'C' INNER JOIN INV1 T2 ON T1.CardCode = T2.BaseCard INNER JOIN OINV T3 ON T2.DocEntry = T3.DocEntry WHERE T0.IntrnMatch = '0' AND T0.BalDueDeb !=  T0.BalDueCred and T1.CardCode  Like '%[%0]%' ORDER BY T1.CardCode, T0.TaxDate FOR BROWSE

[leon_laikan · Accepted solution]

Hi, Danielle

See attached picture.

Hope it helps.

Regards,

Leon

[Former Member · Accepted solution]

Thank you I did get help on this yesterday and understand. The issue I have now is when adding fields in SAP to this query it does crash my system. Not sure if the way I am joining tables is not so great but works or is it my system. I have to add one field at a time and then log off SAP and then log back on and add another. Very frustrating.

[leon_laikan · Accepted solution]

Hi Danielle,

Could you post your amended code (one that works!), and let me know what code you add that makes your query crash?

I never met your kind of problem. It does not matter how many fields you add at once. It's supposed to work. And your code is short, so it's not a memory problem.

I'll test it on my computer.

Regards,

Leon

[Former Member · Accepted solution]

Thank you. I am not sure what causes this but it takes a long time to run and it does crash when I try to continue adding new fields/columns. Currently it crashed and said SAP B1 has run out of memory it will shut down. Basically I am trying to run a customer statement with details from the invoice. I will have to og off my computer and log back on to see if it works. BRB.

[Former Member · Accepted solution]

I am unable to provide the report I had as it will not open as it says "Out of Memory" Below is what I started with ... basically an aging detail, where I was adding Invoice details from the below two tables (INV1 and OINV). Each time I would add a field/column, it woudl slow down and crash. INNER JOIN INV1 T2 ON T1.CardCode = T2.BaseCard INNER JOIN OINV T3 ON T2.DocEntry = T3.DocEntry SELECT T1.CardCode AS 'Cust Num', T1.CardName AS 'Cust Name', T0.SysDeb AS 'Debit Amt', T0.SysCred * -1 AS 'Credit Amt', CASE WHEN T0.TransType = 13 THEN 'AR Invoice' WHEN T0.TransType = 14 THEN 'AR Cred Memo' WHEN T0.TransType = 24 THEN 'Payment' ELSE 'Other' END AS 'Trans Type', T0.Ref1 AS 'Reference', FcCurrency AS 'Currency', CONVERT(VARCHAR(10), RefDate, 103) 'Posting Date', CONVERT(VARCHAR(10), DueDate, 103) 'Due Date', CONVERT(VARCHAR(10), T0.TaxDate, 103) 'Doc Date' , CASE WHEN (DATEDIFF(DD,RefDate,Current_Timestamp)) +1 0 THEN SysCred * -1 ELSE SysDeb END END AS '0-30 Days', CASE WHEN (DATEDIFF(DD,RefDate,Current_Timestamp)) +1 > 30 AND (DATEDIFF(DD,RefDate,Current_Timestamp)) +1 < 61 THEN CASE WHEN SysCred < > 0 THEN SysCred * -1 ELSE SysDeb END END AS '31 to 60 Days', CASE WHEN (DATEDIFF(DD,RefDate,Current_Timestamp)) +1 > 60 AND (DATEDIFF(DD,RefDate,Current_Timestamp)) +1 < 91 THEN CASE WHEN SysCred < > 0 THEN SysCred * -1 ELSE SysDeb END END AS '61 to 90 days', CASE WHEN (DATEDIFF(DD,RefDate,Current_Timestamp)) +1 > 90 AND (DATEDIFF(DD,RefDate,Current_Timestamp)) +1 < 121 THEN CASE WHEN SysCred < > 0 THEN SysCred * -1 ELSE SysDeb END END AS '90 to 120 Days', CASE WHEN (DATEDIFF(DD,RefDate,Current_Timestamp)) +1 > 120 THEN CASE WHEN SysCred != 0 THEN SysCred * -1 ELSE SysDeb END END AS '120 Plus Days' FROM JDT1 T0 INNER JOIN OCRD T1 ON T0.ShortName = T1.CardCode AND T1.CardType = 'C' INNER JOIN INV1 T2 ON T1.CardCode = T2.BaseCard INNER JOIN OINV T3 ON T2.DocEntry = T3.DocEntry WHERE T0.IntrnMatch = '0' AND T0.BalDueDeb !=  T0.BalDueCred and T1.CardCode  Like '%[%0]%' ORDER BY T1.CardCode, T0.TaxDate FOR BROWSE

[leon_laikan · Accepted solution]

Hi Danielle,

I presume you are using SAP's Query Generator for running your queries.

If yes, you will run "Out of Memory" if your query returns too much data, because it is limited by the memory of your computer.

For instance, if I run this simple query on my computer:

"SELECT * FROM OJDT", my computer ALWAYS runs out of memory and always crashes. ALWAYS! ....

Two solutions to avoid this problem:

1. Restrict your output by using WHERE clause, so that less data is returned.

2. Use MANAGEMENT STUDIO instead of QUERY GENERATOR. It will not crash because the query is run on the server, which has much more memory than your local computer.

For my part, I have stopped using Query Generator since long mainly because of its limitations (one of which is the out of memory problem)

Change to Management Studio. You won't regret it! It is much more powerful than Query Generator and has more capabilities.

Best Regards,

Leon

[Former Member · Accepted solution]

Thank you Leon! I spoke to my project management team and they told me they can not give me access to Studio Management without taking classes. Once I take classes I can use it as they advised I can crash SAP if I am not careful. Thanks for your help as this might be the solution if I can learn it quick enough!

[leon_laikan · Accepted solution]

Hi Danielle,

Fine then!

With Management Studio, you can write the same SQL as with Query Generator.

Besides, it has numerous advantages compared to Query Generator.

• It is MUCH more powerful than Query Generator.
• It's easy to use, once you learn the basics. No need to take classes.
• If you need a book on SQL, there are hundreds on the market, And many free sites also!
• If you need SQL oriented towards SAP B1, there is only 1 book, and a good one! Called Mastering SQL Queries for SAP Business One by Gordon Du.
• The same Gordon Du will answer many of your questions if you post them on the SAP Forum (Reporting & Printing section)
• You can write much longer codes (seems there is no limit!) with MS
• No more computer memory crashes!
• When an error occurs, it gives you an indication about its nature, so debugging is easier
• You can write Stored Procedures. These are queries you save on the server, and allows you to run complex queries. With complex projects, SPs are indispensable.
• You can run small parts of the program, just by highlighting the lines you wish to run
• You can open numerous queries at once and run whichever you want
• And... you can use it with CRYSTAL REPORTS (ask your SAP Partner to give you access)

       to generate endless professional-looking reports.

Once you get used to it, you will stop using Query Generator, except for small tasks.

If you have problems, keep posting. Someone will help you.

Best Regards,

Leon

ADDED

>>Once I take classes I can use it as they advised I can crash SAP if I am not careful.

You CAN crash SAP even if you use Query Generator!

Sure you have to be CAREFUL when programming SAP, whether using Management Studio or Query Generator.

And remember, there are many things SAP does not allow you to do! You may lose support!

At the same time, if you are TOO AFRAID, you will never produce anything useful

What I do when I give SAP access to my staff is as foll:

I ask my SAP Consultant to make a copy of the live SAP database - and name it, say DUMMY or SIMULATION.

Then my staff learn MANAGEMENT STUDIO using the DUMMY database. Later, they may use the LIVE database

If anything goes wrong, it's DUMMY which is corrupted.

----

You don't need to be an expert on Management Studio.

Learning how to use it to write and run queries takes a few minutes...

You must convince your SAP Partner to give you access to MS to let you learn using the DUMMY database.

---

For my part, I always develop all my applications on the DUMMY database.

Only after extensive testing will I run LIVE!