Speed consideration VO & codeblocks vs X# and IF's

[wriedmann]

Hi Dick,

you are right: ADS does not fully implement the SQL language, but also other SQL databases are doing this.
Therefore you need to simplify your statement as possible until it works, and do the rest in your code, maybe using an array or an intermediate DBF table, if the data becomes too large.

To read ADS by SQL, there is a class ADSSQLServer. You can use it like this:

Code: Select all

AX_SetConnectionHandle( oApp:hADSHandle )
oServer := ADSSQLServer{ cSelect,,, "AXSQLCDX" }
oServer:GoTop()
while ! oServer:EoF
  _cUniqueID := AllTrim( oServer:FieldGet( #UniqueID ) )
  oServer:Skip()
end
oServer:Close()

Wolfgang

[robert]

Dick,

(As far as I know) in all SQL languages, you need to include the colums from the SELECT clause that are not aggregate calculations into the group by clause. Try this:

select j.keynr , j.debcred, sum(CASE WHEN j.debcred='D' THEN j.amt ELSE j.amt*-1 END) from jpost j group by j.keynr , j.debcred

I am not sure what you wanted to achieve in the first query:

select j.keynr , sum(CASE WHEN j.debcred='D'THEN j.amt ELSE j.amt*-1 END) from jpost j group by 1

How many rows does that return? What does the "group by 1" do ?
I would expect the following group by clause:
group by j.keynr

https://devzone.advantagedatabase.com/d ... clause.htm

Robert

[ic2]

Hello Robert,

YES! That's it; thank you so much. I thought I checked for the specific error in all my documentation but did not find the one you sent as a link do ADS Devzone. Indeed it works and once you know the error message seems to explain it enough;).

What I want to achieve is a quick overview from journal entries: General Ledger from jpost, description of the GL from a description database and the total amount booked taking into debit or credit. This now works.

@Paul, Wolfgang: I can already present that in a bBrowser with just the few lines I showed (without the need to convert it first to DBF) but I may need to have access it via DBF or otherwise; that depends a bit on what I can do more with the SQL statement. I will check your solutions for that.

Fact is that many 1000's of journal records indeed return the required table with totals in seconds instead of in minutes.

I'll let you know the end result!

Dick

[ic2]

I added an extra option in Financial Reporting of our program to create a quick overview using an SQL statement via ADS. We already had a window to run ADS SQL queries, assign these to a bBrowser which can export the content to Excel and use that.This works, as Wolfgang predicted, very fast. I may use the suggestions to use the SQL cursor as a faster start to make the current reports as well. The browser data output has some limitations compared to the existing report but for now as a fast alternative it is adequate.

For future readers I translated below some of the fields/data in the query I use now which Robert helped me finalize. The query below sums all amounts (+ for debit,minus for credit) within the date range from journal entries (jpost.dbf) and add the last end balance amounts for balance accounts (subbal.dbf). The output is the GL number, the description from a description database (coded "RS" as type of description) and the total of the 2 values. It's still in our VO based program but I think this option will have the highest impact on the customer's speed experience for now.

select reknr as account, desc as description, sum(total) as amount from (select j.gl,sum(CASE WHEN debcred='D' THEN amount ELSE amount*-1 END) total from jpost j where j.datein>='2017-01-01' AND j.datein<='2017-12-31' group by j.gl UNION all select s.gl,sum(CASE WHEN LEFT(gl,1)>='4' then 0 WHEN debcred='D' THEN amount ELSE amount*-1 END) total from subbal s where s.datein='2015-12-31' group by s.gl) t join desc on gl=desk.glnr and desc.typeofdesc='RS' group by gl,desc