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

Previous Topic: Removing duplicates ?
Next Topic: Trigger problem with nested query
Goto Forum:
  


Current Time: Sat Feb 15 12:01:39 CST 2025