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


ADVERTISEMENT

Return Minimum Duplicate Values

Dec 16, 2009

I am trying write a script that will return all values (based on the minimum tarif) from the Germany table for any duplicate values. Duplicate values are any values with the same UFI, ZC,limitid,depot. The German table also contains the fields tarif, city, supplier, etc.

Below is the script I have previously used to sort out duplicates. I have tried 50 different ways get it to return just lines for the minimum tariff but haven't been successful.

select *
from Germany t
where (ufi,zc,limitid,depot) in (
select ufi,zc,limitid,depot from (
select ufi,zc,limitid,depot, count(*) n
from Germany t
group by ufi,zc,limitid,depot)
where n<>1
)

View 4 Replies View Related

SQL & PL/SQL :: Dynamic Resultset For Linear Growing Columns In A Table?

May 7, 2013

Provided a sample table to determine the sales agents performance below.

Id AgenName Zone1 Zone2 Zone3 Zone4
1 ABC 90 85 65 55
2 SDD 45 67 78 90
3 ERF 85 30 52 45

If in any zone, the sales is less than 40, it should be failed.This can be determined using the below query

select Id, AgenName, Case when Zone1 < 40 or Zone2 < 40 or Zone3 < 40 or Zone4 < 40 Then 'Failed'
Else 'Eligible'
End as Flg_Comm
from sales_table;

But the problem is when the zone grows (zone5, zone6) every time the query requires to be added with or condition.

Is there a way to dynamically determine the result even when no. of columns grow (or added)?

View 1 Replies View Related

Enterprise Manager :: Install Grid Control 11gR1 - SQLException Null Resultset

May 19, 2011

I'm trying to install Enterprise Manager Grid Control 11gR1 - Windows2003 32. On the "Connect to Oracle Database" step 5 when installing a new Enterprise Manager System, I get an Error window that states:

"ERROR: SQLException null Resultset

Cause: Verifying whether database is valid for this install, using the given credentials has failed.

Possible Reasons:
1. Credentials provided may be incorrect
2. Listener may be down
3. Database may be down

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

PL/SQL :: Create_Date Must Be Set To Minimum Of All?

Jun 29, 2013

I have a table structure as follows Student(Id,First_Name, Last_Name, email, Contact, Address1 ,Address2, City, Edit_Date,Create_Date,Archived) 

Now if there is more than one row with same email the one with the latest edit date should be updated with missing fields by using same field value other rows (if the field is present in more than one row, the one with the next latest edit date is to be considered) and

the archived status of all rows with same email except this master row must be set to 1.The Create_Date must be set to the minimum of all the create_date values of rows with same email value 

View 2 Replies View Related

SQL & PL/SQL :: Getting Minimum Composite Key From Group By

Oct 11, 2011

I have the following case:

create table try_o
(pk1 NUMBER, pk2 NUMBER, fld1 VARCHAR2(10), fld2 NUMBER,
PRIMARY KEY (pk1, pk2));
insert ALL
into try_o values (1,1,'f1',5)
into try_o values (1,2,'f1',5)

[Code]....

I need to get the row that contains the least pk2 among the group of records with the same fld1 and fld2. but if I used

select min(pk1), min(pk2), fld1, fld2
from TRY_o
group by fld1, fld2;

I get a record that doesn't exists as the min(pk1) and min(pk2) are from different records. I need to get the min(pk1) and its corresponding pk2.

View 4 Replies View Related

Minimum Size For Db_recovery_file_dest_size

Apr 25, 2013

i am trying to enable flashback on in my database and i am getting the below error.

idle> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38708: not enough space for first flashback database log file

my db_recovery_file_dest_size is 1g. If i change it to 20g i am able to "alter database flashback on".

Why does a size of 1g give this proble? Any simple steps or recommendation that i am missing? Or is the size of the DB which impacts the db_recovery_file_dest_size also?

idle> select *from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE    11.1.0.7.0      Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

View 14 Replies View Related

ODP.NET :: Minimum Role For GetSchema

Apr 16, 2013

I am trying to find out what is the minimum Oracle User Role required for the GetSchema command to work using ODP.NET and the Oracle.DataAccess library.

