SQL Statement to update F0411

i_stevens

i_stevens

Member
Hi All

I have recently downloaded ESU JD10863 for B733.2. One of the SARs (4901666)
has special instructions that I would like to implement.

The SAR indicates that "Because the format of RPPO in F0411 has changed it is
recommended that an SQL statement be written to update all existing records in
F0411 where RPPO is NOT blank and left pad the existing Purchase Order Number
with zeros. The Purchase Order Number (RPPO) field for existing records will
be in the following format '1234'. The SQL statement should update them to look
like this '00001234'."

I initially asked the JDE response line for assistance, but have been told that
it is "against policy" to provide SQL statements to customers.

Being a relative newbie to SQL and it's uses (and it's potential hazards), I was
wondering if somebody out there would be so kind as to email me with the SQL
statement that should be used to achieve this.

Any help would be greatly appreicated.

Thanks
Ian

[email protected]
AS400 720 V4R4M0 Ent Server, NT 4.0 Deployment Server
B7332 co-existence with A7.3 cu 11

View our website at http://www.monadel.com.au

|---------------------------------------------------------------------------|
| |
| If this communication is not intended for you and you are not an|
| authorised recipient of this email you are prohibited by law from dealing|
| with or relying on the email or any file attachments. This prohibition|
| includes reading, printing, copying, re-transmitting, disseminating,|
| storing or in any other way dealing or acting in reliance on the|
| information. If you have received this email in error, we request you|
| contact us immediately by returning this email to|
| [email protected] and destroy the original. |
|---------------------------------------------------------------------------|






B733.2 SP11.1, A73 Cum 11 (Payroll)
Ent Svr AS/400 V4R4, Dep Svr NT4 SP5
 
Ian,
The SQL statement will be as following:
UPDATE F0411 SET RPPO = '00001234'.
However I would recommend you to run this over some selected orders before
you go for the entire F0411.
Also, maybe run the update over all records when this file is being used the
least.

Cheers,
[email protected]
 
If you want all F0411 records to have the same RPPO this might be okay, I
would prefer the SQL to be a bit more flexible and work for any value of
RPPO -

I suggest:

UPDATE F0411 SET RPPO = LPAD(RPPO,8,'0');

This will left pad RPPO with zeros - unto 8 characters.

I think the syntax is correct, try it in Prototype first. You could also do
it for a selection of Invoices just to check it works first.



OW733.3 Xe SP 14.2
Enterprise Server - Intel NT + Oracle 8.0.6
Client - Citrix TSE + 4 NT PC's for development
 
Thanks to all who responded.

Carl came the closest with his response. As Carl has pointed out the SQL needs
to be flexible and work for any value of RPPO.

Unfortunately, it appears that there isn't a LPAD function on the SQL on the
AS/400.

Luckily for me, all of our purchase orders are currently six characters long so
I think the following will work:

UPDATE F0411 SET RPPO = '00' || LEFT(RPPO,6) WHERE RPPO <>''

Thanks again.

Ian





Carl_Fisher <[email protected]> on 16/07/2001 03:52:02 PM



B733.2 SP11.1, A73 Cum 11 (Payroll)
Ent Svr AS/400 V4R4, Dep Svr NT4 SP5
 
Re: RE: SQL Statement to update F0411

Just a quick note on this. The lpad would be good, but I don't know the equivalent in SQL. You could write a UBE to do this in ER pretty easily though.

Also, using your code, if you had different lengths of PO#:

select substr('00000000' || trim(rppo) ,-8,8) from f0411

works in oracle. The left and right function don't work in oracle sql. To do the right function, the substring with a negative start value (-8) gives an equivalent. This would also pad with 0's any blank values.

Regards,
David
 
Back
Top