Home » SQL & PL/SQL » SQL & PL/SQL » plsql concatenate how i do that
plsql concatenate how i do that [message #388544] Wed, 25 February 2009 06:01 Go to next message
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 #388545 is a reply to message #388544] Wed, 25 February 2009 06:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at ROW_NUMBER function.

Regards
Michel

[Updated on: Wed, 25 February 2009 06:10]

Report message to a moderator

Re: plsql concatenate how i do that [message #388549 is a reply to message #388545] Wed, 25 February 2009 06:21 Go to previous messageGo to next message
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 #388553 is a reply to message #388549] Wed, 25 February 2009 06:45 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
But why are looking of PL/SQL if this can be done in simple SQL.
Re: plsql concatenate how i do that [message #388554 is a reply to message #388549] Wed, 25 February 2009 06:46 Go to previous messageGo to next message
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 #388557 is a reply to message #388544] Wed, 25 February 2009 06:56 Go to previous messageGo to next message
chomaster
Messages: 6
Registered: February 2009
Location: Holland
Junior Member
I do know anymore Confused .
can jou post code with sql how it can

or best solution
please post the code
Re: plsql concatenate how i do that [message #388558 is a reply to message #388557] Wed, 25 February 2009 07:03 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
the code is

select DISPLAY_NAME || ROWNUM from master


Now YOU would have to explain further why that doesn't fit your requirement.

[Updated on: Wed, 25 February 2009 07:05]

Report message to a moderator

Re: plsql concatenate how i do that [message #388559 is a reply to message #388554] Wed, 25 February 2009 07:04 Go to previous messageGo to next message
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 Go to previous messageGo to next message
pablolee
Messages: 2835
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 Go to previous messageGo to next message
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 #388579 is a reply to message #388544] Wed, 25 February 2009 08:42 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
First of all, there is a difference between r1 and r_1 , wouldn't you agree ?

But, and it has been said before, if you'll stick to the pl/sql way, then I'm afraid this topic has to be moved to the Homework-section of this forum Wink
Re: plsql concatenate how i do that [message #388586 is a reply to message #388579] Wed, 25 February 2009 09:11 Go to previous messageGo to next message
chomaster
Messages: 6
Registered: February 2009
Location: Holland
Junior Member
thank you so much
Re: plsql concatenate how i do that [message #388620 is a reply to message #388586] Wed, 25 February 2009 12:44 Go to previous messageGo to next message
pablolee
Messages: 2835
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
Re: plsql concatenate how i do that [message #388677 is a reply to message #388544] Wed, 25 February 2009 22:25 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Hi,

I believe that even with ROWNUM ( not ROW_NUMBER Analytical function) this is possible.

Regards,
Ashoka BL

Re: plsql concatenate how i do that [message #388684 is a reply to message #388677] Wed, 25 February 2009 22:57 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It is possible only if you have one distinct (collection_id,display_name) or if you don't need to restart from 1 for each one.

Regards
Michel
Previous Topic: TEXT_IO
Next Topic: character manipulation
Goto Forum:
  


Current Time: Thu Dec 08 08:04:36 CST 2016

Total time taken to generate the page: 0.21551 seconds