| partitioned counter values [message #195909] |
Tue, 03 October 2006 02:22  |
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 #196027 is a reply to message #196023] |
Tue, 03 October 2006 14:40  |
joy_division
Messages: 4963
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 |
 |
Barbara Boehmer
Messages: 9101
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>
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
