*** QUIZ *** F0911 "Update"

  • Thread starter Christian Audet
  • Start date
Re: *** QUIZ *** F0911 \"Update\"

Christian, I understand.. but that assumes that the field you want to use for selection (where clause) is in an index at all. You can't go creating an index for each field you ocassionally want to use for record selection.

And as far as the flat file explanation, if the development management at JDE thinks it should just keep cobbling code together to work around limitations imposed by long-abandoned technologies, I think some heads need to roll.

---------------------------------
OneWorld Xe SP15
Clustered Windows 2000 + SQL 2000
 
Re: *** QUIZ *** F0911 \"Update\"

Hi everybody,

Denver finally came to me with this answer :

"
Hello Christian,

The development replied that the issue is fixed under
SP16_011 --> SAR #5369350

SP16.1_001 --> SAR #5361788
You will see that the SAR doesn't mention the exact issue as you have
described to me but the developer confirm that the SAR fix the issue. The
workaround if you don't want to go to those SP is to create the new index
which contain only batch number, generate index, then use table I/O on the
new index.

Hope this answer your question. I'll close the call now.
Thanks,
Ekvara.
"

So I will have to wait for SP16 if I don't want to create a new index.

Christian Audet


Implementing B7333 (Xe) SP14.1, SQL
(Support B732, B7331 and B7332)
 
Re: *** QUIZ *** F0911

Hi All,

It as been one year since I created this thread and here I am again !

What's new today :

- In my last post Denver said that the problem will be fixed under SP17, wrong I run SP18.1 and they error is still there and even worse !

- Last year in this thread we came up with the conclusion that when you do partial key update you need to use all key field from the first one. It apear to be wrong from from a error that one developer's got here today. We were doing a update on F15013B.

I need to mention that I've us a key with those fields inside (exact same order) :

