plsql concatenate how i do that [message #388544] |
Wed, 25 February 2009 06:01  |
chomaster
Messages: 6 Registered: February 2009 Location: Holland
|
Junior Member |
|
|
Hello guys,
I am quite new to PL/SQL and I have a problem. i have rows whit the same name.
I have a table like this:
sql> select Collecion_id, Name from master
---------------------------------------------------------
COLLECTION_ID | DISPLAY_NAME
1........................Turbo
1........................Turbo
1........................Turbo
1........................Turbo
1........................Turbo
----------------------------------------------------------
and i want this output
---------------------------------------------------------
COLLECTION_ID | DISPLAY_NAME
1........................Turbo
1........................Turbo1
1........................Turbo2
1........................Turbo3
1........................Turbo4
---------------------------------------------------------
Here is me code.
declare
CURSOR c_1
IS
SELECT
ID
, FILE_NAME
, FILE_TYPE_ID
, COLLECTION_ID
, DISPLAY_NAME
, DESCRIPTION
, KEYWORDS
, URL
, XMLDOC
ORDER BY
COLLECTION_ID
, DISPLAY_NAME
FROM master
begin
for c_1
loop
end loop;
end;
I can not finish this code
I have found a lot of stuff using cursors but I dont know how to implement them..
If anyone can help me, I would really appreciate it.
Thanks
|
|
|
|
Re: plsql concatenate how i do that [message #388549 is a reply to message #388545] |
Wed, 25 February 2009 06:21   |
chomaster
Messages: 6 Registered: February 2009 Location: Holland
|
Junior Member |
|
|
Thanks for your response Michel but that's not what I'm looking for
I need this basic code who can finish it
declare
CURSOR c_1
IS
SELECT
ID
, FILE_NAME
, FILE_TYPE_ID
, COLLECTION_ID
, DISPLAY_NAME
, DESCRIPTION
, KEYWORDS
, URL
, XMLDOC
ORDER BY
COLLECTION_ID
, DISPLAY_NAME
FROM master
begin
for c_1
loop
end loop;
end;
|
|
|
|
Re: plsql concatenate how i do that [message #388554 is a reply to message #388549] |
Wed, 25 February 2009 06:46   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Actually, if you're looking to append an increasing number on to a field, and you want this number to reset on a certain criteria, then ROW_NUMBER is exactly what you're looking for.
If you think it isn't, you'll have to explain in more detail exactly what you do want.
|
|
|
|
|
Re: plsql concatenate how i do that [message #388559 is a reply to message #388554] |
Wed, 25 February 2009 07:04   |
chomaster
Messages: 6 Registered: February 2009 Location: Holland
|
Junior Member |
|
|
I have a old table with The same display_name en the column is not
UNIQUE
---------------------------------------------------------
COLLECTION_ID | DISPLAY_NAME
1........................Turbo
1........................Turbo
1........................Turbo
1........................Turbo
1........................Turbo
----------------------------------------------------------
and I want inserting the data into a new table but the column name in the new table is UNIQUE.
I would put a number behind the display_name
like thi
---------------------------------------------------------
COLLECTION_ID | DISPLAY_NAME
1........................Turbo
1........................Turbo1
1........................Turbo2
1........................Turbo3
1........................Turbo4
---------------------------------------------------------
|
|
|
Re: plsql concatenate how i do that [message #388560 is a reply to message #388557] |
Wed, 25 February 2009 07:06   |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
Hi chomaster.
Welcome to Orafaq. On this site, we endeavour to help people, and we generally consider that simply providing an answer in full (i.e. "just posting the code") doesn't really help. We prefer that you put some effort in, go and read the links that get provided, have a try at resolving the issue and then post back with any problems (as well as successes)
Good luck
PS, as already advised, ROW_NUMBER is exactly what you need to be working with.
[Updated on: Wed, 25 February 2009 07:07] Report message to a moderator
|
|
|
Re: plsql concatenate how i do that [message #388577 is a reply to message #388544] |
Wed, 25 February 2009 08:34   |
chomaster
Messages: 6 Registered: February 2009 Location: Holland
|
Junior Member |
|
|
Hi,
This is now mij code
declare
CURSOR c_1
IS
SELECT ID
, FILE_NAME
, FILE_TYPE_ID
, COLLECTION_ID
, DISPLAY_NAME
, DESCRIPTION
, KEYWORDS
, URL
, XMLDOC
FROM cdl_files
ORDER BY COLLECTION_ID
, DISPLAY_NAME
;
a_collection NUMBER(10.0);
b_name varchar2(200);
BEGIN
FOR r_1 IN c_1
LOOP
a_collection := r1.COLLECTION_ID;
b_name := r1.DISPLAY_NAME;
dbms_output.put_line
(r_1.DISPLAY_NAME||' '||1 );
END LOOP;
END;
I get this error
Error report:
ORA-06550: line 24, column 23:
PLS-00201: identifier 'R1.COLLECTION_ID' must be declared
ORA-06550: line 24, column 7:
PL/SQL: Statement ignored
ORA-06550: line 25, column 19:
PLS-00201: identifier 'R1.DISPLAY_NAME' must be declared
ORA-06550: line 25, column 7:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
|
|
|
|
|
Re: plsql concatenate how i do that [message #388620 is a reply to message #388586] |
Wed, 25 February 2009 12:44   |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
OK, I'll re-iterate the point. I'm hoping that you pick up on it.
PL/SQL is NOT the route to go. Do this in SQL, user the ROW_NUMBER analytic function to achieve your requirements. The only reason to use PL/SQL is if you are a student and your teacher has expressly told you to use PL/SQL and cursors.
Good luck
|
|
|
|
|