Home » SQL & PL/SQL » SQL & PL/SQL » Dynamically inserting records in output based on some conditions (Oracle 10g, HP-UX)
Dynamically inserting records in output based on some conditions [message #318387] |
Tue, 06 May 2008 13:54 |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
Hello,
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for HPUX: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production
$ uname -a
HP-UX o2hp71 B.11.11 U 9000/800 1595853211
SQL> l
1 with t
2 as
3 (
4 select 1 element_no,'header 1' text,'text 1' value from dual union all
5 select 2,'header 2', 'text 2' from dual union all
6 select 3,'header 3', 'text 3' from dual union all
7 select 5,'header 4', 'text 4' from dual union all
8 select 12,'header 5','text 5' from dual union all
9 select 15,'header 6','text 6' from dual union all
10 select 16,'header 7','text 7' from dual union all
11 select 17,'header 8', null from dual union all
12 select 26,'Main record','main 1' from dual union all
13 select 28,'OptionSelected Option:- 1', null from dual union all
14 select 34,'body 1','Successful' from dual union all
15 select 35,'body 2', null from dual union all
16 select 36,'body 3','body 3 text' from dual union all
17 select 37,'body 4','body 4 text' from dual union all
18 select 38,'body 5','body 5 text' from dual union all
19 select 39,'body 6','body 6 text' from dual union all
20 select 40,'body 7','body 7 text' from dual union all
21 select 41,'body 8 Previous','body 8 text' from dual union all
22 select 44,'OptionSelected Option:- 2', null from dual union all
23 select 50,'body 9','body 9 text' from dual union all
24 select 51,'body 10','body 10 text' from dual union all
25 select 52,'body 11','body 11 text' from dual union all
26 select 53,'body 12','body 12 text' from dual union all
27 select 54,'body 13 Previous','body 13 text' from dual union all
28 select 59,'OptionSelected Option:- 1', null from dual union all
29 select 67,'Finished' , 'closed' from dual
30 )
31* select * from t
Output I would like to get to is as follows
ELEMENT_NO NEW_TEXT NEW_VALUE
---------- ------------------------- -------------------------
1 header 1 text 1
2 header 2 text 2
3 header 3 text 3
4 header 4 text 4
5 header 5 text 5
6 header 6 text 6
7 header 7 text 7
8 header 8
9 Main record main 1
10 OptionSelected Option:- 1
11 body 1 Successful
12 body 2
13 body 3 body 3 text
14 body 4 body 4 text
15 body 5 body 5 text
16 body 6 body 6 text
17 body 7 body 7 text
18 body 8 Previous body 8 text
19 header 1 text 1
20 header 2 text 2
21 header 3 text 3
22 header 4 text 4
23 header 5 text 5
24 header 6 text 6
25 header 7 text 7
26 header 8
27 Main record main 1
28 OptionSelected Option:- 2
29 body 9 body 9 text
30 body 10 body 10 text
31 body 11 body 11 text
32 body 12 body 12 text
33 body 13 Previous body 13 text
34 header 1 text 1
35 header 2 text 2
36 header 3 text 3
37 header 4 text 4
38 header 5 text 5
39 header 6 text 6
40 header 7 text 7
41 header 8
42 Main record main 1
43 OptionSelected Option:- 1
44 Finished closed
A brief explanation what I would like to achieve. All I am intertested in some of your ideas/inputs how will you approach
this problem if you have tasked to achieve something like this. The records are demarcated as follows :
a) Text containing header is classified as header record
b) Text containing Main is classified as main record
c) Text containing OptionSelected is the trigger to insert header and main record
d) Text containing finished indicates there are no records after this.
So when we encounter the record containing the text OptionSelected I need to re-insert the header and body record. Simple
as that.
Any thoughts. I am trying to get to a solution using the Model clause, apart from that I cannot think of any other alternative
how to achieve the same using sql.
Your help on this is much appreciated.
Thanks for taking your time in reading this.
Regards
Raj
|
|
|
Goto Forum:
Current Time: Sat Feb 15 12:01:39 CST 2025
|