SQL debit & credit from amount (with negative and positive sign) 3

[Rock4J]

Hi,

My SQL Code is like this:

select tr_date,entry_ref,desc,source_cd,ref1,ref2,acct_no, ;
iif(amount>0, amount, 0) as debit, ;
iif(amount<0, amount*-1, 0) as credit, ;
0 as balance ;
from transact order by acct_no,tr_date

But unfortunately the field for "Debit" do not show any amount, but "*".

Please help me.

Regards,
Rocky

 

[vgulielmus]

Use CAST(IIF(....) as Numeric(...,...)) or something like 0000000000.00 instead of 0 as the third parameter in IIF()

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[Olaf Doschke]

To expand what Vilhelm-Ion Praisach said, look at the first result record. The debit there is 0, as the amount of the first record was <0, it resulted from iif(amount>0, amount, 0),
This will be a N(1) column and all further records will only be capable to hold a 1 digit debit.

That's the way VFP determines the types of fields of result sets, the first record decides that. If you want more digits, then you can instead write iif(amount>0, amount, 0000000.000), or better yet cast the field for that expression as
CAST(iif(amount>0, amount, 0) as Y), where Y is the short for a currency field (c is reserved for char). The same applies to the Credit field, otherwise the next query on first record amount<0 has the same problem in the Credit column.

Another way to enforce the reult column type to match that of the amount field is iif(amount>0, amount, amount-amount), as the expression amount-amount will become the type of the amount column, too.

Bye, Olaf.

 

[Rock4J]

Thanks a lot Vilhelm-Ion.. I used the 0000000000.00 and it works.. I don't know how to use the CAST function, but at least my problem solved.

next, I want to INNER JOIN "Transact" Table with another table named "Account" and use the "opening balance" for example the fieldname is "ytd_bal",

select transact.tr_date,transact.entry_ref,desc,transact.source_cd,transact.ref1, ;
transact.ref2,transact.acct_no, iif(transact.amount>0, transact.amount, 0000000000.00) as debit, ;
iif(transact.amount<0, transact.amount*-1, 0000000000.00) as credit, ;
INCREASING_VALUE_FROM_YTD_BAL as balance ;
from transact ;
INNER JOIN Account on transact.acct_no = account.acct_no ;
order by transact.acct_no, transact.tr_date

I want to know how to write the "INCREASING_VALUE_FROM_YTD_BAL" area..

Please help.

Regards,
Rocky

 

[Olaf Doschke]

You can't query a running sum (balance) inside the query, this has to be done on the resultset as "aftermath" in the sense of a scan and replace record by record.

Bye, Olaf.

 

[Rock4J]

Thanks so much Olaf for the clear explanation and alternatives.

Regards,
Rocky

 

[Rock4J]

Thanks again Olaf !

Does the scan code can be done in the COMMAND windows ?

I found a code like this:

Local nBal
nBal = account.ytd_bal
Select cards
Go top
Scan
    nBal = nBal + debit - credit
    replace balance with nBal
endscan
go top

but I do not know how to implement it in the COMMAND windows

Regards,
Rocky

 

[Olaf Doschke]

Use new from the VFP Toolbar and create a new PRG. Put the code inside. Select it with CTRL+A, now right click and "execute selection".
Besides saving this will jelp you repeat the same thing. After saving a PRG into some PRG file you can simply execute it with the ! toolbar button of the standard toolbar.

Why do you want to do this from the command window?

Bye, Olaf.

 

[Rock4J]

What I'm trying to do is actually, exporting the query result into excel, because the original program are written in DBASE programming and I don't have any knowledge about DBASE programming. That's why I hope at least I could get the data for the DBF tables using Foxpro Command windows.

Regards,
Rocky

 

[Olaf Doschke]

If you make use of VFP it doesn't matter if you execute VFP commands from command window or prg, what difference do you think there is?
You depend on VFP being capable to read the DBF in dbase format, which it obviously can do. There is no difference if it can do that from a command line or a prg.

