Solved

Union Query

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

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.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
error I/O in copying in linux redhat 12 65
Remove Hyphens in Oracle SQL 5 44
Oracle Finace 3 43
compre toata in where clue oracle 4 41
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…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

708 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

17 Experts available now in Live!

Get 1:1 Help Now