programming question

I would like to know why the data can't append to the output file from the
script below:

**
close databases
set date to british
set safety off
set century on
set cpdialog off
set talk off
set defa to c:\GP\utilities
set path to c:\GP\utilities


startday=ctod("01/09/2007")
endday=ctod("03/09/2007")
inpath = "D\Source\"

USE ABC_Count
ZAP

in_date = startday

DO WHILE in_date <= endday
?in_date
wdate = DTOC(in_date,1)
wdd = SUBSTR(wdate,7,2)
wmm = SUBSTR(wdate,5,2)
wyy = SUBSTR(wdate,1,4)
filename = inpath + wyy + wmm + "\" + wyy + wmm + wdd + "-stock.dbf"

use &filename in select(1) alias a

select a.date,a.location,a.stock,ud.type, ;
sum(iif(a=1)) as stockAcount, ;
sum(iif(b=0)) as stockBcount, ;
from a;
group by a.location;
into table a1
APPEND FROM a1 FOR (stock= 'A')
REPLACE bdate WITH in_date FOR EMPTY(bdate)
in_date=in_date+1
ENDDO

copy to stock.csv type xls
close database

Man-wai
Fri Sep 07 04:52:45 PDT 2007

> startday=ctod("01/09/2007")
> endday=ctod("03/09/2007")
> inpath = "D\Source\"
>
> USE ABC_Count
> ZAP
>
> in_date = startday
>
> DO WHILE in_date <= endday
> ?in_date
> wdate = DTOC(in_date,1)
> wdd = SUBSTR(wdate,7,2)
> wmm = SUBSTR(wdate,5,2)
> wyy = SUBSTR(wdate,1,4)
> filename = inpath + wyy + wmm + "\" + wyy + wmm + wdd + "-stock.dbf"
>
> use &filename in select(1) alias a

* use "temp" rather than a single letter "a"
use (filename) in 0 alias temp
>
> select a.date,a.location,a.stock,ud.type, ;
> sum(iif(a=1)) as stockAcount, ;
> sum(iif(b=0)) as stockBcount, ;
> from a;
> group by a.location;
> into table a1

* use cursor for temporary stuff
... into cursor a1

* you forgot to select abc_count first
select abc_count

> APPEND FROM a1 FOR (stock= 'A')

append from dbf("a1") for stock="a"

* which table are u trying to REPLACE fields?

> REPLACE bdate WITH in_date FOR EMPTY(bdate)

> in_date=in_date+1
> ENDDO

* you again forgot to select the table first
select abc_count

> copy to stock.csv type xls
> close database
>
>


--
@~@ Might, Courage, Vision, SINCERITY.
/ v \ Simplicity is Beauty! May the Force and Farce be with you!
/( _ )\ (Xubuntu 7.04) Linux 2.6.22.6
^ ^ 19:49:01 up 12 min 0 users load average: 0.00 0.21 0.25
news://news.3home.net news://news.hkpcug.org news://news.newsgroup.com.hk

LL
Fri Sep 07 04:57:36 PDT 2007

thanks Chang, i will have a try....

"Man-wai Chang ToDie" <toylet.toylet@gmail.com> ¼¶¼g©ó¶l¥ó·s»D:eqAjSWU8HHA.5504@TK2MSFTNGP02.phx.gbl...
>> startday=ctod("01/09/2007")
>> endday=ctod("03/09/2007")
>> inpath = "D\Source\"
>>
>> USE ABC_Count
>> ZAP
>>
>> in_date = startday
>>
>> DO WHILE in_date <= endday
>> ?in_date
>> wdate = DTOC(in_date,1)
>> wdd = SUBSTR(wdate,7,2)
>> wmm = SUBSTR(wdate,5,2)
>> wyy = SUBSTR(wdate,1,4)
>> filename = inpath + wyy + wmm + "\" + wyy + wmm + wdd + "-stock.dbf"
>>
>> use &filename in select(1) alias a
>
> * use "temp" rather than a single letter "a"
> use (filename) in 0 alias temp
>>
>> select a.date,a.location,a.stock,ud.type, ;
>> sum(iif(a=1)) as stockAcount, ;
>> sum(iif(b=0)) as stockBcount, ;
>> from a;
>> group by a.location;
>> into table a1
>
> * use cursor for temporary stuff
> ... into cursor a1
>
> * you forgot to select abc_count first
> select abc_count
>
>> APPEND FROM a1 FOR (stock= 'A')
>
> append from dbf("a1") for stock="a"
>
> * which table are u trying to REPLACE fields?
>
>> REPLACE bdate WITH in_date FOR EMPTY(bdate)
>
>> in_date=in_date+1
>> ENDDO
>
> * you again forgot to select the table first
> select abc_count
>
>> copy to stock.csv type xls
>> close database
>>
>>
>
>
> --
> @~@ Might, Courage, Vision, SINCERITY.
> / v \ Simplicity is Beauty! May the Force and Farce be with you!
> /( _ )\ (Xubuntu 7.04) Linux 2.6.22.6
> ^ ^ 19:49:01 up 12 min 0 users load average: 0.00 0.21 0.25
> news://news.3home.net news://news.hkpcug.org news://news.newsgroup.com.hk