Besides you act on the resultset, which is a vfp cursor, you don't act on the original DBF with the REPLACEs. "cards" should be the name of your query result, to get it that way you create it with INTO CURSOR cards READWRITE at the end of your query.

Bye, Olaf.

 

[Rock4J]

Thanks Olaf, I need some time to create the code. I'm confused to work with both transact.dbf and account.dbf at the same time (key: acct_no). I'm don't much knowledge in this. I do understand the scan code is something like do-until/while-loop in vb classic.

Regards,
Rocky

 

[Olaf Doschke]

Well, you don't need to do much, the code you showed would work, besides naming your result cards and makeing it READWRITE you simply would need to have the account dbf open at the same time (USE account IN 0 does that) and have positioned on the right acocunt, so that nBal = account.ytd_bal reads in that opening balance. and afterwards you can COPY TO cards.XLS TYPE XLS, as you most probably already know, to get your excel file.

Caution, this will ony work for a single account, if you need to process all accounts you need an outer scan loop on account.dbf AND filter the inner SCAN FOR cards.accot_no == account.acct_no

Bye, Olaf.

 

[Rock4J]

Thanks Olaf,

Please verify if this code is correct:

select tr_date,entry_ref,desc,source_cd,ref1,ref2,acct_no, ;
iif(amount>0, amount, 0000000.00) as debit, ;
iif(amount<0, amount*-1, 0000000.00) as credit, ;
0 as balance ;
from transact order by acct_no,tr_date
COPY TO "c:\documents and settings\administrator\desktop\gl.dbf"

select account
gotop
scan
        select GL
        gotop
	scan while gl.acct_no=account.acct_no
		nbal = nbal + gl.debit - gl.credit
		gl.balance = nbal
	endscan
endscan

Select GL
COPY TO "c:\documents and settings\administrator\desktop\GL_20150515.xls" TYPE XL5

Regards,
Rocky

 

[Rock4J]

I have done this :

local nbal

select tr_date,entry_ref,desc,source_cd,ref1,ref2,acct_no, ;
iif(amount>0, amount, 0000000.00) as debit, ;
iif(amount<0, amount*-1, 0000000.00) as credit, ;
0 as balance ;
from 'c:\documents and settings\administrator\desktop\transact.dbf' order by acct_no,tr_date
COPY TO "c:\documents and settings\administrator\desktop\gl.dbf"

select 0
use 'c:\documents and settings\administrator\desktop\account.dbf' alias account

select 1
use 'c:\documents and settings\administrator\desktop\GL.dbf' alias gl

select account
go top
scan
	nbal = account.ytd_bal
	select gl
    	go top
	scan while gl.acct_no=account.acct_no
		nbal = nbal + gl.debit - gl.credit
		gl.balance = nbal && ERROR
	endscan
endscan

Select gl
COPY TO "c:\documents and settings\administrator\desktop\GL_20150515_777.xls" TYPE XL5

but at the line with "&&ERROR" an error appeared : "Object GL is not found"

Regards,
Rocky

 

[Olaf Doschke]

You're missing the initialization of nbal for each account and you didn't opened the account dbf. Also there is no need to copy to gl.dbf, but it doesn't hurt. Some more comments inline and afterwards.

[COLOR=#CC0000]*open up account table
use account in 0

* define variables
Local nbal[/color]

*sql opens tables itself, so no need to open transact.dbf
* changed your iif to result debit and credit as the same field type as amount
* also your balance field needs to be more than N(1)!!!!!!
* if cast doesn't work (if your vfp version is older) use 000000.000 as balance instead, or amount-amount as balance, for the same reason to get the correct type
select tr_date,entry_ref,desc,source_cd,ref1,ref2,acct_no, ;
iif(amount>0, amount, amount-amount) as debit, ;
iif(amount<0, -amount, amount-amount) as credit, ;
Cast(0 as y) as balance ;
from transact order by acct_no,tr_date
COPY TO "c:\documents and settings\administrator\desktop\gl.dbf"
Index on acct_no tag acct_no && accelerate inner scan loop

select account
* gotop not needed, scan always starts from top
scan
        [COLOR=#CC0000]nBal = account.ytd_bal[/color] && you missed copying that line, you NEED the initial balance!
        select GL
        * gotop
	scan [COLOR=#CC0000]for[/color] gl.acct_no=account.acct_no [COLOR=#CC0000]&& I said FOR, not WHILE, big difference![/color]
		nbal = nbal + gl.debit - gl.credit
                [COLOR=#CC0000]* wrong: gl.balance = nbal[/color] 
		REPLACE balance with m.nbal in gl [COLOR=#CC0000]&& what is so hard in copying code? You can't assign something to a field, you need replace or update-sql[/color]
	endscan
endscan

* Select GL not needed, it's still selected from the scan loop
COPY TO "c:\documents and settings\administrator\desktop\GL_20150515.xls" TYPE XL5

While doesn't work here, WHILE would need a locate of the first record with the wanted acct_no and would need records to be ordered by acct_no, otherwise it may only pick some acct_no records, which are sequential, FOR will pick all records for the acct_no, no matter how they are scattered. An Index of gl on acct_no will help to make this faster, which I did. This still doesn't enable you to use WHILE instead, that still will stop, if the first record does not have the acct_no of account, which will be most time, except for one account! You could also set up a relation between account and gl based on the index, but FOR will work ok in any case.

Bye, Olaf.

 

[Olaf Doschke]

One last question, that you should be able to answer better than me: Are you sure you add debit and subtract credit? The initial amount data of the transact.dbf is negated for credit, which speaks for your solution, but I'd always associate a minus with a debt and a plus with a credit I have. Depends on the point of view, most probably. If you amend what you name credit and what debit and then sum +credit-debit, you'll get the same result anyway, you are actually summing the original transact.amount values, but the naming in your gl.dbf will be "more" correct, I'd say.

Bye, Olaf.

 

[Rock4J]

Thanks so much Olaf..

It works after some adjustment:

use account in 0
[COLOR=#EF2929]use gl in 1[/color]

local nbal

select tr_date,entry_ref,desc,source_cd,ref1,ref2,acct_no, ;
iif(amount>0, amount, 0000000.00) as debit, ;
iif(amount<0, amount*-1, 0000000.00) as credit, ;
[COLOR=#EF2929]000000.000 as balance[/color] ; && my foxpro program version might be older
from transact order by acct_no,tr_date
COPY TO "c:\documents and settings\administrator\desktop\gl.dbf"
Index on acct_no tag acct_no && accelerate inner scan loop

select account
go top
scan
	nbal = account.ytd_bal
	select gl
    	go top
	scan for gl.acct_no=account.acct_no
		nbal = nbal + gl.debit - gl.credit
		REPLACE balance with m.nbal in gl
	endscan
endscan

Select gl
COPY TO "c:\documents and settings\administrator\desktop\GL_20150515_777.xls" TYPE XL5

I didn't really know about the "scan" syntax for vfp, i only assume it like "do-while" and "do-until" in vb classic as I've said before, although there is "for-next" in vb-classic, it's meaning are not the same with vfp's "for". Anyhow, You did explained a lot of things already, such the usage of "scan-for", "Index" and "tag", which i do not know before. Thanks so much.. I really appreciates it..


Regards,
Rocky

 

[Olaf Doschke]

OK. You don't need the use gl at the start, as you generate it in the COPY TO line. You don't need the go tops, because you don't use the REST scope of the SCAN loops you scan from start to end.

Aside of that the FOR option of several commands in VFP has nothing to do with the FOR..NEXT or FOR...ENDFOR loop, which VFP also knows. FOR is rather like a WHERE clause or a FILTER in case it's used as option of SCAN (or LOCATE and some more), so the inner scan loop just iterates all records of the current account. SCAN is a loop you don't know from VB, because it's iterating the records of a dbf or cursor, which is a concept not known in other languages.

Bye, Olaf.