Update field with the systemdate
- Thread starter gerie
- Start date
MaximMonin
Member
field = now. (datetime)
or
field = today. (date)
or
field = today. (date)
gerie
Member
When I want to make a partial dump I use the following statement:
OUTPUT TO VALUE ("crdtrs.d").
FIND CURRENT DWHdate.
FOR EACH crdtrs NO-LOCK WHERE crdtrs.date-lc >= DWHdate.date:
EXPORT crdtrs.
END.
I already have the table DWHdate with the field date, wich is filled with a date aswell.
My output file says:
** No DWHdate record is available. (91)
OUTPUT TO VALUE ("crdtrs.d").
FIND CURRENT DWHdate.
FOR EACH crdtrs NO-LOCK WHERE crdtrs.date-lc >= DWHdate.date:
EXPORT crdtrs.
END.
I already have the table DWHdate with the field date, wich is filled with a date aswell.
My output file says:
** No DWHdate record is available. (91)
MaximMonin
Member
FIND CURRENT DWHdate. - this operator causes error.
find current ordianry used to change lock status, else you have to use something like find first/last.
find current ordianry used to change lock status, else you have to use something like find first/last.
gerie
Member
Sigh..... Sorry to bother you again.
My complete procedure works just fine in the procedure editor connected to the database.
So I added the connect to the database.
Tried to compile the procedure.
But again I'm receiving messages on the new table DWHdate.
CONNECT -db dbname -N tcp -H servername -S portnumber.
OUTPUT TO VALUE ("crdtrs.d").
FIND FIRST DWHdate.
FOR EACH crdtrs NO-LOCK WHERE crdtrs.date-lc >= DWHdate.DATE:
EXPORT crdtrs.
END.
This is the message in the compile.log:
11:22:20 Looking for c:\exact\apps\dwh\dwhupdate.p
Compiling c:\exact\apps\dwh\DWHupdate.p
Unknown or ambiguous table DWHdate. (725)
** c:\exact\apps\dwh\DWHupdate.p Could not understand line 3. (196)
My complete procedure works just fine in the procedure editor connected to the database.
So I added the connect to the database.
Tried to compile the procedure.
But again I'm receiving messages on the new table DWHdate.
CONNECT -db dbname -N tcp -H servername -S portnumber.
OUTPUT TO VALUE ("crdtrs.d").
FIND FIRST DWHdate.
FOR EACH crdtrs NO-LOCK WHERE crdtrs.date-lc >= DWHdate.DATE:
EXPORT crdtrs.
END.
This is the message in the compile.log:
11:22:20 Looking for c:\exact\apps\dwh\dwhupdate.p
Compiling c:\exact\apps\dwh\DWHupdate.p
Unknown or ambiguous table DWHdate. (725)
** c:\exact\apps\dwh\DWHupdate.p Could not understand line 3. (196)
MaximMonin
Member
you cannot connect to db and run db script in 1 procedure.
you have to put db script to external procedure
like:
CONNECT -db dbname -N tcp -H servername -S portnumber.
run db_script.p.
dbscript.p:
OUTPUT TO VALUE ("crdtrs.d").
FIND FIRST DWHdate.
FOR EACH crdtrs NO-LOCK WHERE crdtrs.date-lc >= DWHdate.DATE:
EXPORT crdtrs.
END.
you have to put db script to external procedure
like:
CONNECT -db dbname -N tcp -H servername -S portnumber.
run db_script.p.
dbscript.p:
OUTPUT TO VALUE ("crdtrs.d").
FIND FIRST DWHdate.
FOR EACH crdtrs NO-LOCK WHERE crdtrs.date-lc >= DWHdate.DATE:
EXPORT crdtrs.
END.
Stefan
Well-Known Member
Just spotted this due to your other post today on the SQL syntax. I think you really want to be using indexes for this. There is no date-lc index on crdtrs BUT since crdtrs records are normally never changed, you can use the combination of adm-nr and seq-nr-crd (the primary index).
The above is assuming you add / update your table to store sequence numbers (and possibly other stuff) per company.
Code:
OUTPUT TO "crdtrs.d".
FOR EACH admdat NO-LOCK:
FIND DWHdata WHERE DWHdata.adm_nr = admdat.adm-nr NO-LOCK NO-ERROR.
IF NOT AVAILABLE DWHdata THEN DO:
CREATE DWHdata.
ASSIGN
DWHdata.adm_nr = admdat.adm-nr
DWHdata.seq_nr_crd = 0
.
END.
FOR EACH crdtrs
WHERE crdtrs.adm-nr = DWHdata.adm_nr
AND crdtrs.seq-nr-crd > DWHdata.seq_nr_crd
NO-LOCK:
EXPORT crdtrs.
END.
END.
OUTPUT CLOSE.The above is assuming you add / update your table to store sequence numbers (and possibly other stuff) per company.