NODOCO (I've used this one)
NOLSVR (I've used this one)
NOYEOV (I did not use this one because if there is more than one I want to update them all)

here is the code :

VA evt_cSuspendBilling_SUSP = "Y"
VA evt_mnLeaseVersion_LSVR = 1
VA evt_mnLeaseNumber_DOCO = 222
F15013B.Update
VA evt_mnLeaseNumber_DOCO = TK Document (Order No, Invoice, etc.)
VA evt_cSuspendBilling_SUSP -> TK Suspend Code
VA evt_mnLeaseVersion_LSVR = TK Lease Version

here is the generated SQL :

UPDATE F15013B SET NODOCO = 222, NOSUSP = "Y" (of course this code is wrong and generate duplicate key)

where it should be :

UPDATE F15013B SET NOSUSP = "Y" WHERE NODOCO = 222 and NOLSVR = 1

What's the conclusion ? last year we said that partial key update will work if we use all field starting from the first one in the key. Now I have the proof that it's not even true.

My problem is now fixed because I've found that there was a index with only the required field (NODOCO, NOLSVR)

All this to tell you again to be carefull when you do update to choose a index with EXACTLY the required field for selection not even one extra field at the end that you don't need to select on.

Give me your feedback

Christian Audet
 
Re: *** QUIZ *** F0911 \"Update\"

Hi Christian,

I came from the RPG/400 world. I have done coding in RPG/400 where it does not allow me to use a partial key that is in the middle of the key structure. So, I dont think that its a problem with Oneworld.
Sriram.
 
Re: *** QUIZ *** F0911 \"Update\"

Sriram,

Actually my post was to tell everybody that "yes" the problem exist in OneWorld like on the AS/400 and worse, my last post was to confirm that event partial key starting from the first key field is not working properly in OneWorld (I was told that this is possible on the AS/400 if you start from the first key).

Partial key update are so inconsistent in OneWorld that I will never sugest anybody doing it. You can believe me don't do any partial key update in OneWorld, I've tried even with SP18.1 and the problem is still there.

Christian Audet
 
Re: *** QUIZ *** F0911 \"Update\"

Christian.
Yes, it is possible on the AS/400 if you start from the first key.

And You are right. The best way is to create the index that you want in OW and do an update using that key instead of using the partial key.

Sriram.
OW XE SP18.1 AS/400.
 
Re: *** QUIZ *** F0911

I'll try to explain what I believe Christian is iterating - it is a bug... and one that we should all understand.

If I have a table with a five-field unique key and I attempt to update using only three(and it doesn't matter what order or sequence) of those unique keys, I should be notified 'during development' that the table is uniquely keyed on all five fields.

I should not be able to create an Update ER (or an Insert, for that matter) on any subset of the unique keys.

Christian - is this what you are relaying?
 
Re: *** QUIZ *** F0911

Daniel,

that's right except for one thing : OneWorld will never notify you.

OneWorld will generate a wrong SQL Update statement and you will not even notice if you don't take a look at the jdedebug.log and jde.log

RULE : When doing update, use EVERY fields in the key to do it

Christian
 
Re: *** QUIZ *** F0911

Excuse me Daniel but I re-read your post and I realized that when I told you you were right it was not true.

What I mean is using "any" index (unique or not) if you "Update" not using all fields for this index then you can get a wrong "translation" of your request in SQL.

Sorry for this, I "Try" to be as clear as possible but this is not easy to explain.

Christian Audet
 
Re: *** QUIZ *** F0911

Christian,

We are on the same page. If an update is attempted - and all unique keys are not used, then some very disturbing behavioral updates can occur...

Basically the SQL statement that is generated encompases more than is intended... time for a restore.

right?

Daniel (the other one0
 
Re: *** QUIZ *** F0911

Right for unique and non-unique key !

Christian
 
Re: *** QUIZ *** F0911

I have been reading these emails and I missed one thing. In which platform
these updates are occurring. AS/400, Oracle or Access.

Naveed
----- Original Message -----
From: "DBohner" <[email protected]>
To: <[email protected]>
Sent: Thursday, August 01, 2002 2:21 PM
Subject: Re: *** QUIZ *** F0911


unique keys are not used, then some very disturbing behavioral updates can
occur...Basically the SQL statement that is generated encompases more than
is intended... time for a restore.right?Daniel (the other one0




Senior Consultant
M&D Information Systems
847 477 0812
 
Re: *** QUIZ *** F0911

Hi Christian,

You are right. OneWorld will not/rarely notify you. Even though OneWorld notifies you, they are likely to be the wrong message sometimes.

I am always being very careful when updating a record with partial key because chances are it will update the wrong record if multiple records exist. For example, if record 1 and 2 has the same address number of 12345. But you really need to update the 2nd record. When comes to partial key update, chances are the 1st record will be updated!!! By using Select and Fetch Next to retrieve the full key values to do the update will work well.
 
Re: *** QUIZ *** F0911

Naveed,

The update occur in SQL but the problem is not database specific. The problem is not the way the database will process the UPDATE statement but the way OneWorld Generate the UPDATE statement.

Christian Audet
 
Re: *** QUIZ *** F0911

Christain,

I was not a member of 'List' when this thread originated, but I must say I have been very interested in its developments. I know I have used partial (starting at the beginning!!!) keys in ER to do updates and all has worked fine.

I am at home today, so I am referring only to the DEMO version. You mention your updates to F15013B using the following keys:
DOCO
LSVR
YEOV

On my demo, there is no such index (it is DOCO, YEOV, LSVR). Is it possible that you are still attempting an update using keyfld1 and keyfld3?? (skipping YEOV). Or is my DEMO index not same as full version?

I tested an update to a 5 key index, using just the 1st key field. It worked just fine. However, I did try it 2 different ways: one in a UBE ER and another in an NER. Interestingly, they produce different SQL statements. Both, however, produce the same results. The key field is DHJOBS.

UBE ER SQL:
UPDATE F550002H SET DHJOBS=14001.000000,DHDCF2='Test' WHERE ( DHJOBS = 14001.000000 )

NER SQL:
UPDATE F550002H SET DHDCF2=' ' WHERE ( DHJOBS = 14001.000000 )
 
Re: *** QUIZ *** F0911 \"Update\"

We were just having this same discussion two days ago about an update with
partial keys. What about key fields that are blank? Do those need to be
included also in an update? For example, we don't use multi-currency or
subsidiary account numbers, but both of those are part of the key in the
F0902. When we do an update, should we include a blank for SUB or CRCD?

This has been a very interesting discussion thread!

Chris

Xe, Update 3, SP18.1


Yes, it is possible on the AS/400 if you start from the first key.

And You are right. The best way is to create the index that you want in OW
and do an update using that key instead of using the partial key.

Sriram.
OW XE SP18.1 AS/400.




OneWorld Xe, SP18.1, Update 3
 
Re: *** QUIZ *** F0911

Jeremy,

Thanks, you made me look differently into the problem. Ok here is the result :

1- First my index name was (I changed it now) : LeaseNo, Version, YE Override+

2- The key is DOCO, YEOV, LSVR (Title did not match key info)

3- From point 1 & 2, yes you were right, I was again trying to update using "non-contiguous key fields"

4- Never rely on the index title, go to table design

5- I've realize that the table index was change (at this client) but only the title, strange !!

6- The good news is : "My update is now working with the use of another index"

7- The bad news is : "update using "non-contiguous key fields" is still not working even if JDEdwards told us that the issue was fixed under SP17"

8- The rule is : "Anyway, since my first issue last year, I try to avoid partial key update"

Thank for your replys

Christian Audet
 
Re: *** QUIZ *** F0911 \"Update\"

Chris,

What is the index that you are using for your update ?

I've checked the index list of F0902 and "most" of them have CRCD as the last field wich should be fine. But if you use this one :

index name : Cost Center, Object, +B
--------------------------------------------------
#define ID_F0902_COST_CENTER__OBJECT___B 6L

typedef struct
{
char gbmcu[13]; /* 705 to 717 */
char gbobj[7]; /* 718 to 724 */
char gbsub[9]; /* 725 to 733 */
MATH_NUMERIC gbctry; /* 734 to 782 */
MATH_NUMERIC gbfy; /* 783 to 831 */
char gbfq[5]; /* 832 to 836 */
char gblt[3]; /* 837 to 839 */
char gbcrcx[4]; /* 840 to 843 */
char gbcrcd[4]; /* 844 to 847 */
char gbsbl[9]; /* 848 to 856 */
char gbsblt; /* 857 to 857 */
} KEY6_F0902, FAR *LPKEY6_F0902;
--------------------------------------------------

Then you can be in trouble because if you don't pass a value to CRCD but pass a value to SBL, you don't know how OneWorld will translate that.

Christian Audet
 
Re: *** QUIZ *** F0911

Chris,

I comming back on your post because I forgot to take in consideration the SUB field in your example. In your example you talk about the SUB field and this field is part of many key and is rarely at the last position, in this case to prevent having the same issue that I got, I sugest you pass-in blank to SUB for the update or create another index that don't include the SUB field. As soon as you "skip" field from the index during a update you never know how OneWorld will translate it to SQL.

I even sugest you run your actual update using the jdedebug.log activated and take a look at the generated SQL statement. Maybe it's wrong and you didn't even notice.

Christian
 
Back
Top