Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2011
    Posts
    13

    Unanswered: help on 'order by decode'

    I have the following query
    Code:
    SELECT distinct prc.prc_typ ,typ.prc_srt
    from prmpricest prc,prmprctypt typ
    WHERE  prc.pub_idt     = 123
    AND    prc.pub_vol_idt = 1
    and prc.prc_typ=typ.prc_typ order by typ.prc_srt desc
    the result returns,
    Code:
    prc_typ  prc_srt
    -------   ------
     PRP         2 
     INS         1

    Now since i want to display only the prc_typ column i changed my query as follows,
    [CODE] select distinct a.IDT from (SELECT distinct prc.prc_typ IDT,typ.prc_srt
    from prmpricest prc,prmprctypt typ
    WHERE prc.pub_idt = 336
    AND prc.pub_vol_idt = 1
    and prc.prc_typ=typ.prc_typ order by typ.prc_srt desc)a [CODE]

    Now the result will be ,
    Code:
     IDT
    -------   
     PRP         
     INS
    On top of this a constraint has to be added.Suppose in the result(IDT) fetched above ,if there is a value of 'PRI' in the list of values,then PRI should be returned first,then the remaining values should be returned as per the desc order given in the query.
    I changed my query as follows,(by using 'order by decode')

    Code:
    select distinct a.IDT from (SELECT distinct prc.prc_typ IDT,typ.prc_srt
    from prmpricest prc,prmprctypt typ
    WHERE  prc.pub_idt     = 336
    AND    prc.pub_vol_idt = 1
    and prc.prc_typ=typ.prc_typ order by typ.prc_srt desc)a
    ORDER  BY DECODE(a.IDT,'PRI',0,1)
    But the result returning is not correct.Since the 'PRI' value is not been fetched in the above query,it should return the result as the original result that is as follows,
    Code:
    IDT
    ---
    PRP
    INS
    But after placing 'order by decode' the value is returning in alphabetical order
    though 'PRI' is not in the list of values
    i.e
    Code:
    IDT
                   ---
                  INS
                  PRP

    Please help me on this..

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Perhaps you should change the principle.

    Obviously, if IDT = 'PRI', it should be first in the list. All other IDTs should be ordered by PRC_SRT in descending order. It means that, basically, it doesn't matter what PRC_SRT belongs to IDT = 'PRI' as you always want it to be on the top of the list.

    Therefore, you could do something like this: set PRI's PRC_SRT value to an enormously high value (i.e. the one you suppose can't normally be reached):
    Code:
    SQL> with
      2    test as
      3      (      select 'PRP' idt, 2 prc_srt from dual
      4       union select 'INS' idt, 1 prc_srt from dual
      5       union select 'PRI' idt, 0 prc_srt from dual
      6      ),
      7    test_1 as
      8      (select idt, decode(idt, 'PRI', 1E99, prc_srt) srt       --> let PRC_SRT = 1E99 if ITD = 'PRI'
      9       from test
     10      )
     11    select idt
     12    from test_1
     13    order by srt desc;
    
    IDT
    ---
    PRI
    PRP
    INS
    What happens if there's no 'PRI' (nothing happens, it still works OK):
    Code:
    SQL> with
      2    test as
      3      (      select 'PRP' idt, 2 prc_srt from dual
      4       union select 'INS' idt, 1 prc_srt from dual
      5       -- union select 'PRI' idt, 0 prc_srt from dual
      6      ),
      7    test_1 as
      8      (select idt, decode(idt, 'PRI', 1E99, prc_srt) srt
      9       from test
     10      )
     11    select idt
     12    from test_1
     13    order by srt desc;
    
    IDT
    ---
    PRP
    INS
    
    SQL>
    What do you think?

  3. #3
    Join Date
    Mar 2007
    Posts
    623
    Alternatively you may avoid constructing this complex expression and simply order by two columns:
    Code:
    order by DECODE(a.IDT,'PRI',0,1), prc_srt desc
    The first column puts rows with IDT = 'PRI' to the top, the second column will sort in those two groups based on PRC_SRT value.

    Note that any of these approaches will fail with ORA-01791 exception when using DISTINCT keyword in the outer query as it aggregates data only by IDT column. Either it is useless (and you should not use it) or your test case is incomplete (as there are no duplicates in IDT column).

    I wonder why you do not follow your thread on the same topic (http://www.dbforums.com/oracle/16647...c-program.html); anyway, if the DISTINCT is really needed, it is impossible to get correct sorting expression(s) without answering the question I asked there.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    One other thing to note is that the optimizer is probably ignoring the ORDER BY clause in the subquery. Any ordering of the resultset should be done only at the outermost level.

  5. #5
    Join Date
    Mar 2011
    Posts
    13
    Thanks All for ur suggestions and help ... the query is working fine while giving it as follows

    Code:
    select a.IDT from(SELECT  distinct prc.prc_typ IDT,typ.prc_srt
    from prmpricest prc,prmprctypt typ
    WHERE  prc.pub_idt     = 123
    AND    prc.pub_vol_idt = 1
    and TO_CHAR( prc_dat, 'YYYYMMDD' ) = '20100101'
    and prc.prc_typ=typ.prc_typ)a 
    order by DECODE(a.IDT,'PRI',0,1),a.prc_srt desc
    But in DB level this query works.Wen i put the same query in an embedded SQL programming language-PRO*c as follows
    Code:
    static char prc_typ_all[]="\
    SELECT a.IDT\n\
    FROM(SELECT  distinct prc.prc_typ IDT,typ.prc_srt\n\
    from prmpricest prc,prmprctypt typ\n\
    WHERE  prc.pub_idt     = %d\n\
    AND    prc.pub_vol_idt = %d\n\
    AND    prc.vol_prt_idt = %d\n\
    AND    prc.edn_idt = %d\n\
    AND    TO_CHAR( prc.prc_dat, 'YYYYMMDD' ) <= %s\n\
    AND prc.prc_typ=typ.prc_typ) a \n\
    %s\n\
    ORDER  BY a.prc_srt DESC, DECODE(a.IDT,'PRI',0,1)\
    ";
    it throws an error
    4-E- S(tructured) Q(uery) L(anguage) error <program name>
    -936 ORA-00936: missing expression
    When i remove TO_CHAR function it executes properly.

    The to_char function was working fine before when the code in pro*c program was as follows

    Code:
    static char prc_typ_all[]="\
    SELECT DISTINCT prc_typ\n\
    FROM   prmpricest\n\
    WHERE  pub_idt     = %d\n\
    AND    pub_vol_idt = %d\n\
    AND    vol_prt_idt = %d\n\
    AND    edn_idt = %d\n\
    %s\n\
    AND    TO_CHAR( prc_dat, 'YYYYMMDD' ) <= %s\n\
    ORDER  BY DECODE(prc_typ,'PRI',0,1)\
    ";
    but after placing the new query,the addition of to_char function.. throws error.Any help on this??

  6. #6
    Join Date
    Mar 2011
    Posts
    13
    Sorry forgot to post the new query in PRO*C prog

    static char prc_typ_all[]="\
    SELECT a.IDT\n\
    FROM(SELECT distinct prc.prc_typ IDT,typ.prc_srt\n\
    from prmpricest prc,prmprctypt typ\n\
    WHERE prc.pub_idt = %d\n\
    AND prc.pub_vol_idt = %d\n\
    AND prc.vol_prt_idt = %d\n\
    AND prc.edn_idt = %d\n\
    AND TO_CHAR( prc_dat, 'YYYYMMDD' ) <= %s\n\
    AND prc.prc_typ=typ.prc_typ\n\
    ORDER BY typ.prc_srt DESC, DECODE(prc.prc_typ,'PRI',0,1)) a \n\
    %s";

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Do the single quotes need to be escapes at all?

Posting Permissions

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