Solved

Union Query

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

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

Suggested Solutions

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

777 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