SQL & PL/SQL :: Getting Output From Multiple Rows Return Only One Row In Oracle?

Mar 27, 2013

EMP table

create table emp1 (empno number,deptname varchar2(30),deptno number,sal number);
insert into emp1 values (1,'Bank',10,1000);
insert into emp1 values (1,'Finance',20,400);
insert into emp1 values (2,'Finance',20,4000);
insert into emp1 values (3,'Account',30,3000);
commit;
select * from emp1;

actual output :

empno deptname deptno sal
1 Bank 10 1000
1 Finane 20 400
2 Finane 20 4000
3 Account 30 3000

Expected output :

empno deptname deptno sal
1 Bank 10 1000
2 Finane 20 4000
3 Account 30 3000

I am looking a output like above one. if any empno belongs to deptname Bank then give priority to that values else go to other dept like Finance but empno 1 should return only one row.i.e Bank dept only.

I have to return only one row based on dept values for one empno. how to do in sql or plsql?

View 11 Replies


ADVERTISEMENT

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 :: 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

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

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

SQL & PL/SQL :: How To Print The Multiple Output Statements In Single Output

Jul 1, 2013

DECLARE
JOBSFILE UTL_FILE.FILE_TYPE;
-- TAKE ALL JOB TITLES FROM JOBS
CURSOR JOBSCUR IS
SELECT *
-- DDOCNAME,DDOCTITLE,DSECURITYGROUP,DDOCAUTHOR,DDOCTYPE,DINDATE,PRIMARYFILE,EXTRACTIONDATE,BATCH_ID
FROM TARGET_UCM ;
[code].......

this is my plsql here to print table values i am using many utl_file.put_line statements is there any way to print all table values in a single utl_file.put_line.

View 2 Replies View Related

SQL & PL/SQL :: Splitting 1 Output Row Into 3 Output Rows

Sep 25, 2013

Currently I have a requirement where I need to create 2 more output rows using each result row.

In my requirement I am populating charges table with types of charges, on each line item of charges, I need to apply 2 types of taxes and populate it along with the charge line item. I will be storing charges in table charges and the 2 taxes to be applied in taxes table respectively. For each row of charges, i need to apply these 2 taxes present in taxes table resulting in 3 rows output.

--Create tables charges
create table charges
(
charge_type varchar2(10) ,
charge number
);

[Code]....

My expected output should be like below:

Item_type amount
-------------------- ----------
charge1 100
Charge1_tax1 10
Charge1_tax2 20
charge2 200
Charge2_tax1 20
Charge2_tax2 40

how I can achieve the expected output using a single sql query

View 6 Replies View Related

SQL & PL/SQL :: Oracle Query Failing For Multiple Rows?

Mar 8, 2012

I have been given a task to produce an ad-hoc report based on the following conditions (I will give you the structure of the table and details in the table below the requirements)

Requirements: Adhoc Report for Audit on CIT Income Allocation
1. Select from the RETURNS table:
•Id > 3600000 and
•Prog_program_cd = '01' and

[code]...

3.If there are multiple entries from the same account and tax year, only retain the latest record: From the records selected in step 2, if there are multiple records with the same Acct_Id and Period_end_date, only retain the record with the most recent Status_date (i.e. MAX value on the date).

4. Using the results from step 3, link to the applicable RETURN_LINE_ITEMS table where:
•RETURNS.Id = RETURN_LINE_ITEMS.Rtrn_Id

5. From the selected return on RETURN_LINE_ITEMS table, retrieve records where (value on Sch 000 Line 062 > 500,000) and (value on Sch 000 Line 066 < value on Sch 000 Line 062) :
•Sched_nbr = '000', and Litm_line_item_nbr = '062', and Active_ind = 'Y', get Revise_val_amt as 'ab_taxable_income'
•Sched_nbr = '000', and Litm_line_item_nbr = '066', and Active_ind = 'Y', get Revise_val_amt as 'amt_taxable_in_ab'

[code]...