Anders
Fri Sep 07 05:48:50 PDT 2007

Since VFP8 I believe you're no longer allowed to break the SQL rule the the
column list in the GROUP BY must list the selected columns (except the
aggegates). Thus
SELECT a.location, a.stock, ud.type ...
GROUP BY a.location, a.type, ud.type

but what is ud.type? Is it an object.property, a table column? Anyway, as
it's not a related table it becomes a fixed value and the query could be
perhaps be allowsed in recent versions of VFP with
SELECT a.location, a.type, MAX(ud.type). SUM(... ;
GROUP BY a.location, a.stock

-Anders


"LL" <1@1.com> wrote in message
news:e4ZLiSU8HHA.1484@TK2MSFTNGP06.phx.gbl...
>I would like to know why the data can't append to the output file from the
>script below:
>
> **
> close databases
> set date to british
> set safety off
> set century on
> set cpdialog off
> set talk off
> set defa to c:\GP\utilities
> set path to c:\GP\utilities
>
>
> startday=ctod("01/09/2007")
> endday=ctod("03/09/2007")
> inpath = "D\Source\"
>
> USE ABC_Count
> ZAP
>
> in_date = startday
>
> DO WHILE in_date <= endday
> ?in_date
> wdate = DTOC(in_date,1)
> wdd = SUBSTR(wdate,7,2)
> wmm = SUBSTR(wdate,5,2)
> wyy = SUBSTR(wdate,1,4)
> filename = inpath + wyy + wmm + "\" + wyy + wmm + wdd + "-stock.dbf"
>
> use &filename in select(1) alias a
>
> select a.date,a.location,a.stock,ud.type, ;
> sum(iif(a=1)) as stockAcount, ;
> sum(iif(b=0)) as stockBcount, ;
> from a;
> group by a.location;
> into table a1
> APPEND FROM a1 FOR (stock= 'A')
> REPLACE bdate WITH in_date FOR EMPTY(bdate)
> in_date=in_date+1
> ENDDO
>
> copy to stock.csv type xls
> close database
>

Bernhard
Fri Sep 07 06:41:22 PDT 2007

Hi LL,

in addition to what others already have written:
IIF( ) needs 3 parameters
What is a and what is b in your sum(iif(...)) expressions?

> select a.date,a.location,a.stock,ud.type, ;
> sum(iif(a=1)) as stockAcount, ;
> sum(iif(b=0)) as stockBcount, ;
> from a;
> group by a.location;
> into table a1
> APPEND FROM a1 FOR (stock= 'A')
> REPLACE bdate WITH in_date FOR EMPTY(bdate)
> in_date=in_date+1
> ENDDO

Regards
Bernhard Sander

Man-wai
Fri Sep 07 06:59:46 PDT 2007


> thanks Chang, i will have a try....

Always select the DBF first before doing a REPLACE or APPEND! :)

--
@~@ Might, Courage, Vision, SINCERITY.
/ v \ Simplicity is Beauty! May the Force and Farce be with you!
/( _ )\ (Xubuntu 7.04) Linux 2.6.22.6
^ ^ 21:58:01 up 2:21 0 users load average: 0.00 0.00 0.00
news://news.3home.net news://news.hkpcug.org news://news.newsgroup.com.hk