Getting Top-N Query To Work As Sub-select In Larger Query?

Mar 10, 2012

Is there a technique to getting a Top-N query to work as a sub-select in a larger query -or- is there another way to generate Top-N like results that works as a sub-select?

Background:

We have a large query that is being used to build an export from a legacy HR system to a new one. Amount the data needed in the export is the employees primary phone number.

The legacy HR system allows multiple phone numbers to be stored in a simple table structure:

SELECT emp_id, phone_type, phone_number
FROM employee_phones

emp_idphone_typephone_number
------- --------------- -------------------
46021CELL2222222222
46021HOME1111111111
46021WORK3333333333

The new HR system does allow for multiple phone numbers, however they need a primary phone number identified and stored with the employee master information. (Subsequent phone numbers get stored in alternate table.)

From a business perspective, we have decided that if they have a HOME phone in the legacy system that should be the primary in the new system, if no HOME phone, then WORK, if no WORK then CELL.

That can be represented as:

SELECT *
FROM employee_people_phones
WHERE emp_id = '46021'
ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')

emp_idphone_typephone_number
------- --------------- -------------------
46021HOME1111111111
46021WORK2222222222
46021CELL3333333333

Or similarly with Top N concept:

SELECT *
FROM (SELECT *
FROM employee_people_phones
WHERE emp_id = '46021'
ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')) results
WHERE ROWNUM = 1

emp_idphone_typephone_number
------- --------------- -------------------
46021HOME1111111111

Or really what I want in my export:

SELECT phone_number
FROM (SELECT phone_number
FROM employee_people_phones
WHERE emp_id = '46021'
ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')) results
WHERE ROWNUM = 1

phone_number
-------------------
1111111111

However, when the Top-N query is added as a sub-select in a larger query using the employee id from the larger query (WHERE emp_id = export.emp_id), it fails saying that �export.emp_id� is not a valid id.

(SELECT phone_number
FROM (SELECT phone_number
FROM employee_people_phones
WHERE emp_id = export.emp_id
ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')) results
WHERE ROWNUM = 1)

1.Any way around this? Is it possible to put a Top-N (with a WHERE clause using data from the main query) in a sub-select?

2.Any alternatives (other than Top-N) to delivering a ROWNUM=1 result with a �custom� ORDER BY statement?

Other Notes: Yes, we know we could do two queries in the data conversion first deliver the bulk data to the target table, and then update with the phone numbers. However, for multiple reasons, that is less than desirable.

View 3 Replies


ADVERTISEMENT

Storing Select Query Result Into Array And Using It In Another Query?

Aug 7, 2009

I am looking to simplify the below query,

DELETE FROM A WHERE A1 IN (SELECT ID FROM B WHERE BID=0) OR A2 IN (SELECT ID FROM B WHERE BID=0)

Since both the inner queries are same,I want to extract out to a local variable and then use it.

Say,

Array var = SELECT ID FROM B WHERE BID=0;

And then ,

DELETE FROM A WHERE A1 IN (var) OR A2 IN (var)

How to do this using SQLPLUS?

View 8 Replies View Related

Forms :: How To Make Query Mode Work (f11)

Apr 2, 2012

I have created a custom form. I have field called Order No. When i query on Order no.. i should be able to pick order no as well as other fields related to it. how do i acheive this functionality. I mean the f11 and ctrl f11 querying functionality.

View 18 Replies View Related

Forms :: Radio Buttons Don't Work In ENTER-QUERY

Mar 11, 2010

I have a query form where I am attempting to add radio buttons for the user to specify if they want to see Active, Expired, or Both vendors. The existing form is a multirow database block and a single-row detail database block. Users query in the multirow block. I created a QRY_CONTROL non-database block with the radio group and a couple other variables. When they select a radio button, the WHEN-RADIO-CHANGED changes the default block's where clause.

My problem is that the radio buttons cannot be changed in ENTER-QUERY mode. If they cancel the query, the buttons work as expected. I put in a couple messages and the trigger isn't even firing, so I ruled out a code problem I think.I checked to make sure that the group properties are Enabled=true, mouse_navigable=true, query_allowed=true.

View 2 Replies View Related

Different Plans On Select Query

Dec 6, 2012

We're using Oracle 10g for development purposes. I have 2 same schema with approximately same data. I'm running same query on 2 schemas and I see that the first schema runs the query around 20sec and the 2.schema less than 1 sec. I thought first that there may be missing constraints or indexes but all are the same.

I checked the plan for the 2 schemas and I see that the plan is different.

Here is the query:
SELECT ccc.ComponentId AS "ComponentId", ccp.Code AS "ParentCode", ccc.Code AS "ChildCode" FROM CatalogueComponent ccp INNER JOIN CatalogueComponent ccc ON ccp.ComponentId = ccc.ParentComponentId WHERE ccc.ComponentId IN (20934777, 1594747)

I'm sending also the 2 output of PLAN results from 2 different schemas.

what should I do to fix the problem with the NAFBCA schema.

Attached File(s)

NAFBCA.JPG ( 57.49K )
Number of downloads: 6

NAFBCA2.JPG ( 60.36K )
Number of downloads: 4

View 1 Replies View Related

How To Add Alias In Select * Query

Feb 23, 2009

I want to select all the field from the table and want to put alias for only field i m just wondering is there any way to write a query something like

select *, emp as employee
from empMaster

here i want to display all the column but only with emp column i want to put alias.

View 1 Replies View Related

Select Query From Two Tables?

Jun 7, 2011

I have two tables a and b column names are id and date. Data type of date in a and b are different

table (a)

date id
10-DEC-01 2:08:39 PM 1
10-DEC-01 2:08:39 PM 2
10-JAN-02 10:10:22 PM 3
10-JAN-02 10:10:22 PM 4
10-JAN-02 10:10:22 PM 5

table (b)
date id
10-DEC-01 1
10-DEC-01 2

I need table b like this one

table (b)

date id
10-DEC-01 1
10-DEC-01 2
10-JAN-02 3
10-JAN-02 4
10-JAN-02 5

View 1 Replies View Related

SQL & PL/SQL :: Oracle Select Query?

Jul 22, 2010

SELECT DISTINCT PATIENT_ID , (
SELECT ROWNUM,APPOINTMENT_ID FROM
AppointmentDetailsHistory WHERE APPOINTMENT_STATUS_ID = 2
AND VISIT_TYPE_ID NOT IN (7) AND PATIENT_ID = ADH.PATIENT_ID

[code]...

MY SELECT query failing actually i am trying to convert sql server select into oracle.My sql server query is like this.

SELECT DISTINCT PATIENT_ID , (
SELECT TOP 1 APPOINTMENT_ID FROM
EMRAppointmentDetailsHistory WHERE APPOINTMENT_STATUS_ID = 2
AND VISIT_TYPE_ID NOT IN (7) AND PATIENT_ID = ADH.PATIENT_ID
ORDER BY PATIENT_ID, LAST_UPDATED_DATE ASC) AS FIRST_APPOINTMENT FROM
EMRAppointmentDetailsHistory ADH WHERE ADH.APPOINTMENT_STATUS_ID = 2
AND ADH.VISIT_TYPE_ID NOT IN (7)

View 14 Replies View Related

SQL & PL/SQL :: Different Plans On Select Query?

Dec 6, 2012

We're using Oracle 10g for development purposes.I have 2 same schemas with approximately same data.I'm running same query on 2 schemas and I see that the first schema runs the query around 20sec and the 2.schema less than 1 sec. I thoughtfirst that there may be missing constraints or indexes but all are the same.I checked the plan for the 2 schemas and I see that the plan is different.

Here is the query:
SELECT ccc.ComponentId AS "ComponentId", ccp.Code AS "ParentCode", ccc.Code AS "ChildCode" FROM CatalogueComponent ccp INNER JOIN CatalogueComponent ccc ON ccp.ComponentId = ccc.ParentComponentId WHERE ccc.ComponentId IN (20934777, 1594747)

I'm sending also the 2 output of PLAN results from 2 different schemas.

View 19 Replies View Related

SQL & PL/SQL :: How To Select And Delete At The Same Query

Jun 10, 2010

select SQL_CALC_FOUND_ROWS navl.xydata.Lat,
navl.xydata.Longi,
MIN(navl.xydata.GpsTime) as mn,
MAX(navl.xydata.GpsTime) as mx,
timediff(MAX(navl.xydata.GpsTime) ,MIN(navl.xydata.GpsTime) ) as idle,
[code]......

i want to delete WHERE mn = mx .

View 4 Replies View Related

PL/SQL :: Select Query On Emp Table

Jun 7, 2012

How to find display the o/p like manager name under dependent employess same like parent child relation ship on noraml emp table:

sample o/p:

name job
xx manger
yy sales
yy1 sales
aa manager
rr marketing
rr1 marketing

View 6 Replies View Related

PL/SQL :: Select Top Row In Single Query?

Sep 19, 2013

write a query to get the first row after order by clause using single query alone.Example:I can write following query to select first rowselect * from (selec * from t order by col1) where rownum = 1;But here I should not use inline view to get the result. Because my original requirement needs to use this query in select list and it needs to use a column (of a table from the FROM clause) in the where clause of inline query. Because there is restriction that we can not use the column (of a table from the FROM clause) more than one level of inline query.

View 6 Replies View Related

SQL & PL/SQL :: Select Query With Rownum

Nov 27, 2012

I am using this as a subquery within a large select statement.

(select NAME_LAST from person_name where person_id=enc.person_id and ROWNUM = 1 order by person_name_id desc) as PatFirstName

I am getting issues when i am doing rownum=1 with order by clause, what is teh right way.

when i use rownum < 2 without order y clause it is workign fine.

I would like to use order by clause.

View 2 Replies View Related

Create Table From Select Query?

Sep 13, 2004

Can we create a table from a Select query ?

View 5 Replies View Related

Select Query Not Fetching Records In 11g?

May 10, 2013

I have a Select query which is not fetching records in 11g (11.2.0.2.0) but working fine in 10g (10.2.0.4.0). The query is as below.

--CREATE TABLE t1 (col1 NUMBER, col2 VARCHAR2 (15 CHAR), flag varchar2(1))

--insert into t1(col1, col2, flag) values(1, 'a', 'Y');
--insert into t1(col1, col2, flag) values(2, 'b', 'N');

SELECT *
FROM t1 x
WHERE col1 = 1 AND col2 = 'a' -------------- condition1
AND 0 = -------------- condition2
NVL (
(SELECT COUNT (1)
FROM t1 y
WHERE y.flag = 'N'
AND x.col1 = y.col1
AND x.col2 = y.col2),0)--=0

When remove NVL function or change the condition by having AND NVL(SELECT) =0 the query working fine.

View 9 Replies View Related

Select Number Of Rows From Query?

Jun 13, 2007

In sql plus How do I get the number of distinct rows of a certain value?

for example

select group_number from records group by group_number

How would I query for the total number of group_numbers in this query?

View 2 Replies View Related

SQL & PL/SQL :: Insert Into Table A With Select Query On B And C?

Mar 22, 2013

I have a table A on dev with definition as TAble A(address,name) and the same table on Prod is defined as Table A(name,address).

my question is Ihave one package in that am trying to insert into this table as follows:

INSERT INTO A
SELECT b.name name,
a.address address,

[Code]....

so the query works on Prod but fails on Dev because column order is different.

I have 2 solutions:

1. I can mention column names in insert line and modify the query but tomorro some body changes again the definition of table A I need to change the query, so do I have solution in oracle sql that can handle the column order without specifying the column names in insert line.

so tomorrow On prod column order and on Dev column order is different though my sql should successfully execute.

View 5 Replies View Related

SQL & PL/SQL :: Query To Select Required Translators

Jul 13, 2010

i have a good query but I thought i know the solution but actually I didn't it's very simple and straight forward but i didn't catch the rope terminal to follow now I have the following code

CREATE TABLE TRANSLATORS
(Tr_code VARCHAR2(8),
Tr_name VARCHAR2(50),
Tr_age number(2),
Tr_location varchar2(25),
constraint PK_TR_CODE PRIMARY KEY(Tr_code)
);
[code]....

NOW I NEED A QUERY which will select the only translator who knows the languages written in where clause like if i specify two languages like 'italy' and 'english' the query should retrieve to me GH and SE also if i passed languages 'spainsh' and 'persia' it should return JH only

but if i passed languages like 'Italy' and 'Dutch' it should not return any thing (just : no rows selected)

View 14 Replies View Related

SQL & PL/SQL :: Select Some Attributes From That Object In Same Query

Oct 25, 2013

He had created an object type and wanted to populate it in a query. So far so good. But then he wanted to select some attributes from that object in the same query. Basically, he wanted to do something like this:

With his_view
As
( select object_type( attr1, attr2) theobj
From his_table
Where ...
)
Select theobj.attr1
From his_view

But somehow he was hitting ORA-00904: "THEOBJ"."ATTR1": invalid identifier over and over again.Here's a test script:

Create type mhe_type As Object( col1 Number
, col2 Varchar2(30)
)
/
-- Basic select
With mhe_view
[code]...

Specify a correlation name, which is alias for the table, view, materialized view, or subquery for evaluating the query. This alias is required if the select list references any object type attributes or object type methods. link...

View 1 Replies View Related

SQL & PL/SQL :: Select Single Query And GroupBy Together?

Jul 8, 2011

i have a problem in the following query. i need to fetch the rows such that i want to fetch all the records keeping "segment1" column as distinct and sum all of the corresponding "quantities" column.

select prha.segment1 --as requisition_no
,prha.creation_date
,sum(prla.quantity)
,prha.description

[code]...

i tried to use the partition technique. using partition solved the problem apperently. the sum function worked but redundancy in "segment1" column still persists. i used the sum function only to extract the distinct "segment1" column and summing its corresponding "quantity" column (only quantity column differs in the redundant rows...)

the second query was like:

SELECT prha.segment1,
prha.creation_date,
SUM(prla.quantity) OVER(PARTITION BY prha.segment1) AS qty,
prha.DESCRIPTION,

[code]...

View 1 Replies View Related

SQL & PL/SQL :: Create A Function Which Can Be Used In A Select Query?

Jun 11, 2008

I have a table structure like :-

Create table test(A varchar2(50),B NUMBER);

The data in that table is like that:-

A B
----------------------
2*3
2*4*5
4*5
column B contain no data.

I want to create a function which can be used in a select query,and the output should come like that :-

A B
----------------------
2*3 6
2*4*5 40
4*5 20

Means column B contains the resultant value of column A.And the above output should come through a select statement.You can use any function inside the select statement.

View 20 Replies View Related

SQL & PL/SQL :: Select * From One Table But Not All Tables In A Query

Oct 30, 2013

How does one select * from one table without selecting * from other tables that are included in a query? For example, if in the query below I want to view all fields in some_table, but not the fields from other_table, how do it?

select *
from some_table st,
other_table ot
where st.id = ot.id

View 15 Replies View Related

SQL & PL/SQL :: CTAS - Select Query In Database

Apr 12, 2011

I have created a table using a CTAS

E.g. :

CREATE TABLE temp1 AS
SELECT * FROM table1;

Now after the table got created, is there any place in the data base where the select query is stored using which the table got created? In brief, I would like to get the select query through which the table got created.

View 2 Replies View Related

SQL & PL/SQL :: Adding Inner-query Select To Group By?

Aug 30, 2011

I have this query

select EAG.AUDIT_NUMBER Audit_Nbr,
EAG.AUDITEE_NAME Grantee_Name ,
EAG.EIN Grantee_EIN_IRS,
EAG.AUDIT_ISSUE_DATE Audit_Issue_Date,
MAX(AUDT.derive_audit_progress_status(EAG.SYS_AUDIT_ID )) Audit_Clesed_Date,
EAG.OIG_DUE_DATE Six_Month_Due_Date,

[code].....

I want o add additional column to this

I added this sql

SELECT CASE
WHEN currentstep.step_id IN (100)
THEN currentstep.start_date
ELSE (SELECT start_date
FROM audt.os_historystep
WHERE ID =

[code].....

here is the completed query

select EAG.AUDIT_NUMBER Audit_Nbr,
(
SELECT CASE
WHEN currentstep.step_id IN (100)
THEN currentstep.start_date
ELSE (SELECT start_date
FROM audt.os_historystep

[code].....

when I try select from this query I get ORA-00904: "DATE1": invalid identifier.

how to add the new column in group by clause.

View 6 Replies View Related

SQL & PL/SQL :: Select Statement Query Calculation

Sep 28, 2011

I'm unable to create a select statement which calculates & brings the below fields :

- Act_Net_Last_Month (LastMonth)
- Act_Net_Same_Month_Last_Year (LM_LastYear)

My Table :

Acc_ID - Period_Name - Period_Year - Act_Net
000044 - Aug-2011 - 2011 - 4493
000044 - Aug-2010 - 2010 - 4300
000044 - Jul-2011 - 2011 - 4389
000044 - Jul-2010 - 2010 - 4266

Example :

Acc_ID - Period_Name - Period_Year - Act_Net - LastMonth - LM_LastYaer
000044 - Aug-2011 - 2011 - 4493 - 4389 - 4266

View 7 Replies View Related

Default In (order By) Select Query Different In 11g From 10g?

Nov 12, 2013

if when you are querying a table in 11g and you use the order by clause and there is more than one occurrences with the same values in the order by, if the 11g default is different than from 10g.

For instance. 

DECLARE MHBulk CURSOR FOR                          select invoice_nbr,               customer_nbr,                post_century,                post_yymmdd,     from CUSTOMERS     where customer_nbr = 1234     order by           post_century,           post_yymmdd; 

If you have more than one occurrence of the same customer_nbr, post_century, and post_yymmdd

it looks like the default order for how 11g retrieves the records is a bit different than the 10g default. 

View 9 Replies View Related

Multiple Select Statement In 1 Query?

Nov 19, 2012

I have following queries:-

#select name from v$database;
#select log_mode from v$database;
#select count(*)"INVALID_OBJECTS" from dba_objects where status='INVALID';
#select count(*) "INVALID_N/A_INDEXES" from dba_indexes where status!='VALID';
#select count(*)"Invalid Triggers" from user_objects where OBJECT_NAME like '%TRIGGERS%' and status='VALID';
#select count(*) "Broken Jobs" from dba_jobs where broken!='Y';
#select count(*) "Block Corruption" from v$database_block_corruption;

i want a table which can be generated just by select cmd and it will list the result of all the above queires as follow:-

DB_NAME ARCH_MOD INV_OBJ INV_IDX INV_TRG B_JOB BLK_CRP
---------- -------------------------------------- -------------------------------------- ---------- ---------- ---------- ----------
PROD NOARCHIVELOG 0 86 6 3 0

I mean to say i want multiple select queries into 1 table (note:- i m not saying to create a tables and then insert,update(using select from other tables), its just a sheel script that will fetch these record into a txt file)

View 7 Replies View Related

PL/SQL :: Perform DML Or DDL When Select Query Is In Progress?

Aug 27, 2012

Is it possible to perform a DML or DDL when a select query is in progress?

View 8 Replies View Related

Substitute Column Name From Variable For Select Query?

Jan 24, 2012

In a select query, I want to substitute the column name from a variable (In turn from an array of column names). Details are given below. I am getting an error as mentioned below.

Create table Test1(IDNumber(10),
Namevarchar2(100),
Descriptionvarchar2(1000),
Sitevarchar2(100));

Insert Into Test1 values(10,'Thomas','Manager','LA');
Insert Into Test1 values(11,'George','Chairman','LA');

declare
MTest1Test1%RowType;
str1VarChar2(1000):='Name';
MValue1Varchar2(100);
begin
Select * Into MTest1 from Test1 where ID=10;
str1:='Select MTest1.'||str1||' from dual';
execute immediate str1 into mvalue1;
dbms_output.put_line('mvalue1 '||mvalue1);
end;
/

I am getting the error:

ORA-00904: "MTEST1"."NAME": invalid identifier
ORA-06512: at line 9

View 5 Replies View Related

Using Array Values In IN Clause Of Select Query?

Sep 9, 2008

I have some issues in passing array values to IN clause.

I am passing a String Array from Java to PL\SQL and want to use the Array values in the IN CLAUSE of Select Query

cust_array is the Array
search_id VARCHAR2(1000);
search_id := '';
FOR j IN 1 .. cust_array.count
LOOP
IF (j != 1) THEN
search_id := search_id || ''',''' || cust_array(j) || ''';
ELSE
search_id := search_id || '''' || cust_array(j) || '''';
END IF;
END LOOP;

trying to form a string of below form: search_id '3211335201','3211335209','3211335279','3211335509'

and use the string search_id in the IN clause of the search Query select * from DPP_EMP where empl in (search_id)

but the query does not returns any result

When I try to hardcode the values in the query as below, its returing 4 rows

select * from DPP_EMP where empl in ('3211335201','3211335209','3211335279','3211335509')

How to achieve this (String to the IN clause) or is there a better way of passing the Array values to the IN clause

View 13 Replies View Related







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