pl/sql Procedure

kn_sivaraman

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

687042

hi

could you write at least what this procedure should do?

Regards,
Jarek

Hoek

(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

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

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

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

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