Dim cn As New OracleConnection(ConnectionString) 'Oracle.DataAccess.Client.OracleConnection Return cn.GetSchema("Tables")

View 0 Replies View Related

SQL & PL/SQL :: Finding Nth Maximum Or Minimum From A Table?

Sep 17, 2011

how can i find nth maximum or minimum salary from a employee table? here i want only one row as output.

View 16 Replies View Related

SQL & PL/SQL :: Extract Minimum Report Time

Oct 1, 2013

I have the below scenario:

[u]Report_Time M I Ta[/u]
02-SEP-13X Y35167005
01-SEP-13X Y35931902
03-SEP-13X Y35931901

The output I am expecting from above is:

X Y 01-SEP-13 03-SEP-2013 35931902 35931901

II need to extract minimum report time , max report time and corresponding Ta.

View 11 Replies View Related

SQL & PL/SQL :: How To Find Minimum Salary Holders

Nov 7, 2012

in this query, i am stuck in this little query but cant get answer lets suppose

select * from emp;

EMPNO ENAME JOB SAL
---------------------- ---------- --------- ----------------------
7788 SCOTT ANALYST 3000
7902 FORD ANALYST 3000
7876 ADAMS CLERK 1100
7934 MILLER CLERK 1300
7900 JAMES CLERK 950

[Code]....

now if i want to see min salary takers group by job then i use

select x.job, min(x.sal), count(*) from emp x group by x.job;

JOB MIN(X.SAL) COUNT(*)
--------- ---------------------- ----------------------
CLERK1 800 1
SALESMAN 1000 5
CLERK 950 3
PRESIDENT 5000 1
MANAGER 2450 5
Developer 2975 1
ANALYST 3000 2

The above result give me minimum salary but total number of JOB holders, You can see only one SALEMAN getting 1000 but count show total number of SALESMAN. Similarly 3 MANAGERS are getting minimum and same salary but count show total number of MANAGERS.

My question is how can i get number of person on min salary?

Possibly my data should be like as this

JOB MIN(X.SAL) COUNT(*)
--------- ---------------------- ----------------------
CLERK1 800 1
SALESMAN 1000 1
CLERK 950 1
PRESIDENT 5000 1
MANAGER 2450 3
Developer 2975 1
ANALYST 3000 2

View 5 Replies View Related

Reports & Discoverer :: Select Query With Minimum Values

May 30, 2013

The prob is i want to display minimum intime and max outtime in idate against employee,report keep displaying multi inout records of an employees!

SELECT div.division,
DEP.department,
E.employeecode,
E.name empname,
DES.designation,
i.idate,
To_char (Min(i.intime), 'HH:MI:SS AM'),
To_char (Max(I.outtime), 'HH:MI:SS AM'),
Round(i.btime / 60),
e.shift
[code]....

View 7 Replies View Related

Backup & Recovery :: Minimum Files Required To Open DB

Dec 26, 2012

what are the minimum files which are required to open the db ? take it as this

I have oracle installed in c drive. I have placed 3 datafile related to one tablespace say test: two in D and one in E. I have place 3 datafile related to tablespace say orcl : two in D and one in E.

my undo is in E scenario is that my E drive crashed . db is in no archive mode . no backup is there. to be more worst db shuts down abnormally. I want my db open with data of c drive and D drive.

View 3 Replies View Related

SQL & PL/SQL :: Distinct And Individual Records In Single Or Minimum Pass(es)

Feb 21, 2012

I have following data

select rowid,object_name,object_type from do;

ROWID OBJECT_NAME OBJECT_TYPE
------------------ ------------------------------ ------------------
AAA/wuAAHAAAW73AAA CON$ TABLE
AAA/wuAAHAAAW73AAB I_COL2 INDEX
AAA/wuAAHAAAW73AAC I_USER# INDEX
AAA/wuAAHAAAW73AAD C_TS# CLUSTER
AAA/wuAAHAAAW73AAE I_OBJ# INDEX
AAA/wuAAHAAAW73AAF I_CON2 INDEX

6 rows selected.

I want it in the following manner

select rowid,object_name,object_type from do;

