Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2003
    Posts
    12

    Question Unanswered: Query Problem On Russian OS

    Hello EveryOne:

    Below is the Query which when I Execute on the Windows 2000 English or Japanese Version will give the Correct Results But on the Windows 2000 Russian OS it is giving incorrect output. The Query is:

    SELECT ST.PNUM,PM.PNAM FROM Z01011 PM,(SELECT * FROM Z01012 ORDER BY MAKE,SPOI,PNUM) ST
    WHERE ST.PNUM = PM.RPNU and RowNum < 101 and ST.PNUM>='0004'

    Now lets suppose Data present with Unqiue ID (PNUM) is like:
    0001
    0002
    ABC
    DEF
    0003
    0004
    FGH
    IJK
    0005
    0006

    On English & Japanese OS the output is in sequence: 0004,0005,0006

    But on Russian OS the output is in Sequence: 0001,0002,ABC,DEF,0003 ................

    So anybody have idea that why this Query behaving incorrectly on the Russain OS.

    Regards
    Khalid

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    It is probably because on the English and Japanese OS Oracle has a binary sort linguistic, whereas on the Russian one a latin or any other variant.

    Try using:
    Code:
    alter session set nls_sort = 'binary';
    Example
    Code:
    SQL@8i> alter session set nls_sort = 'binary';
    
    Session altered.
    
    SQL@8i> select * from t order by a;
    
    A
    ---------------
    0001
    0002
    0003
    0004
    0005
    0006
    ABC
    DEF
    FGH
    IJK
    
    10 rows selected.
    
    SQL@8i> alter session set nls_sort = 'latin';
    
    Session altered.
    
    SQL@8i> select * from t order by a;
    
    A
    ---------------
    ABC
    DEF
    FGH
    IJK
    0001
    0002
    0003
    0004
    0005
    0006
    
    10 rows selected.
    
    SQL@8i>
    If you happen to be on 9i or above, you can also use the NLSSORT function directly in the query, avoiding changing session's parameter.

  3. #3
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Whilst your inline view is sorted, the entire query isn't. It is currently just a matter of luck as to how they are returned.

    Add an order by to the entire query. JMartinez's reply may also apply - I don't know what the default sort is on Japanese / Russian.

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  4. #4
    Join Date
    Jun 2003
    Posts
    12

    Thumbs up

    Hello,

    Thanks Martinez for your reply. As I am using Oracle 9i so I have use the NLSSORT function in my Query, and now correct results are coming on the Russian OS as well. So Thanks a Lot for your help.

    Regards
    Khalid

  5. #5
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Clearly there are differences in sorting between Russian etc. However, you still need an outer order by. Without it, you are getting the correct by results by luck and these could change as time goes on.

    Code:
    select j.job_number, j.job_status, js.code 
    from   h_job_waiting j, 
           (select code, description from h_job_status order by code) js
    where  js.code = j.job_status
    
    JOB_NUMBER  JOB_STATUS CODE
    ==========  ========== ====
    UKD/214858  N          N
    UKD/214859  O          O
    UKD/214860  N          N
    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •