Solved

Union Query

Posted on 2008-10-17
6
897 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

929 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

21 Experts available now in Live!

Get 1:1 Help Now