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


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

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

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

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

Application Express :: 4.2 Item - Validation Return Boolean And Return Error Text Are Switched

Oct 17, 2012

In Apex 4.2, the item validation of "Function Returning Boolean" and "Function Returning Error Text"; They seam to be backwards.

Is there a simple statement that can be used to fix this in the apex dictionary?

View 1 Replies View Related

Server Utilities :: Loading Multiple Input Files Into Multiple Tables

Jul 9, 2012

NGFID;RECTYPE;RECNAME
25;7;POLES
PARENT
CHILD;1401;9845075;2020
817;8;SUPPORT
PARENT
CHILD

Required output:-

AREA_SRNO = 1
AREA_NAME = '3rivieres.export.ngf'

File :-mauri.export.ngf

NGFID;RECTYPE;RECNAME
257;7;POLES
PARENT
CHILD;1401;9845075;2020
8174;8;SUPPORT
PARENT
CHILD

Required output:-

AREA_SRNO = 2
AREA_NAME = 'mauri.export.ngf'....etc

CREATE TABLE NGF_REC_LINK
(
AREA_SRNO NUMBER(2),
AREA_NAME VARCHAR2(40),
NGFID NUMBER(20),
TABLENAME VARCHAR2(40),
PARENT VARCHAR2(200),
[code].......

find the ctl file (ngf_test.ctl) and modify the ctl file as per my requirement.

View 6 Replies View Related

Server Administration :: Multiple Database With Multiple Instance On Same Machine?

Mar 16, 2011

can we have multiple database version running in a single machine with multiple instances provided there are enough resources.Can we do in RAC only?

View 15 Replies View Related

SQL & PL/SQL :: How To Insert Data In Multiple Bases Using Multiple Database Links

Jan 2, 2013

how to insert the data in multiple bases( Same table structure in different bases) using the multiple database links?

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 :: How To Return In One Row Only

Aug 21, 2012

i need to have an SQL statement that will return only in one row.

in this SELECT statement,

select NVL(a.jj_crdr,'CR'),
nvl(a.CR_cnt,0) CR_cnt,
NVL(a.CR_amt,0) CR_amt,
NVL(b.jj_crdr,'DR'),
NVL(b.DR_cnt,0) DR_cnt,
NVL(b.DR_amt,0) DR_amt

[Code]...

it returned

Tot_cr tot_cr_amt tot_dr tot_dr_amt
1 100 0 0

because there is record for CR and there is no DR.

but when there's record in DR and no in CR, there's no record returned.

because the outer join is in the 2nd Select. i need to change the condition to: 'where a.jj_creator(+) = b.jj_creator' in order to return a record.

what i need is to be flexible so that it will return records even if one of the source doesn't have record.

View 10 Replies View Related

SQL & PL/SQL :: XML - Return New Row For Each Variable?

Oct 31, 2013

I have some XML being returned from a web service, and it returns almost 900 variables. Whilst I am familiar with how to return these in a single row, do I can return a row for each variable? My DBA is very uncomfortable with creating a table with almost 900 columns, for obvious reasons. However, we already have plenty of tables with tens of millions of rows, so he's fine with that. I'll try and expand on the requirement. Below is some XML from the data returned to us:

<APPLICANT app_no="1">
<APPLSUMMARY>
<MAIN W="ZZ" X="{ND}"/>
<COUNTS Z="3" AB="0" BB="3" CB="0" DB="3" EB="3" FB="3" GB="0"/>
</APPLSUMMARY>
</APPLICANT>

I would like to be able to return a new row for each variable, For example:

VARIABLE | VALUE
----------------
W | ZZ
X | {ND}
Z | 3
And so on.

View 2 Replies View Related

SQL & PL/SQL :: How To Return Just First Row Which Has Max Date

Sep 1, 2010

This query returns 2 rows and the output is displayed as well. how I can return just the first row where the max end_date is 4/30/2011?

select
pt.customer_number,
pt.customer_name,
lease.lease_number,
lease.lease_name,
lease.property_name_disp,
lease.location_code_disp,
MAX(pt.end_date) end_date,
pt.attribute1 Disabled
[code]...

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

Varying Return Types

Feb 17, 2012

I have two Oracle instances, both are 10.2.0.3..Executing the following results in two different results:

select N'Test" "result" from dual;

On one the output is:
resu
----
Test

and on the other the output is

result
----------------
Test

This seems to suggest that one is returning "Test" as a char(16) and the other as a varchar2 or nvarchar2. In both cases, NLS_CHARACTERSET is WE8ISO8859P1 and NLS_NCHAR_CHARACTERSET is AL16UTF16.

Not being an Oracle DBA, I am not sure where to look.

View 1 Replies View Related

Function Return Parameters

Dec 5, 2006

The function definition in PL/SQL has IN OUT parameter as well as return statement. Using both we can return the values. Basic definition of a function is function can return only one value at a time.

The question is, Can we return a number thru return statement and a char value thru INOUT parameter. Is it possible to return two different values using these?

View 1 Replies View Related

How To Return Image From Oracle10xe

Aug 8, 2011

i am storing image in label how to return for calling place.

View 1 Replies View Related

Return Minimum Value Of Resultset

Jul 22, 2010

SELECT
REF.REFERRAL_TO,
REF.LOCAL_PATIENT_NUM,
REF.REFERRAL_REFERENCE_NUM,
TO_CHAR (REF.REFERRAL_DTE, 'DD/MM/YYYY') REFDATE,
TO_CHAR (OPC.CONTACT_DATE, 'DD/MM/YYYY') CONTACTDATE,
TO_DATE (OPC.CONTACT_DATE, 'DD/MM/YYYY') - TO_DATE (REF.REFERRAL_DTE, 'DD/MM/YYYY')
FROM
PAS.REFERRAL_DETAILS REF
FULL JOIN
PAS.OUTPATIENT_CONTACT OPC

[Code]...

This calculates the difference between a set date and a contact date, I only want it to return the minimum of the results set for each patient, how can this be achieved? At the moment it pulls off several records for each patient as there are several contacts.

View 2 Replies View Related

SQL & PL/SQL :: Clob For Return In Block

Aug 24, 2010

I want to execute a procedure and view its output. The procedurename is GET_clnts which has one of the parameters as clob where i am passing xmltype data to it.How can i execute the procedure to view the output. i can execute the SQL statement by itself, but not sure how to call a procedure from a plsql block..

DECLARE
clientid NUMBER;
OUT_RESULTSET clob;
BEGIN
GET_clnts ( clientid, OUT_RESULTSET);
DBMS_OUTPUT.Put_Line('');
END;
[code]...

View 2 Replies View Related

SQL & PL/SQL :: Return Only One Row Of Data On Query?

Nov 28, 2012

work_order unitid frommi tomi frompm topm
2666054111 06-154 77.000 85.000 77.370 null
2666054111 06-154 77.000 85.000 null 85.370
2666054111 06-154 77.000 85.000 null null

I used select distinct(work_order) to come up with the three different possible scenarios the problem is that i need all this information on a single row

work_order unitid frommi tomi frompm topm
2666054111 06-154 77.000 85.000 77.370 85.370

this is a conversion for distance. when i get this to work properly, it will generate reports on thousands of work orders with their converted distance markers.

View 3 Replies View Related

SQL & PL/SQL :: Return Column Name Corresponding To Values

Apr 28, 2011

I want to search a some values in oracle table and then return the corresponding column names.

View 1 Replies View Related







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