Solved

How to concatenate two SELECT results

Posted on 2009-07-13
6
1,269 Views
Last Modified: 2013-12-19
Hi there,

Is there anyway to concatenate two string results without using variables?

I want to concatenate query results like these two in the Code Box.

The result from the first is '08OUT14' and from the second one is 'JOCO', so the result that i want would be '08OUT14JOCO'.

Any help?
SELECT TO_CHAR(data_inicio,'YYMONDD') FROM registo_eventos WHERE customer_id = 28704000002662 

	  

SELECT SUBSTR(p.display_name,1,3) FROM ev_produtos ev, product p WHERE p.product_id = ev.product_id AND customer_id = 28704000002662

Open in new window

0
Comment
Question by:vmorais_
6 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 500 total points
ID: 24838011
Hope this helps
SELECT (SELECT TO_CHAR(data_inicio,'YYMONDD') FROM registo_eventos WHERE customer_id = 28704000002662 ) || (SELECT SUBSTR(p.display_name,1,3) FROM ev_produtos ev, product p WHERE p.product_id = ev.product_id AND customer_id = 28704000002662) FROM dual

Open in new window

0
 
LVL 34

Expert Comment

by:johnsone
ID: 24840447
rrjegan17's post assumes only 1 record could ever be returned by the queries.

If that is not the case, this would work.  You need to substitute either ev or p for ?? on the last line, I don't know which table CUSTOMER_ID is in.
SELECT TO_CHAR(a.data_inicio,'YYMONDD') || SUBSTR(p.display_name,1,3)

  FROM registo_eventos a, ev_produtos ev, product p

 WHERE a.customer_id = 28704000002662 and

       p.product_id = ev.product_id and

       a.customer_id = ??.customer_id;

Open in new window

0
 
LVL 32

Expert Comment

by:awking00
ID: 24841217
Can you post the relevant table structures with some sample data and what you expect the output to be?
0
 

Author Comment

by:vmorais_
ID: 24844560
I have done it like this..

I will put this script in a WINDOWS schedule task, having in log all the update statements.

Thanks.
	SELECT 'UPDATE CUSTOMER SET NAME = ''' || TO_CHAR(re.data_inicio,'YYMONDD', 'NLS_DATE_LANGUAGE =AMERICAN') || SUBSTR(p.display_name,1,3) || SUBSTR(emp.first_name,1,2) || SUBSTR(emp.last_name,1,2)

	  		|| '-' || re.ev_obj_accao || ''' WHERE CUSTOMER_ID = ' || c.CUSTOMER_ID || ';' 

	  		|| CHR(10)

	  		|| 'COMMIT;'

	  		|| CHR(10)

	  		|| 'UPDATE MEETING_VENUE SET NAME = ''' || TO_CHAR(re.data_inicio,'YYMONDD', 'NLS_DATE_LANGUAGE =AMERICAN') || SUBSTR(p.display_name,1,3) || SUBSTR(emp.first_name,1,2) || SUBSTR(emp.last_name,1,2)

	  		|| '-' || re.ev_obj_accao || ''' WHERE CUSTOMER_ID = ' || c.CUSTOMER_ID || ';' 

	  		|| CHR(10)

	  		|| 'COMMIT;'	

	FROM registo_eventos re, ev_produtos ev, product p, employee emp, customer c, meeting_venue mv

	  	WHERE p.product_id = ev.product_id

	  	AND re.creator_employee_id = emp.employee_id

	  	AND mv.customer_id = ev.customer_id

	  	AND ev.customer_id = c.customer_id

	  	AND c.customer_id = re.customer_id

	  	--AND c.name <> mv.name

	  	--AND re.data_inicio > TO_DATE('30-06-2009', 'DD-MM-YYYY')

	  	AND c.name = 'Evento'

Open in new window

0
 
LVL 34

Expert Comment

by:johnsone
ID: 24848057
Interesting.  The posted solution most resembles my suggestion, however I got no points.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now