ROWID OBJECT_NAME OBJECT_TYPEGROUP
------------------ ------------------------------ ---------------------------
AAA/wuAAHAAAW73AAA CON$ TABLE2
AAA/wuAAHAAAW73AAB I_COL2 INDEX1
AAA/wuAAHAAAW73AAC I_USER# INDEX1
AAA/wuAAHAAAW73AAD C_TS# CLUSTER1
AAA/wuAAHAAAW73AAE I_OBJ# INDEX1
AAA/wuAAHAAAW73AAF I_CON2 INDEX1

6 rows selected.

Here the GROUP is changing when the data type is changing and thus for same data type the group shall remain the same As of now this is achieved by - first selecting distinct object_type, then it's mod(rownum,<input variable) and this result of 'mod' is doing the grouping which is the retrieved along with rowid of all individual record

Present query is as following and it is not much efficient

SELECT DO.ROWID RWID, RID
FROM DO,
(
SELECT OT,CASE MOD(ROWNUM,:v) WHEN 0 THEN :v ELSE MOD(ROWNUM,:v) end as RID
FROM(
(SELECT DISTINCT OBJECT_TYPE OT

[code]....

I tried using sequence with cycle but it gave different results. Even I tried following but it did not gave satisfactory results

select d.rowid,d.object_type,x.x1 from do d,(select distinct object_type,mod(rownum,:v) x1 from do where
created>'01-jan-2008')x where d.object_type=x.object_type and created>'01-jan-2008';

In short the query needs Distinct with mod(rownum) and individual records in a single pass The mod(rownum) i.e. group shall change when the object_type changes but then shall remain constant through out the particular object_type.

View 9 Replies View Related

Minimum Extent Error Creating Tables In Empty Tablespaces

Dec 30, 2010

I am trying to restore to a backup instance on a backup server. When I try to recreate the tables I keep getting ORA-01659: unable to allocate MINEXTENTS. The tablespaces and datafiles on both servers show as the same size in OEM.

I have dropped all tables and OEM shows tablespaces are empty. Then I run a script to recreate all tables. Most of the tables don't get created because their TS is full. After the script to recreate all tables runs, the main tablespaces are full, more full than on the production machine. I have also tried ALTER TABLESPACE xxx COALESCE; on each tablespace right after dropping all tables and before recreating them to reclaim free space. Why is it full? I've only dropped and created the tables, there shouldn't be any data in them yet.

ORA-01659: unable to allocate MINEXTENTS beyond 2 in tablespace PLUS_T...The backup instance was already there, all I did was drop the tables. Here's what I ran on prod to build a script to recreate the tables on backup server. Got it off Burleson somewhere.

SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name) ||'; '
FROM USER_TABLES u;

View 4 Replies View Related

Server Administration :: Minimum Datafile Size Required For Creating Non-system Tablespace?

May 21, 2007

what is the Minimum datafile size required for creating a non-system tablespace?

I am trying to create a tablespace by giving the syntax like,

SQL> create tablespace t1
2 datafile '/home/data/t1.dbf' size 72k;
create tablespace t1
*
ERROR at line 1:
ORA-03214: File Size specified is smaller than minimum required

SQL> create tablespace t1
2 datafile '/home/data/t1.dbf' size 73k;

Tablespace created.

The blocksize for my database is 4096, as i have heard that the minimum size of the datafile is decided by blocksize, but i want to know that how it is calculated as by giving the above syntax the other values will be default. I am trying the syntax in oracle 9.2.0.1.0 version.

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

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

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

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

SQL & PL/SQL :: How To Return More Data From Function

Nov 14, 2011

Following is the scenario:

CREATE OR REPLACE TYPE OBJ IS OBJECT
(
TEST_ID NUMBER(9),
TEST_DESC VARCHAR(30)
)
/

[Code]..

I WANT USED VALUE TEST_ID AND TEST_DESC THE EXISTING IN FUNCTION FN_MY_DATA WITH A VARIABLES :

DECLARE
X NUMBER(9);
Y VARCHAR(30);
BEGIN
X := -- VALUE TEST_ID EXISTING IN FN_MY_DATA;
Y := -- VALUE TEST_DESC EXISTING IN FN_MY_DATA;
END;

View 3 Replies View Related







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