So when you run the scripts above, the tables are created (I have tested it in TOAD) Now let's feed data into both the tables

RETURNS table:

insert into RETURNS
VALUES
(3602448 , '01' ,288151,3876821,9345370 , 403232531, 1, 'TAXPAYER' ,12/4/2008 ,'N', 'Y','N','ASSESSED', 2/18/2009,'SANDEEP',1/2/2008,1/2/2009,
'PRINTED','2/18/2009, 6043073664,'PAPER', 'NOTICE','N');

[code]...

View 15 Replies View Related

Client Tools :: Removing Final Carriage Return From Spooled Output?

Jul 29, 2010

I spool an output using the following script and get a final carriage return at the end. This fails our SSIS bulk insert task. Sample below:

SET SPACE 0
SET HEAD OFF
SET FEEDBACK OFF
SET TERMOUT OFF
set echo off
set newpage 0
set space 0
set pagesize 0

[code]....

View 13 Replies View Related

SQL & PL/SQL :: Min / Max With Multiple Row Return

Apr 22, 2010

Client managerCont. Start DateCont. End Date
abcman11-Jan-0830-Jun-08
abcman21-Jul-0831-Dec-08
abcman11-Jan-0930-Jun-09
abcman11-Jul-0931-Dec-09
abcman21-Jan-1031-Mar-10
abcman21-Apr-1031-Aug-10

I need to code a SQL statement (Not PL/SQL) to display following records:

Client managerCont. Start DateCont. End Date
abcman11-Jan-0830-Jun-08
abcman21-Jul-0831-Dec-08
abcman11-Jan-0931-Dec-09
abcman21-Jan-1031-Aug-10

View 4 Replies View Related

PL/SQL :: Return More Rows Than Specified

Sep 30, 2013

I am fetching records from many voluminous tables having multiple joins based on filter criteria filled from frond end application. As per the selected criteria, I want to have a pre-check if query would return more than 1000 rows then I have to show user a message saying that he should refine the search. Is there any performant way to query db and dynamically find record count and stops executions if it is going return more than the specified no of rows.

View 5 Replies View Related

Why Dba_feature_usage_statistics Return No Rows

Aug 30, 2012

I have a database in 10g in entreprise edition but i don't understand why the dba_feature_usage_statistics return no rows ? On some database when i look at dba_ feature_usage_statistics Protection Mode - Maximum Performance is used and active why?

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 :: How To Return Multiple Values From A Function

Aug 28, 2010

I want multiple values from a function. I want to use this function in a SQL query. Here i'm giving my try.

SQL> CREATE TABLE TEMP
2 (
3 ID NUMBER(1),
4 SAMPTYPE VARCHAR2(20 BYTE),
5 SALARY NUMBER(10)
6 )
7 /

Table created.

SQL> INSERT INTO TEMP VALUES(1,'ABC',10000);

1 row created.

SQL> INSERT INTO TEMP VALUES(2,'PQR',20000);

1 row created.

SQL> INSERT INTO TEMP VALUES(3,'JPD',5000);

1 row created.

SQL> COMMIT;

Commit complete.
[code]...

Here i get result as ABC*10000, but i want two separate values as ABC,10000. how can i do this via function.

View 6 Replies View Related

PL/SQL :: Single Query Return Multiple Value

Sep 6, 2012

I have a sql query as below :

select order_number,
(select decode(hcp.contact_point_purpose,'ABC',hcp.email_address,'CDE',hcp.email_address,null)
from hz_contact_points,
hz_parties hz
WHERE hz.party_id=hcp.owner_table_id) Email
FROM oe_order_headers_all h
WHERE h.order_number='102'
....................
..............

Actually the problem i am facing is the inner select query is returning multiple row , so my main query is erroring out, i need to capture the multiple row.

In the above example the inner decode statement returning two mail address, I need to capture that, but while executing the whole query it is erroring out as saying single query returns multiple values. capture multiple values

View 3 Replies View Related

SQL & PL/SQL :: How To Use Case When Return Multiple Values

Mar 13, 2012

I have a table second_table which has a username and code field. A username (not the primary key) may be entered multiple times with different codes, or a single code 'ALL', in which case, the codes have to be fetched from 'third_table'. I am unable to form a 'case' clause to handle the fact that a list has to returned when the 'code' field from the second_table returns 'ALL'.

e.g.

Second_table
username code
A ALL
B 23
B 56
B 33

Third_Table
code
67
78

So, when the user asks the codes for user A, he should get 67 and 78 and when he asks for the user B, he should get 23,56 and 33

View 13 Replies View Related

SQL & PL/SQL :: How To Return Last 2 Rows In History Table

Nov 23, 2011

I have a history table which has a new record written to it for every time static data is updated. What I want to do is return any record that has been changed on a daily basis returning both the new record and the previous record so you can see the 2 records one below the other and compare.I have written the below but it returns all records per account:

SELECT H.Timestamp,H.AccountNo FROM History H
where exists (select H2.AccountNo,count(*) from History H2
where H2.AccountNo = H.AccountNo
group by H2.AccountNo having count(*) > 1 )
Where H.Timestamp > '20111101'
order by H.AccountNo,H.Timestamp

View 18 Replies View Related

SQL & PL/SQL :: Query To Return Two Different Concatenated Rows

Jan 10, 2012

I have not found a solution for this, but it could be that I don't know what to search for.

I need to edit the following statement:

SELECT a.id||a.name||a.amount*2 as transaction
FROM a
WHERE a.amount IN (500, 1000)

To return:

TRANSACTION
------------------------------------
123SMITH1000
123SMITHADJUSTED AMOUNT PER X
456JONES2000
456JONESADJUSTED AMOUNT PER X

The returned rows need to be in this format to be executed in another database.

Can this be done?

View 8 Replies View Related

SQL Query - Return Operation Over Multiple Dates

Feb 16, 2011

I am new to SQL and I am just wondering if there is a solution to a problem I am having.I am using the piece of code below.Essentially what I am doing is selecting a field from a table and ordering that field in descending order. Using the Row_Number feature I then specify which Row I want to return.

Every day the row I will want is the Count of field1 for that day divided by 100 minus 1. This returns a single value of field1 and a single value of R.

I perform this operation every day. The only fields I change every day are the dates and the value of R. I use a seperate piece of SQL code to calculate R each day.

My problem is I have to often populate historical tables with this data. I can only run the code once for each day and for each value of R. Is there anyway I can alter this code such that it can return multiple values of field1 over several dates?The only way I can think of is to repeat the code multiple times using UNION but I am hoping there is a more efficient way.

SELECT *
FROM (SELECT Field1,
ROW_NUMBER() OVER (ORDER BY field1 desc ) R
FROM table
WHERE date >= TO_DATE ('20110215', 'YYYYMMDD')
AND date < TO_DATE ('20110216', 'YYYYMMDD')
)
WHERE R = 1227
--Note: 1227 = (count(field1)/100)-1

View 5 Replies View Related

SQL & PL/SQL :: Select Query To Return Parent Rows?

Apr 1, 2012

I need to get the super set(parent) record from a relational table. Following is the structure of the table,

create table relation (data_field1 number(20),data_field2 number(20),data_field3 number(20),

primary key (data_field1,data_field2,data_field3) );
insert into relation (data_field1, data_field2, data_field3) values (-10,123,334);
insert into relation (data_field1, data_field2, data_field3) values (200,123,334);
insert into relation (data_field1, data_field2, data_field3) values (300,123,334);
insert into relation (data_field1, data_field2, data_field3) values (400,123,334);

[code].....

Here '-10' will be considered as parent(super set) for all other values. If i have records like below,

-10 200 300
100 200 300
123 200 300
521 201 300

Output should only contain two rows. A super set row and other distinct row(second column value is not equivalent to super set).

View 14 Replies View Related

SQL & PL/SQL :: Why Does SELECT DISTINCT Return Different Rows When Used With ORDER BY

Jan 4, 2013

nex lines showing SELECT DISTINCT return different rows when used with ORDER BY.

CREATE TABLE M1( ID_ NUMBER, A NUMBER, B NUMBER);
CREATE TABLE V1 ( A NUMBER, B2 NUMBER);
DELETE FROM M1;
DELETE FROM V1;
INSERT INTO M1 (ID_, A,B) VALUES (107, 5,1);
INSERT INTO M1 (ID_, A,B) VALUES (108, 11,1);
INSERT INTO M1 (ID_, A,B) VALUES (109, 17,1);

[code]....

View 11 Replies View Related

PL/SQL :: Return The Rows Of The Table Where Column Contains Specific Value First

Feb 5, 2013

I want my query to return the rows of the table where a column contains a specific values first in a certain order, and then return the rest of the rows alphabetized.

For Example:

Country
ALBANIA
ARGENTINA
AUSTRALIA
....
CANADA
....
USA
....

Now i want USA and CANADA on top in that order and then other in alphabetized order.

View 5 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

SQL & PL/SQL :: Carriage Return Line Feed Multiple Times In Same CLOB

Jan 31, 2012

I'm looking for a way to make CRLFs show in a CLOB.I'm feeding the insert statement with a concatinated string like this:

insert into table(Data) values (MyConc)

(MyConc is a string put togheter by another application)

Because of this I can't use the "|| chr(13) || chr(10) ||" because I only have that one concatinated string.Is there a character I can set in my string that automatically translates to a CRLF?

My CLOB-data should look something like this:

1;blue;Woodstock;;
34;giant;squid;attack;
5;blue;squid;;

And in this case the "MyConc" would look like this "1;blue;Woodstock;;[X]34;giant;squid;attack;[X]5;blue;squid;;[X]

where [X]=the character I need for CRLF =)

