Home » SQL & PL/SQL » SQL & PL/SQL » counting records in text file using utl_file
counting records in text file using utl_file [message #292839] Thu, 10 January 2008 00:36 Go to next message
ridhi_sundar
Messages: 184
Registered: November 2007
Location: Bangalore
Senior Member
Hi

i have an input file as below

0010 asp
0020 fdp
0030 hfg
0030 hfg

0040 fdh
0050 dfh
0010 sjh
0020 hjd
0030 feh
0030 jhj
0030 seb

0040 jnj
0050 njj

each group starts with 0010 & ends with 0050.
I have to count the no. of 0030 records in each group.

Here my output should be
1~2
2~3

mean in the 1st group the number of records starting with 0030 is 2 & that in 2nd group is 3

Please give a solution to count them..

[Updated on: Thu, 10 January 2008 00:37]

Report message to a moderator

Re: counting records in text file using utl_file [message #292843 is a reply to message #292839] Thu, 10 January 2008 00:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use external table and then SQL.

Also, please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

Regards
Michel
Re: counting records in text file using utl_file [message #292876 is a reply to message #292839] Thu, 10 January 2008 01:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I was posting a solution when I saw that:
1/ We already asked you to format your post
2/ We answered to your questions and give solutions and you never gave any feedback

So finally, I decide to not post the solution.

Regards
Michel
Re: counting records in text file using utl_file [message #292915 is a reply to message #292876] Thu, 10 January 2008 03:02 Go to previous messageGo to next message
ridhi_sundar
Messages: 184
Registered: November 2007
Location: Bangalore
Senior Member
HI
I am extreamly sorry. Your replies really helped me a lot.

I promise i wont repeat the mistake again.

Please help me.Your help is essential.
Re: counting records in text file using utl_file [message #292919 is a reply to message #292915] Thu, 10 January 2008 03:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create table t_ext (
  2    seq integer,
  3    id  varchar2(4),
  4    val varchar2(3)
  5    )
  6  organization external (
  7    type oracle_loader
  8    default directory TMP_DIR
  9    access parameters (
 10  --    records delimited by X'0A'
 11      records delimited by newline
 12      nobadfile
 13      nologfile
 14      nodiscardfile
 15      fields terminated by ' '
 16      missing field values are null
 17      (seq recnum, id, val)
 18    )
 19    location ('data.txt')
 20  )
 21  reject limit unlimited
 22  /

Table created.

SQL> select * from t_ext;
       SEQ ID   VAL
---------- ---- ---
         1 0010 asp
         2 0020 fdp
         3 0030 hfg
         4 0030 hfg
         5 0040 fdh
         6 0050 dfh
         7 0010 sjh
         8 0020 hjd
         9 0030 feh
        10 0030 jhj
        11 0030 seb
        12 0040 jnj
        13 0050 njj

13 rows selected.

SQL> with
  2    step1 as (
  3      select seq, 
  4             decode(nvl(lag(id) over(order by seq),'0050'), '0050', seq) grp,
  5             id, val
  6      from t_ext
  7    ),
  8    step2 as (
  9      select max(grp) over (order by seq) grp, id, val
 10      from step1
 11    )
 12  select dense_rank() over (order by grp) grp,
 13         count(decode(id,'0030',1)) cnt
 14  from step2
 15  group by grp
 16  /
       GRP        CNT
---------- ----------
         1          2
         2          3

2 rows selected.

Regards
Michel
Re: counting records in text file using utl_file [message #292926 is a reply to message #292919] Thu, 10 January 2008 03:18 Go to previous messageGo to next message
ridhi_sundar
Messages: 184
Registered: November 2007
Location: Bangalore
Senior Member
cant it be done with out creating a table?

Regards
Re: counting records in text file using utl_file [message #292928 is a reply to message #292926] Thu, 10 January 2008 03:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This does not create a table, that is it does not allocate space, it creates a pointer to the file defining the format of each line.

Regards
Michel
Re: counting records in text file using utl_file [message #292953 is a reply to message #292839] Thu, 10 January 2008 04:24 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

But i could give some unexpected output some times .

SQL> select * from t_ext order by 1;

       SEQ ID    VAL
---------- ----- -----
         0 0010  p
         1 0010  b
         2 0020  c
         3 0030  d
         4 0030  e
         5 0040  f
         6 0050  g
         7 0050  n
         8 0010  h
         9 0020  a
        10 0020  x
        11 0030  i
        12 0030  j
        13 0030  k
        14 0040  l
        15 0040  m
        16 0050  o
        17 0050  q

18 rows selected.

SQL> with
  2      step1 as (
  3        select seq, id, lag(id) over(order by seq) ls ,
  4               decode(nvl(lag(id) over(order by seq),'0050'), '0050', seq) gr
p,
  5                val
  6        from t_ext
  7      ),
  8      step2 as (
  9        select max(grp) over (order by seq) grp, id, val
 10        from step1
 11      )
 12    select dense_rank() over (order by grp) grp,
 13           count(decode(id,'0030',1)) cnt
 14    from step2
 15    group by grp;

       GRP        CNT
---------- ----------
         1          2
         2          0
         3          3
         4          0

SQL>


Let me change the code slighly (with your permission ) Smile

SQL> with
  2    step1 as (
  3      select seq, id, val ,
  4              nvl(lag(id) over(order by seq),'0050') lag1
  5       from t_ext ),
  6    step2 as (
  7      select id, val ,
  8             max( DECODE(id,'0010',DECODE(lag1,'0050',seq))) over (order by s
eq) grp
  9      from step1)
 10    Select dense_rank() over (order by grp) grp,
 11          count(decode(id,'0030',1)) cnt
 12    from step2
 13    group by grp;

       GRP        CNT
---------- ----------
         1          2
         2          3

SQL>


Now it may be adaptable to any condition provided .

Quote:

each group starts with 0010 & ends with 0050.


Thumbs Up
Rajuvan.

[Updated on: Thu, 10 January 2008 04:25]

Report message to a moderator

Re: counting records in text file using utl_file [message #292954 is a reply to message #292953] Thu, 10 January 2008 04:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I assume groups are valid ones and only check the end value.
If data are not valid, anything can be done and I don't see why your result is better than mine.

Regards
Michel
Re: counting records in text file using utl_file [message #292964 is a reply to message #292839] Thu, 10 January 2008 05:26 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Michel ,

Quote:

I don't see why your result is better than mine.

I didn't even claim like that , didn't even think about it .
Even my query ( Base is yours) may go on toss with some other scenario.

Thumbs Up
Rajuvan
Previous Topic: insert
Next Topic: plzz help
Goto Forum:
  


Current Time: Tue Dec 06 14:31:41 CST 2016

Total time taken to generate the page: 0.09805 seconds