Forum Stats

  • 3,839,951 Users
  • 2,262,553 Discussions
  • 7,901,102 Comments

Discussions

pl/sql Procedure

kn_sivaraman
kn_sivaraman Member Posts: 79
edited Jan 19, 2010 6:40AM in SQL & PL/SQL
HI,

i have table like this.

CREATE TABLE pr_temp(pr_no NUMBER,f_flg NUMBER(1));


INSERT INTO pr_temp VALUES(11,0);
INSERT INTO pr_temp VALUES(12,1);
INSERT INTO pr_temp VALUES(13,0);
INSERT INTO pr_temp VALUES(14,0);
INSERT INTO pr_temp VALUES(15,0);
INSERT INTO pr_temp VALUES(16,1);
INSERT INTO pr_temp VALUES(17,0);
INSERT INTO pr_temp VALUES(18,0);
INSERT INTO pr_temp VALUES(19,1);
INSERT INTO pr_temp VALUES(20,0);
INSERT INTO pr_temp VALUES(21,0);
INSERT INTO pr_temp VALUES(22,0);
INSERT INTO pr_temp VALUES(23,1);
INSERT INTO pr_temp VALUES(24,1);
INSERT INTO pr_temp VALUES(25,1);


0-success
1-failure


SELECT * FROM pr_temp WHERE f_flg=0 ORDER BY pr_no;

output FOR success;
-------------------
11-15;17-18;20-22;

(ie)11 TO 15 datas are success records,.....

SELECT * FROM pr_temp WHERE f_flg=1 ORDER BY pr_no;

output FOR failure;
------------------
12;16;19;23-25;

(ie)23 TO 25 datas are failure records,.....


i need a pl/SQL PROCEDURE.Please help me.


by
siva

Answers

  • 687042
    687042 Member Posts: 17
    hi

    could you write at least what this procedure should do?

    Regards,
    Jarek
  • Hoek
    Hoek Member Posts: 16,087 Gold Crown
    edited Dec 30, 2009 7:03AM
    (ie)11 TO 15 datas are success records,.....
    ¿What about 12 then?

    Shouldn't it be:

    11;13-15;17-18;20-22; instead?

    edit
    I think you could use Aketi's 'Tabibitosan-method' here.
    See these examples: 1007478

    Edited by: hoek on Dec 30, 2009 1:02 PM
  • 730428
    730428 Member Posts: 2,087
    Hoek is right, the o/p you posted is not coherent wiht input and rules. Anyway this should solve:
    SQL> with t as (
      2  select F_FLG, decode(min(min),max,to_char(max),min(min)||'-'||max) range,
      3  row_number() over(partition by f_flg order by max) rn from (
      4  select F_FLG, connect_by_root pr_no min, pr_no max
      5  from pr_temp
      6  where connect_by_isleaf=1
      7  connect by pr_no = prior pr_no+1 and F_FLG = prior f_flg)
      8  group by F_FLG, max)
      9  select F_FLG, ltrim(sys_connect_by_path(range,';'),';')  ranges
     10  from t 
     11  where connect_by_isleaf=1
     12  connect by rn = prior rn+1 and f_flg=prior f_flg
     13  start with rn=1;
    
         F_FLG RANGES
    ---------- ------------------------------
             0 11;13-15;17-18;20-22
             1 12;16;19;23-25
    Max
    [My Italian Oracle blog|http://oracleitalia.wordpress.com/2009/12/29/estrarre-i-dati-in-formato-xml-da-sql/]
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,265 Red Diamond
    edited Dec 31, 2009 12:09PM
    Hi, Siva,

    See the link Hoek supplied for how to grou the rows into sets witht the same f_flg and consecutive pr_nos.
    Once you have them assigned to groups, use GROUP BY to get the lowest and highest in each group, and use your favorite string aggregation technique to get the results from all groups in a single string.
    For example:
    VARIABLE	target_f_flg	NUMBER
    EXEC  :target_f_flg := 1;
    
    WITH	got_grp_num	AS
    (
    	SELECT	  pr_no
    	,	  pr_no - ROW_NUMBER () OVER (ORDER BY pr_no)	AS grp_num
    	FROM	  pr_temp
    	WHERE	  f_flg	= :target_f_flg
    )
    ,	got_agg		AS
    (
    	SELECT	  MIN (pr_no)	AS min_pr_no
    	,	  MAX (pr_no)	AS max_pr_no
    	,	  ROW_NUMBER () OVER (ORDER BY grp_num)
    				AS grp_seq
    	FROM	  got_grp_num
    	GROUP BY  grp_num
    )
    SELECT	LTRIM ( SYS_CONNECT_BY_PATH ( min_pr_no || CASE
    							WHEN  min_pr_no != max_pr_no
    							THEN  '-' || max_pr_no
    						   END
    				    , ';'
    				    )
    	      , ';'
    	      )		AS result_txt
    FROM	got_agg
    WHERE	CONNECT_BY_ISLEAF	= 1
    START WITH	grp_seq	= 1
    CONNECT BY	grp_seq	= PRIOR grp_seq + 1
    ;
    In Express Edition 10.2.0.1.0, the CONNECT BY is often stopping after 2 levels.
    I'll test n another version as soon as I can.

    Edited by: Frank Kulash on Dec 31, 2009 12:08 PM
    Works fine in Enterprise Edition 11.
  • Hoek
    Hoek Member Posts: 16,087 Gold Crown
    Another one, just for fun:
    SQL> select ltrim(sys_connect_by_path(pr_no, ';'), ';')||';' pr_no
      2  ,      f_flg
      3  from ( select f_flg
      4         ,      case
      5                  when min(pr_no) = max(pr_no)
      6                  then to_char(min(pr_no))
      7                  else min(pr_no)||'-'||max(pr_no)
      8               end pr_no
      9         ,    row_number() over ( partition by f_flg order by min(pr_no) ) rn
     10         from ( 
     11                select f_flg 
     12                ,      pr_no
     13                ,      pr_no-row_number() over( partition by f_flg order by f_flg, pr_no ) rn    
              
     14                from ( select pr_no
     15                       ,      f_flg
     16                       from   pr_temp
     17                       order  by f_flg, pr_no
     18                     )
     19              )
     20         group by f_flg, rn
     21         order by f_flg
     22       )
     23  where connect_by_isleaf=1
     24  start with rn=1     
     25  connect by rn = prior rn+1     
     26         and f_flg = prior f_flg;
    
    PR_NO                          F_FLG
    ------------------------- ----------
    11;13-15;17-18;20-22;              0
    12;16;19;23-25;                    1
    What DB-version are you on?
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    I like Tabibitosan method B-)
    col str for a35
    
    select f_flg,substr(sys_connect_by_path(str,';'),2) as str
    from (select f_flg,Row_Number() over(partition by f_flg order by dis) as rn,
          case count(*)
          when 1 then min(to_char(pr_no))
          else min(to_char(pr_no)) || '-' || max(to_char(pr_no)) end as str
            from (select pr_no,f_flg,
                  pr_no-Row_Number() over(partition by f_flg order by pr_no) as dis
                  from pr_temp)
          group by f_flg,dis)
    where connect_by_IsLeaf = 1
    start with rn=1
    connect by prior f_flg = f_flg
          and  prior rn = rn-1;
    
    F_FLG  STR
    -----  --------------------
        0  11;13-15;17-18;20-22
        1  12;16;19;23-25
This discussion has been closed.