DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Hierachy and Sequence In One SELECT

  1. #1
    Join Date
    Mar 2006
    Posts
    1

    Question Hierachy and Sequence In One SELECT

    User has two tables:

    Table: V_STATUS
    V_CODE V_SEQ
    X1 1
    X2 2
    X3 3
    X4 4

    Table: T_PERIOD
    T_CODE T_DATE
    2006Q1 01-JAN-2006
    2006Q2 01-APR-2006
    2006Q3 01-JUL-2006
    2006Q4 01-OCT-2006
    2007Q1 01-JAN-2007
    2007Q2 01-JAN-2008

    User, when creating a new vendor, wants to assign a sequence of statuses to a sequence of periods and have such return in one statement.

    So if the user is creating vendor X for period 2006Q2 the statement should return:

    X1 2006Q2
    X2 2006Q3
    X3 2006Q4
    X4 2007Q1

    But if the user is creating vendor Z for period 2006Q3 the statement should return:

    X1 2006Q3
    X2 2006Q4
    X3 2007Q1
    X4 2007Q2

    The number of records returned should always be equal to the number of records in V_STATUS (can assume period table extends for many periods).

    Can such be returned in one SQL statement?

  2. #2
    Join Date
    Nov 2001
    Location
    The Netherlands
    Posts
    19
    How about:
    Code:
    SQL> var period varchar2(6)
    SQL> exec :period :='2006Q3';
    
    PL/SQL-procedure is geslaagd.
    
    SQL> select *
      2  from   (select t_code,t_date,rownum rn
      3          from t_period
      4           where t_code>=:period) tp
      5  ,      v_status vs
      6  where  vs.v_seq=tp.rn
      7  /
    
    T_CODE T_DATE           RN V_COD      V_SEQ
    ------ -------- ---------- ----- ----------
    2006Q3 01-07-06          1 X1             1
    2006Q4 01-10-06          2 X2             2
    2007Q1 01-01-07          3 X3             3
    2007Q2 01-04-07          4 X4             4
    
    SQL> exec :period :='2006Q1';
    
    PL/SQL-procedure is geslaagd.
    
    SQL> select *
      2  from   (select t_code,t_date,rownum rn
      3          from t_period
      4           where t_code>=:period) tp
      5  ,      v_status vs
      6  where  vs.v_seq=tp.rn
      7  /
    
    T_CODE T_DATE           RN V_COD      V_SEQ
    ------ -------- ---------- ----- ----------
    2006Q1 01-01-06          1 X1             1
    2006Q2 01-04-06          2 X2             2
    2006Q3 01-07-06          3 X3             3
    2006Q4 01-10-06          4 X4             4
    
    SQL>
    Regards,

    Arian

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    An analytic function would be a good method here, I should think.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

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


Click Here to Expand Forum to Full Width