Subquery Returning Multiple Rows

Oct 13, 2009

I understand what the message "subquery returning multiple rows" means but I have a case where I'm not 100% sure why it's happening to my update query (which in turn probably means I don't fully understand what's going on behind the scenes)

Here is my query:

Update A set (A.id, A.alt_name, A.min_rank)=
(SELECT B.id,
B.fullname,
MIN(B.nm_rankval)
FROM B,
A
WHERE A.id = B.id
AND A.name <> B.fullname
AND B.nametyp = 'ON'
GROUP BY B.id,
B.fullname)
;

The subquery returns 6 rows but they are all unique in terms of the id, name, rankval, etc...I naturally thought that the update statement wouldn't have a problem with this since the subquery is returning rows that are not duplicates and match one for one between table A and B. I used the group by to ensure I return unique values from table B (which does have duplicate id values)

Each of those 6 rows from the subquery of table B can be matched 1-1 with table A...so what am I missing.

View 2 Replies


ADVERTISEMENT

Subquery Returning Multiple Rows And Update

Mar 3, 2010

This is a surprisingly common one I've found on the web...even on devshed forum

I am updating one table from another (Updating Table A from Table B):

Table A
ID, Value
-- -----
1 A
1 A
2 B

Table B
ID, Value
-- -----
1 Animal
2 Box

Table A (modified)
ID, Value, Name
1 A Animal
1 A Animal
2 B Box

No I need to update a new column in Table A with the value in Table B.Value where the ID's from both tables match. Problem is: When I do this I get multiple rows and hence Oracle won't let me update this column. Now, I keep reading that for these types of updates, there has to be a one-to-one relationship...

Is this true...is there anyway of telling Oracle to update wherever it finds that ID, regardless of how many duplicate ID's there are?

This is quite a frustrating problem and most of the sites that I've looked for solutions try get the query one-to-one...problem is...with my table sets it's impossible to do that - I need to update wherever the id's match (even if it return multiple rows).

View 10 Replies View Related

Returning Multiple Rows

Mar 1, 2011

I am working on a script in which I want to retrieve multiple rows but I get error ORA-1422.I tried solving it using the following script , but it still gives error.

CREATE OR REPLACE PROCEDURE proc_query
DECLARE
TYPE all_dest IS TABLE OF NUMBER;
destIds all_dest;
BEGIN
SELECT dest_id from sb_packet WHERE src_id = 32;
RETURNING dest_id bulk collect into destIds;
END;

View 3 Replies View Related

SQL & PL/SQL :: Package Returning Multiple Strings

Apr 5, 2011

I have a function in PL/SQL that uses CTE to obtain several strings. I need to return those strings to C#, either as a Return Value or a OUTPUT parameter.

I've managed to do that using PLSQL Associative Array, but it has the unnecessary usage of Array Bind Size, and I do not need to know what will be my Array size. Is that any other way to do it?

My Package is the next one:

create or replace
PACKAGE Pkg1 IS
TYPE listResults is TABLE of VARCHAR2(100) INDEX BY BINARY_INTEGER;
PROCEDURE CalculateResults ( iCode IN VARCHAR2, iAg IN VARCHAR2, resultados OUT listaResultados );
END Pkg1;

