Re: How to query ...?

On Jan 30, 6:52 pm, Maxim Demenko <mdeme..._at_arcor.de> wrote:
> ganesh bora schrieb:
>
>
>
> > hi if it helps ...??
>
> > create or replace procedure numbers is
> > i number :=1;
> > j number :=1;
> > flag number :=5;
> > begin
> > loop
> > for j in 1..flag
> > loop
> > dbms_output.put(i);
> > i:=i+1;
> > end loop;
> > if flag = 5 then
> > flag :=3;
> > else flag :=5;
> > end if;
> > dbms_output.put('*');
> > dbms_output.new_line;
> > exit when (i=1000);
> > end loop;
> > end ;
>
> > On Jan 30, 2:40 pm, Jeremy <jeremy0..._at_gmail.com> wrote:
> >> In article <1170138729.917043.271..._at_p10g2000cwp.googlegroups.com>,
> >> Fhatoy says...
>
> >>> Dear all,
> >>> How can I write the number from 1 to 1000 on the screen like the
> >>> following in Oracle ?
> >>> 12345*678*910111213*141516*1718192021*....
> >>> Please help me...What are the * supposed to represent?
> >> --
> >> jeremy
>
> Can't see a reason to resort to pl sql.
>
> WITH t AS (
> SELECT decode(mod(ROWNUM,8),0,ROWNUM||'*',5,ROWNUM||'*',ROWNUM) val
> ,ROWNUM rn, ROWNUM -1 prev
> FROM dual
> CONNECT BY LEVEL <= 1000),
> t1 AS (
> SELECT replace(sys_connect_by_path(val,' '),' ') part1
> FROM (SELECT * FROM T
> WHERE rn <= 500)
> CONNECT BY PRIOR rn=prev
> START WITH rn=1
> ),
> t2 AS (
> SELECT replace(sys_connect_by_path(val,' '),' ') part2
> FROM (SELECT * FROM T
> WHERE rn > 500)
> CONNECT BY PRIOR rn=prev
> START WITH rn=501
> )
> SELECT (MAX(part1))||MAX(part2) silly_string
> FROM t1,t2;
>
> Best regards
>
> Maxim

Thanks very much...

it's a perfect code... Received on Wed Jan 31 2007 - 03:17:58 CST