Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2002
    Location
    India
    Posts
    40

    Unanswered: How best we can in PL/SQL

    Hi all

    I have following Query ...this is all done in .sql file
    now i want this code , move to PL/SQL
    So can any one suggest me what is the best way i can follow
    ..instead of this can i use any other techniq...to get the result

    All suggestions are welcome

    Select
    DECODE(arrival_dt-TRUNC(sysdate),
    GREATEST(LEAST(arrival_dt-TRUNC(sysdate),-1),-99999),
    'Over Due',
    DECODE(arrival_dt-TRUNC(sysdate),
    GREATEST(LEAST(arrival_dt-TRUNC(sysdate),7),0),
    '1 Week',
    DECODE(arrival_dt-TRUNC(sysdate),
    GREATEST(LEAST(arrival_dt-TRUNC(sysdate),14),8),
    '2 Weeks',
    DECODE(arrival_dt-TRUNC(sysdate),
    GREATEST(LEAST(arrival_dt-TRUNC(sysdate),21),15),
    '3 Weeks',
    DECODE(arrival_dt-TRUNC(sysdate),
    GREATEST(LEAST(arrival_dt-TRUNC(sysdate),28),22),
    '4 Weeks',
    DECODE(arrival_dt-TRUNC(sysdate),
    GREATEST(LEAST(arrival_dt-TRUNC(sysdate),35),29),
    '5 Weeks',
    DECODE(arrival_dt-TRUNC(sysdate),
    GREATEST(LEAST(arrival_dt-TRUNC(sysdate),42),36),
    '6 Weeks',
    DECODE(arrival_dt-TRUNC(sysdate),
    GREATEST(LEAST(arrival_dt-TRUNC(sysdate),49),43),
    '7 Weeks',
    DECODE(arrival_dt-TRUNC(sysdate),
    GREATEST(LEAST(arrival_dt-TRUNC(sysdate),56),50),
    '8 Weeks',
    DECODE(arrival_dt-TRUNC(sysdate),
    GREATEST(LEAST(arrival_dt-TRUNC(sysdate),63),57),
    '9 Weeks',
    DECODE(arrival_dt-TRUNC(sysdate),
    GREATEST(LEAST(arrival_dt-TRUNC(sysdate),70),64),
    '10 Weeks',
    DECODE(arrival_dt-TRUNC(sysdate),
    GREATEST(LEAST(arrival_dt-TRUNC(sysdate),77),71),
    '11 Weeks',
    DECODE(arrival_dt-TRUNC(sysdate),
    GREATEST(LEAST(arrival_dt-TRUNC(sysdate),84),78),
    '12 Weeks',
    '+12 Weeks'))))))))))))) --PO bucket
    From po
    GROUP BY DECODE(arrival_dt-TRUNC(sysdate),
    GREATEST(LEAST(arrival_dt-TRUNC(sysdate),-1),-99999),
    'Over Due',
    DECODE(arrival_dt-TRUNC(sysdate),
    GREATEST(LEAST(arrival_dt-TRUNC(sysdate),7),0),
    '1 Week',
    DECODE(arrival_dt-TRUNC(sysdate),
    GREATEST(LEAST(arrival_dt-TRUNC(sysdate),14),8),
    '2 Weeks',
    DECODE(arrival_dt-TRUNC(sysdate),
    GREATEST(LEAST(arrival_dt-TRUNC(sysdate),21),15),
    '3 Weeks',
    DECODE(arrival_dt-TRUNC(sysdate),
    GREATEST(LEAST(arrival_dt-TRUNC(sysdate),28),22),
    '4 Weeks',
    DECODE(arrival_dt-TRUNC(sysdate),
    GREATEST(LEAST(arrival_dt-TRUNC(sysdate),35),29),
    '5 Weeks',
    DECODE(arrival_dt-TRUNC(sysdate),
    GREATEST(LEAST(arrival_dt-TRUNC(sysdate),42),36),
    '6 Weeks',
    DECODE(arrival_dt-TRUNC(sysdate),
    GREATEST(LEAST(arrival_dt-TRUNC(sysdate),49),43),
    '7 Weeks',
    DECODE(arrival_dt-TRUNC(sysdate),
    GREATEST(LEAST(arrival_dt-TRUNC(sysdate),56),50),
    '8 Weeks',
    DECODE(arrival_dt-TRUNC(sysdate),
    GREATEST(LEAST(arrival_dt-TRUNC(sysdate),63),57),
    '9 Weeks',
    DECODE(arrival_dt-TRUNC(sysdate),
    GREATEST(LEAST(arrival_dt-TRUNC(sysdate),70),64),
    '10 Weeks',
    DECODE(arrival_dt-TRUNC(sysdate),
    GREATEST(LEAST(arrival_dt-TRUNC(sysdate),77),71),
    '11 Weeks',
    DECODE(arrival_dt-TRUNC(sysdate),
    GREATEST(LEAST(arrival_dt-TRUNC(sysdate),84),78),
    '12 Weeks',
    '+12 Weeks')))))))))))))

    Thanks
    Suryadevara

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

    I'm not sure what you mean by moving it to PL/SQL, but this query should get the same results, and a lot simpler...

    Code:
    select st, count(*) 
    from   (
            select 
               case when trunc(arrival_dt) <= trunc( sysdate ) then 'Overdue' 
               else trunc(((arrival_dt - sysdate))/7)+1||' weeks' end as st 
            from po
            where arrival_dt is not null
           )
    group by st
    You could also use decode instead of the case..when. When using a complex expression for a group by, I prefer to use a subselect purely for readability.

    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.

  3. #3
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Code:
    select decode(sign(arrival_dt- trunc(sysdate))
    		,-1,'Overdue'
    		   ,decode(least(trunc((arrival_dt- trunc(sysdate))/7)+1,12)
    			,12,'Over 12 weeks'
    			    ,trunc((arrival_dt- trunc(sysdate))/7)+1 || ' weeks'
    			  )
    	     )
    How about that?

    Regards

  4. #4
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    When posting code, could you please specify in plain English what the code is supposed to do?

    Sometimes, the code does not do what the developer intended it to.

    If you do not mention the purpose of the code, people will interpret the code and try to fix whatever problem the code is trying to solve, rather than the actual problem you want to solve.
    Ravi

Posting Permissions

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