create or replace
PACKAGE BODY Pkg1 AS
PROCEDURE CalculateResults(
iCode IN VARCHAR2,
[code]......

View 4 Replies View Related

SQL & PL/SQL :: Returning Updated Rows With REF CURSOR?

Sep 27, 2013

The following code is indicative of what I'd like to do (as in not correct at all ). Would there be a more immediate way to accomplish this other than executing a SELECT statement after the UPDATE?

-- Incorrect indicative example 1.

DECLARE
v_cur SYS_REFCURSOR;
BEGIN
UPDATE table1(f1, f2)
SET ('v1', 'v2')
WHERE f3 = 'v3'
RETURNING <updated_rows> INTO v_cur
END;

-- Incorrect indicative example 2.

DECLARE
v_cur SYS_REFCURSOR;
BEGIN
OPEN v_cur FOR
UPDATE table1(f1, f2)
SET ('v1', 'v2')
WHERE f3 = 'v3'
END;

View 4 Replies View Related

Performance Tuning :: Returning All Rows That Equal Max (value)?

Oct 14, 2010

I'm trying to write a query that counts how many sessions are active during a 1 second time interval, then returns the maximum number of sessions active during any time interval, and all the time intervals that hit that max.

Here's a sample of the inner query results:

"INTERVAL_VALUE""SESSIONS"
"13:14:47" 13
"13:14:52" 13
"13:14:54" 13
"13:19:05" 4
"13:19:28" 4

[code]....

The max(sessions) is 13, so what I want the final output to be is:

"INTERVAL_VALUE""SESSIONS"
"13:14:47" 13
"13:14:52" 13
"13:14:54" 13

Here is the create sql for the test data:

CREATE TABLE "SESSION_TABLE"
(
"SESSIONKEY" NUMBER,
"SESSION_START_TIME" TIMESTAMP,
"SESSION_END_TIME" TIMESTAMP,
CONSTRAINT "PK_SESSIONKEY" PRIMARY KEY ("SESSIONKEY")
);

[code]....

Here is my query that works:

SELECT
maxval.interval_value,
allval.sessions,
licenselimit
FROM
(SELECT

[code]....

View 2 Replies View Related

SQL & PL/SQL :: Select Column_list Vs Returning Different Number Of Rows

Mar 2, 2011

The query below returns 101 rows. If I replace the column list with an asterik the query returns 892 rows. I do not understand why.

--select *
select Ref_Consultant_CD
,Resident_CD
,ID
,Ref_Facility_CD
[code]......

View 6 Replies View Related

Multiple Table Query Returning Duplicates With Wrong Values

Oct 20, 2012

These are the tables I'm working with:

SQL> desc custinfo;
Name Null? Type
----------------------------------------- -------- ----------------------------
CUSTID VARCHAR2(4)
CUSTNAME VARCHAR2(18)
CUSTADR VARCHAR2(12)
CUSTCITY VARCHAR2(10)
CUSTSTATE CHAR(2)
CUSTZIP VARCHAR2(5)
CAPACITY NUMBER(3)
HOUSECODE VARCHAR2(2)
[code]...

I don't understand why the custid is the same for each customer, and why it's selecting every customer and not just those with more than 150 gallons ordered.

For this one use the oil tables that you set up and use a subquery. Select the minimum average fall use from the house table. Then show all customers whose number of gallons delivered times two is greater than the minimum.

View 4 Replies View Related

PL/SQL :: Merge Multiple Rows Into Single Row (but Multiple Columns)

Oct 17, 2012

How to merge multiple rows into single row (but multiple columns) efficiently.

For example

IDVal IDDesc IdNum Id_Information_Type Attribute_1 Attribute_2 Attribute_3 Attribute_4 Attribute_5
23 asdc 1 Location USA NM ABQ Four Seasons 87106
23 asdc 1 Stats 2300 91.7 8.2 85432
23 asdc 1 Audit 1996 June 17 1200
65 affc 2 Location USA TX AUS Hilton 92305
65 affc 2 Stats 5510 42.7 46 9999
65 affc 2 Audit 1996 July 172 1100

where different attributes mean different thing for each Information_type. For example for Information_Type=Location

Attribute_1 means Country
Attribute_2 means State and so on.

For example for Information_Type=Stats

Attribute_1 means Population
Attribute_2 means American Ethnicity percentage and so on.

I want to create a view that shows like below:

IDVal IDDesc IDNum Country State City Hotel ZipCode Population American% Other% Area Audit Year AuditMonth Audit Type AuditTime
23 asdc 1 USA NM ABQ FourSeasons 87106 2300 91.7 46 85432 1996 June 17 1200
65 affc 2 USA TX AUS Hilton 92305 5510 42.7 46 9999 1996 July 172 1100

View 1 Replies View Related

SQL & PL/SQL :: Multiple Rows On A Table To Multiple Columns On One Row

Nov 26, 2010

I am attempting to select back multiple values for a specific key on one row. See the example below. I have been able to use the sys_connect_by_path to combine the fields into one field but I am unable to assign them to fields of their own. See the example below

TABLE DETAILS:
Policy id plan name
111 A Plan
111 B Plan
111 Z Plan
112 A Plan
112 Z Plan

My desired result is to be able to show the output as follows

Policy ID Plan_1 Plan_2 Plan_3
111 A Plan B Plan Z PLan
112 A Plan Z PLan

View 6 Replies View Related

PL/SQL :: Multiple Rows To Multiple Column

May 6, 2013

I have a table TableA containing 2 columns ( Name and Value). Here I know what are the values for column Name

TABLEA
=======
Name Parameter
-------------------------
Nexus 11
GPlay 21
Demo 31

I need a query which provides the below output

Desired Output:
======
First Second Third
11 21 31

I have tried the below query
SELECT
DECODE (name,'Nexus', parameter) First,
DECODE (name, 'GPlay', parameter) Second,
DECODE (name, 'Demo', parameter) Third
FROM (SELECT name, parameter FROM TableA where name in ('Nexus','GPlay','Demo'));

This gives me the output

First Second Third
11 <Empty> <empty>
<empty> 21 <empty?>
<empty?> <empty?> 31

Is there any way to get the output in single line.

View 3 Replies View Related

SQL & PL/SQL :: Multiple Rows Into One Row

Apr 10, 2011

tried searching google and this site too, found postings on WM_CONCAT, STRAGG, concat_all, LISTAGG functions by Michel and have experimented with these, but either the syntax is giving me a hard time or i just have not got the concept down.

Trying to get 2 rows into one. Have provided the create statements and insert of data. Also below will show what is returned with a Select i have and what is ideally required.

CREATE TABLE Person_Lang
(
Person_ID NUMBER NOT NULL,
Language_ID NUMBER NOT NULL,
Contact_Name VARCHAR2(255 CHAR),
Main_Phone VARCHAR2(255 CHAR),
Secondary_Phone VARCHAR2(255 CHAR),

[Code]...

Data Returned from Select is:

1 46 905-231-3319 22 Street11 Apt402 Brantford
1 46 905-231-3319 23 Street12 Apt315 MainTown

Ideally what is required back is:

1 46 905-231-3319 22 Street11 Apt402 Brantford 23 Street12 Apt315 MainTown

View 7 Replies View Related

SQL & PL/SQL :: Generated Multiple Rows?

Nov 5, 2010

in eache record we are receiving information from differente city, we pretend to get an output where we have a row for each city (delimited by comma) that we have in column CITY

Input data

SELECT '1001001' as CLIENT_ID, 'LONDON, PARIS' as CITY, TO_DATE('20101105', 'YYYYMMDD') as DT_REG FROM DUAL UNION
SELECT '1022201' as CLIENT_ID, 'MADRID, OSLO' as CITY, TO_DATE('20101105', 'YYYYMMDD') as DT_REG FROM DUAL UNION
SELECT '1033001' as CLIENT_ID, 'PARIS' as CITY, TO_DATE('20101105', 'YYYYMMDD') as DT_REG FROM DUAL UNION
SELECT '1033004' as CLIENT_ID, 'MADRID, OSLO, PARIS' as CITY, TO_DATE('20101105', 'YYYYMMDD') as DT_REG FROM DUAL

Output expected
CLIENT_IDDT_REGCITY
100100105/11/2010 LONDON
100100105/11/2010 PARIS
102220105/11/2010 MADRID

[code]...

View 3 Replies View Related

SQL & PL/SQL :: Merge Multiple Rows Into One Row

Apr 7, 2011

How am i able to combine multiple rows into one row without using cursor if possible. I am currently using SQLPlus.

My current result set:
user id|user name|group name
1|user1|group1
1|user1|group2
2|user2|group1
2|user2|group3

What i wish to achieve:
user id|user name|group name
1|user1|group1,group2
2|user2|group1,group3

View 7 Replies View Related

SQL & PL/SQL :: Return Only Multiple Rows

Apr 30, 2010

I need to return results for the following query only when there are two or more rows found by the following:

SELECT DISTINCT D.PUBLICATION,
D.ADI,
D.DISTRICT,
D.ACCOUNT,
D.ROUTE,
[code]......

Expected results would be:

1 145 CRF TUANR000 VM BILLETING 16-APR-10 5 5 23-APR-10 48623
1 145 CRF TUANR000 VM BILLETING 16-APR-10 5 4 23-APR-10 48629

View 19 Replies View Related

SQL & PL/SQL :: Getting Multiple Rows To Single?

Mar 24, 2010

I have records:

owner company
A X
A Y
A Z
B X
B Y
C X

owner companyX companyY companyZ
A 1 1 1
B 1 1 0
C 1 0 0

How do I write the SQL?

View 2 Replies View Related

Execute Immediate Command For Multiple Rows?

Mar 25, 2007

I have to build a select query but its where conditions will be retrieved from a table. I was told that the execute immediate command can handle it.

lets say i have this:

string_var:= 'select field1, field2, field3
from mytable
where' ' || i.condition_selection || ';'

If the above select resuls in a single row, i could do this:

EXECUTE IMMEDIATE string_var INTO var_field1, var_field2, var_field3;

In my case the select will return multiple rows. How do I proceed ?

View 4 Replies View Related

Convert Rows To Multiple Columns

Aug 30, 2004

I have a table called N1

N1_no Srvarea_type_cd
1 P
1 P
2 C
2 C
2 C
3 I

Another table N2

N1_no srvarea_txt
1 ABCD
2 DEFG
3 XYZA

Can i get a query so that the data can be displayed in the following way ..

P C I
ABCD DEFG XYZA

View 3 Replies View Related

Select All Rows Where Multiple Criteria Met?

Sep 14, 2009

My table has the follwoing 3 columns (in addition to others)

Col Name = active ; type=number ; values=1 (true) or 0 (false)
col name start_date ; type=date; format=dd-mmm-yy
col name end_date ; type=date; format=dd-mmm-yy

I need to select all rows where all active=1, start_date=<today and end_date=>today

my sql is: SELECT id, start_date, end_date FROM offers WHERE (active='1' AND start_date<='14-SEP-09' AND end_date>='14-SEP-09');

However the results are not right. Example, the first row returned is: Offer5000312 01-JAN-09 11-DEC-08

This is not correct. Due to the end_date this row should not be part of the results.

View 10 Replies View Related

SQL & PL/SQL :: Multiple Rows Based On One Column

Mar 24, 2013

I have one table , with one column having 2,3 or 4 machine codes , i need to display them as each row per machine code will it be possible to do as i have thousands of records similar to the test case and which i had to do it manually in excel and then upload it back.

create table ow_oper_setup (wo_no varchar2(12),mrk_no varchar2(20),pos_no varchar2(30),mc_code varchar2(60))

insert into ow_oper_setup VALUES ('1270','1270001','W165','IR HO BV ')
insert into ow_oper_setup VALUES ('1270','1270001','W1332','IR BV ')
insert into ow_oper_setup values ('1270','1270001','W1367','RE HO SC BV ')
insert into ow_oper_setup values ('1270','1270001','W389','RE HO SC BV')

commit;

SELECT * FROM ow_oper_Setup;

WO_NOMRK_NOPOS_NOMC_CODE
12701270001W165IR HO BV
12701270001W1332IR BV
12701270001W1367RE HO SC BV
12701270001W389RE HO SC BV

--i want the output in the following way or the same table data to be replaced as below

WO_NOMRK_NOPOS_NOMC_CODE
12701270001W165IR
12701270001W165HO
12701270001W165BV
12701270001W1332IR
12701270001W1332BV
12701270001W1367RE
12701270001W1367HO
12701270001W1367SC
12701270001W1367BV
12701270001W389RE
12701270001W389HO
12701270001W389SC
12701270001W389BV

View 12 Replies View Related

SQL & PL/SQL :: How To Convert Single Row Into Multiple Rows

Feb 28, 2012

CREATE TABLE T1 ( id NUMBER,
START_date DATE,
end_date DATE,
end_date1 DATE,
end_date2 DATE,
end_date3 DATE,
LEVEL1 number
)
/

[Code]...

I have data in the first table as mentioned above I need to insert multiple rows into the second table for the same ID depends on the level, If it is level 1 then two rows for same ID first reocrd start_date as the start_date and end_date as end_date from the table t1 for second record start_date is end_date in t1 and end_date for this record is end_date1 column in table t1.

If the level is 3 then the table t2 should have four records for one id and the phase is the value for each record for one ID for example in level 3 we have 4 records for one id and phase should be 1,2,3,4.

View 3 Replies View Related

Duplicating Multiple Rows - Getting Error?

Sep 9, 2008

I'm getting an error "SQL command not properly ended" with the following complex delete duplicates query.

DELETE FROM A_EMIS_TOXIC_DTL
WHERE exists
(
select dcf.MASTER_AI_ID,
dcf.INT_DOC_ID,
txd.SUBJECT_ITEM_CATEGORY_CODE,

[code]...

View 2 Replies View Related

SQL & PL/SQL :: Transform Single Row Into Multiple Rows?

Mar 22, 2013

writing the sql, to transform a single row into multiple rows. I am trying to create multiple rows based on a value of a column in the table.In the below example, I am trying to create the rows based on the 'Col2' values. find the below example:

Original table data:

Col1 Col2 Col3 Col4

Row1 a1 a,b,c 01 ON
Row2 b1 d,e,f 02 OFF
Row3 c1 g,h 03 ON

I want the above table to be transformed into below:

Col1 Col2 Col3 Col4

Row1 a1 a 01 ON
Row1 a1 b 01 ON
Row1 a1 C 01 ON
Row2 b1 d 02 OFF
Row2 b1 e 02 OFF
Row2 b1 f 02 OFF
Row3 c1 g 03 ON
Row3 c1 h 03 ON

View 2 Replies View Related

SQL & PL/SQL :: Split Multiple Columns Into Rows

May 2, 2012

I have data like :

ID NAME1 NAME2 NAME3
JJ AD MED
VI TIBO PH TIBO

I want output like

ID NAME
JJ AD
JJ MED
VI TIBO
VI PH
VI TIBO

View 4 Replies View Related

SQL & PL/SQL :: Function Return Multiple Rows

Jun 23, 2006

can i have a pl/sql function that can return multiple rows

may be the syntax will be like

create or replace function multiple() returns ...
begin
select candidateid from tbl_candidateinfo;

..code to return the result of above statement to calling program..

end;

and functions will be called as

select candidateid from .. where candidateid in( select multiple());

View 13 Replies View Related

SQL & PL/SQL :: Joining One Row To Multiple Rows In Same Table?

Feb 8, 2011

I have two tables. The first contains all Segment information within Oracle i.e;

Table 1
Segment Description Flex_Value
1 North 1234
1 South 1235
1 East 1236
2 Car C001
2 Boat B001

and the second table contains financial data, but only the segment code;

Table 2
Date Segment1 Segment2 Value
01/01/11 1234 C001 10,000
02/01/11 1235 C001 10,000

what I want to return is some of the columns within table two as well as additional columns for the segment descriptions.

Below is an extract of what I have put together, and it does return what I need, but I'm sure there is a much more efficient way of creating the query;

SELECT

b.DATE,
b.SEGMENT1,
b.SEGMENT2,
b.SEGMENT3,
b.SEGMENT4,
b.SEGMENT5,

[code].....

View 6 Replies View Related

PL/SQL :: Function That Return Multiple Rows

Jun 6, 2013

I need a function that should return output of this query

SELECT b.branding_code, c.name_desc
FROM
development.brandings b, godot.company c
WHERE b.company_id = c.company_id;

This above function return 30 rows and I am not giving any input

Function using cursor,pipeline

View 5 Replies View Related

PL/SQL :: Datatype Name For Multiple Rows Return

Jun 7, 2013

If I return multiple rows says ex:100 rows then what will be data type for that return values.

View 1 Replies View Related

PL/SQL :: Sql Query To Get Multiple Dates In Rows

Oct 1, 2013

i need a query to get dates for last 7 days and each dates should be in one row... but select sysdate from dual..gives one row...

Expected Output Dates: 01-oct-201330-sep-201329-sep-201328-sep-201327-sep-201326-sep-2013

View 3 Replies View Related

Return Multiple Rows From Stored Procedure?

May 26, 2010

How to return multiple rows from the stored procedure in ORACLE..

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved