-
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?
-
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
-
An analytic function would be a good method here, I should think.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|