Solved

Union Query

Posted on 2008-10-17
6
926 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
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 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…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

724 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