Sequence vs Comparison - on Oracle DB

Hi JDEList,

I wouldn'like to open a sub-thread under Oracle vs MS SQL thread, so I open this in a separate thread.

DB platform: Oracle V(???) Standard Installation + XE

The scenario:
=============

Application:
- vanilla P92002 / W92002F Work With Glossary Items form

User Override:
- primary sequencing on Alias

Filtering on System Code QBE column:
- my custom SysCode "5502" on Sytem Code column

Results on the screen:
55020000A
55020000B
:::
:::
550020001
550200002

Adding additional QBE filtering on Data Item (Alias) column:
- ">5502000A"
Results on the screen:
> [55020001 - 55020009] range is missing on the screen

Changeing QBE filtering on Data Item (Alias) column:
- "<5502000A"
Results on the screen:
> Now I get [55020001 - 55020009] range of my custom Error Messages Data Items

I hope, do you see, what is the problem for me:

55020001 is listed AFTER 5502000A in sequencing on alias, but filtering on "greater than 5502000A" I do NOT hit 55020001 in the list, but I DO, when I change criteria to "less than 5502000A"

It is a paradox for me - do you agree???

As far as I know (keep in mind, I am not a CNC guy), there is something Collation Table in Oracle, which can drive this behavior.

My questions:
Q1.) Is there any vanilla Collation Table, which can solve this paradox?
Q2.) If yes, how can we apply this on a live Oracle database - and what kind of issues can arise via this procedure?
Q3.) Any other hint, adsvise, info, etc.?

Thanks in advance & Regards,

Zoltán

Zoltán,

Unfortunately, I don't have any of the answers to your questions.

However I would be keenly interested if you could find the time to produc and post the SQL statements from the debug log for each scenario. One thing I am interested to see is if the sequencing is part of the SQL statements.

The "normal" sequence order is letters after numbers - this is what Windows does and what I believe Oracle or SQL will do. Hence, the QBE behaviour is normal.

AS/400 (surprise!) falls ot of the line with the weird order you reported to have observed in UTB.

I second Peter's interest in the SQL used. If it looks normal, try it outside of JDE to check the order - it's possible that JDE re-sorts the data to match the AS/400 sorting (World was originally developed for it, after all).

The normal ASCII sequence is numbers then letters.



The normal EBCDIC sequence is letters then numbers.



The AS400 uses EBCDIC.



Ellen Deak

Senior Systems Developer

Cooper Standard Automotive

JDE EnterpriseOne (OneWorld XE Update 7 SP23_M1), AS400 DB2 V5R3M0,
Citrix Clients

Hi Peter, Alex, Ellen,

I know, that the "normal" sequence order is letters after numbers.
It was OK, still I haven't started on systems with Oracle DB, where the sequence is numbers after letter, but the comparison works as letters after number.

Peter, Alex,

Excuse me, but I am in large project more weeks ago and will be for more weeks too - so currently I can not investigate the SQL statements, by the way I am not an expert in SQL tools.

Maybe somebody, who faced this issue too, will do that. If yes, I will highly appreciate it. Excuse me again.

Do you think, does this issue more fit to the Technical/CNC forum?

Regards,

Zoltán