Home » RDBMS Server » Server Utilities » partitioned counter values
partitioned counter values [message #195909] Tue, 03 October 2006 02:22 Go to next message
srraajesh
Messages: 63
Registered: May 2005
Member
Hi,

I have a table like this

DeptID PrimaryKey
DeptName
PersonName
Counter

Now, in my CSV file, I have the values for DeptID, DeptName, PersonName.

I need to set the value for counter, that is partitioned by Dept Name. For ex

Dept ID Dept Name PersonName Counter
1 A A1 1
2 A A2 2
3 B B1 1
4 B B2 2

How to get this done in the control file itself.

Can Analytic functions be used for the same..

Please let me know.

Thanks.
srr
Re: partitioned counter values [message #196020 is a reply to message #195909] Tue, 03 October 2006 14:06 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
I don't think there is any way that you can specify that in a control file. You could easily update the column using an analytic function after the load. Howver, it is generally considered a waste of space and therefore bad practice to unnecessarily store information in a column that can be selected based on other existing data.
Re: partitioned counter values [message #196023 is a reply to message #196020] Tue, 03 October 2006 14:14 Go to previous messageGo to next message
srraajesh
Messages: 63
Registered: May 2005
Member
Hi,

Thanks for your reply..

Could you give me an example for such an update after the loading process is complete?

Regards
Raajesh
Re: partitioned counter values [message #196027 is a reply to message #196023] Tue, 03 October 2006 14:40 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
Actually, there would be a way in a control file (if this was a philosophical question as to whether is can be done), but it's even worse than what Barbara says, putting a value into a column when it really should be set at "selection" time.

If you created a function that checks to see if the department name is in the table yet, and then use that value or bump up the counter if not found. You would have to load using BINDSIZE=1 so that the load will commit after every single row (a very bad idea) as such:

.
.
.
colname1 "func1(:colname1)",
.
.
.

[Updated on: Tue, 03 October 2006 14:48]

Report message to a moderator

Re: partitioned counter values [message #196030 is a reply to message #196023] Tue, 03 October 2006 14:47 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
-- If you have a table like this:
SCOTT@10gXE> SELECT * FROM your_table
  2  /

    DEPTID DEPTNAME PERSONNAME
---------- -------- ----------
         1 A        A1
         2 A        A2
         3 B        B1
         4 B        B2


-- You can just select the information you want:
SCOTT@10gXE> SELECT deptid, deptname, personname,
  2  	    ROW_NUMBER () OVER
  3  	      (PARTITION BY deptname
  4  	       ORDER BY deptid, personname)
  5  	      AS counter
  6  FROM   your_table
  7  /

    DEPTID DEPTNAME PERSONNAME    COUNTER
---------- -------- ---------- ----------
         1 A        A1                  1
         2 A        A2                  2
         3 B        B1                  1
         4 B        B2                  2


-- If you want to unnecessarily perform the steps to
-- add a column and update it, you can do it like so:
SCOTT@10gXE> ALTER TABLE your_table ADD (counter NUMBER)
  2  /

Table altered.

SCOTT@10gXE> UPDATE your_table yt1
  2  SET    counter =
  3  	    (SELECT counter
  4  	     FROM   (SELECT deptid, deptname, personname,
  5  			    ROW_NUMBER () OVER
  6  			      (PARTITION BY deptname
  7  			       ORDER BY deptid, personname)
  8  			      AS counter
  9  		     FROM   your_table yt2) yt3
 10  	     WHERE  yt1.deptid	   = yt3.deptid
 11  	     AND    yt1.deptname   = yt3.deptname
 12  	     AND    yt1.personname = yt3.personname)
 13  /

4 rows updated.

SCOTT@10gXE> SELECT * FROM your_table
  2  /

    DEPTID DEPTNAME PERSONNAME    COUNTER
---------- -------- ---------- ----------
         1 A        A1                  1
         2 A        A2                  2
         3 B        B1                  1
         4 B        B2                  2

SCOTT@10gXE>

icon6.gif  Re: partitioned counter values [message #196168 is a reply to message #195909] Wed, 04 October 2006 06:57 Go to previous message
srraajesh
Messages: 63
Registered: May 2005
Member
Thanks for the help..

I will try this and get back.

/Raajesh
Previous Topic: How to import data in Oracle
Next Topic: Views invalidated on import why?
Goto Forum:
  


Current Time: Fri Dec 09 04:08:07 CST 2016

Total time taken to generate the page: 0.07671 seconds