Solved

Union Query

Posted on 2008-10-17
6
911 Views
Last Modified: 2013-12-19
Can please somebody tell me what this Union query is doing:-

SELECtT
e.firstname,e.lastname,
(
      SELECT e.employee_first_name||' '||e.employee_last_name Circuit_Designer
                  FROM task@asap_read t, employee@asap_read e, serv_req sr
                  WHERE sr.document_number = t.document_number
                  AND t.last_modified_userid = e.employee_number
                  AND t.task_type = 'VE_CORE'
                  AND sr.order_number = sor.RECIC_TBS_ORDER_NUMBER
                  UNION
                  SELECT u.workflow_user_name
                  FROM workflow_user@eods_read u
                        , workflow_task@eods_read t
                        , serv_build_main@eods_read sb
                  WHERE sb.workflow_job_ods_id = t.workflow_job_ods_id
                  AND t.acquire_by_workflow_user_ods_i = u.workflow_user_ods_id
                  AND t.workflow_task_name = 'Design'
                  AND sb.order_item_name = sor.recic_tbs_order_number
) Circuit_Designer,
s.source
FROM
employee e, switch s ......


This sub query having Union clause is returning more that one rows. Due to this the database is throwing exception,
But I am not able to understand the way the sub query is working.
0
Comment
Question by:Manoj_Pardeshi
6 Comments
 
LVL 6

Accepted Solution

by:
divyeshhdoshi earned 64 total points
ID: 22738356
the inner query returning more than one row for one or more external rows.

to overcome from this problem join external table inner queries.

0
 
LVL 27

Assisted Solution

by:sujith80
sujith80 earned 62 total points
ID: 22738386
THis method of using a SELECT statement in the SELECT list is called scalar subqueries.
But the restriction here is; the query should return only one row.

In your case, the UNION clause could be returning multiple values and hence resulting in the error.
You should either restrict the query to make sure that it returns only one value OR you have to more the query to the FROM clause and convert it to a join.
0
 
LVL 7

Assisted Solution

by:Dr_Billy
Dr_Billy earned 62 total points
ID: 22740857
I guess there is a problem in your UNION , the second subquery should have the same column name as the first subquery , you are getting all the employees first and last name as a Circuit_Designer in the first one and the second should get all the user names from the workflow table who has submitted any transaction , so both columns should have the circuit_Designer alias for the column

try this in your subquery and see if that helps

SELECtT
e.firstname,e.lastname,
(
SELECT e.employee_first_name || ' ' || e.employee_last_name circuit_designer
  FROM task@asap_read t, employee@asap_read e, serv_req sr
 WHERE sr.document_number = t.document_number
   AND t.last_modified_userid = e.employee_number
   AND t.task_type = 'VE_CORE'
   AND sr.order_number = sor.recic_tbs_order_number
UNION
SELECT u.workflow_user_name circuit_designer   --<< here is the change you should apply
  FROM workflow_user@eods_read u,
       workflow_task@eods_read t,
       serv_build_main@eods_read sb
 WHERE sb.workflow_job_ods_id = t.workflow_job_ods_id
   AND t.acquire_by_workflow_user_ods_i = u.workflow_user_ods_id
   AND t.workflow_task_name = 'Design'
   AND sb.order_item_name = sor.recic_tbs_order_number
) Circuit_Designer,
s.source
FROM
employee e, switch s ......

Open in new window

0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 62 total points
ID: 22887472
See attached comments.
observations.txt
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to recover a database from a user managed backup
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

829 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