View 11 Replies View Related

SQL & PL/SQL :: Formatting Output Into Multiple Columns?

Apr 16, 2013

as part of an exercise we are to demonstrate a FOR LOOP from 1 - 100.

We must show the output as evidence of doing the work.

Is it possible to display the results in two columns, instead of all in one row? Because 100 rows will not fit it one screenshot!

BEGIN
FOR i in 1..100
LOOP
DBMS_OUTPUT.put_line(i);
END LOOP;
END;
/

View 5 Replies View Related

SQL & PL/SQL :: UNION ALL Output In Multiple Columns?

Nov 26, 2012

Following is my table structure:

14:54:17 PYMTPRODIAT@UCS43 > select * from DGTEST;

ID AMOUNT DI
---------- ---------- --
1 50 D
2 50 D
3 20 D
4 60 C
2 60 C
3 20 C

now, I want the output in the following format.what could be the easiest way.. I need the output in one query,,

ID D_Net_Amount C_Net_Amount
--- ------------- --------------
1 50
2 50 60
3 20 20
4 60

View 2 Replies View Related

PL/SQL :: Concat Multiple Output Lines?

Nov 21, 2012

I'm struggling with a problem for quite a while now and can't find any solution online so far ...

OK I got three different Tables

cust_tbl:
customer:id
nuxx :1
naxx :2
baxx :3
suxx :4

[code]...

As you see, I only need the Customer one time followed by part, amount and address - separated hyphen - comma, next part, amount, address and so on ...I've tried the PIVOT way and STRAGG (wm_concat) but all failed so far.The main problem is that when one customer fits more than one condition, ora throws: ORA-01427: single-row subquery returns more than one row

View 9 Replies View Related

SQL & PL/SQL :: To Remove Duplicate Rows From Output

Jun 14, 2011

I have a view and in that view i need to remove duplicate rows from output. For that i need to run select query in where clause of view if select query return true then we need to execute second condition.

my requirement in view like

And..........
And ((select count(*) from table A where conditions)=1 )then name is null
AND

in that code first we need to check first select query condition then we need to apply name is null condition. but i tried to run it but select query not run properly. because tables is used in View.

View 4 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







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