Update of a date field

gerie

gerie

Member
OE 10.1B
OS Windows 2003

I'm trying to update a datefield, because one of the users entered a wrong date and processed the change.
I'm tying to update the field using Squirell in order to update via a SQL query. However it doesn't work.
I could also update the field via a procedure, but I don't know how to write the correct procedure.

I tried this query:
Update pub.ledtrs set ledtrs."date"='13/04/2011' where ledtrs."adm-nr"=111 and ledtrs."journal-nr"=12 and ledtrs.creditor=20119

Then I'm getting the error:
Error: [DataDirect][OpenEdge JDBC Driver][OpenEdge] Syntax error in SQL statement at or about "."date"='13/04/2011' where ledtrs."adm-n" (10713)
SQLState: HY000
ErrorCode: -210056

I also tried different date formats, but no succes.

I don't know how to update this field via the procedure editor.

I hope someone can help me here.
 
FIND FIRST ledtrs WHERE
ledtrs.adm-nr = 111
AND ledtrs.journal-nr = 12
AND ledtrs.creditor = 20119 EXCLUSIVE-LOCK NO-ERROR.
IF AVAILABLE ledtrs THEN DO:
ledtrs.date = 13/04/2011.
DISPLAY ledtrs.date.
END.
ELSE DO:
MESSAGE "locked" VIEW-AS ALERT-BOX.
END.
 
At the end of the run I get a message ** "finance/trig/twledtrs.p"was not found (293)
The new date is displayed, but the database is not altered with the new date.
 
You need to modify PROPATH so that "finance/trig/twledtrs.p" can be found.

Or, if you are evil (or simply don't care about the consequences):

DISABLE TRIGGERS FOR LOAD OF ledtrs.

At the top of your update program.
 
That table and trigger look very familiar... :-)

I had a 5 second look at Squirrel and decided I was not going to be able to test my connection quickly, so with the OpenEdge Architect SQL Editor (which also uses a JDBC connection to the database):

Code: 
select * from pub.ledtrs where "adm-nr" = 621 and "date" = '2011-01-01'

Works fine (date value in format yyyy-mm-dd with single apostrophes around it).

If you want to continue along the procedure editor path and assuming that the error is on 'finance/trig/tw/twledtrs.p'. Depending on the version of Exact Financials you are using, add the pls to your propath first (assuming the Exact working directory as your current directory):

3.5 (and older):

Code: 
PROPATH = ".;lib/gui-ptch.pl;lib/gui-fin.pl".

7.0:

Code: 
PROPATH = ".;lib/ef-blp.pl;lib/ef-bl.pl".

I would also advise you to have a look at the indexes on ledtrs and choose your selection fields based on one of those. Using adm-nr, journal-nr and creditor will only get you an index with adm-nr resulting in all ledtrs records of that company being locked while the find does its work. I you use adm-nr, fin-yr, journal-nr, entry-nr you will have decreased the result set dramatically.
 
The problem was not in selecting the data, but in changing the data in the different tables.
To disable the triggers is something that works.
It's no problem that the tables are locked, I just need to time it in the weekend after I created the back-up. But you are right, I will write the procedure and use indexes.
 
gerie said:
The problem was not in selecting the data, but in changing the data in the different tables.
Click to expand...

I thought you were just having issues with your dates. The update using SQL (which bypasses the ABL triggers) also works fine when using the correct date format using:

Code: 
update pub.ledtrs set "date" = '2011-04-13' where "adm-nr" = 111 and "journal-nr" = 12 and creditor = 20119

The above updates just fine using OE Architect's SQL Editor.
 
You must log in or register to reply here.
Back
Top