SQL & PL/SQL :: Query For Top N Max

SQL & PL/SQL :: Union Query Need To Add Extra Dummy Field To One Side Of Query

Dec 8, 2005

I have inherited a query that union alls 2 select statements, I added a further field to one of the select statements ( a date field). However I need to add another dummy field to the 2nd select statement so the union query marries up I have tried to do this by simply adding a

select
'date_on'
to add a field called date on populated by 'date_on' (the name of the column in the first query)

however when I run the union query i get the error Ora-01790 expression must have same datatype as corresponding expression.

View 6 Replies View Related

SQL & PL/SQL :: Create View From Dynamic Query (or Function Returning Query)

Dec 5, 2012

I have a dynamic query stored in a function that returns a customized SQL statement depending on the environment it is running in. I would like to create a Materialized View that uses this dynamic query.

View 1 Replies View Related

Reports & Discoverer :: Excluding SQL Query Data When Linked With XML Query?

Apr 26, 2013

I have data in a table and another in XML file,I used SQL query to retrive the data placed on the table, and link this query with XML query that retrieves the data stored in the xml file. The data stored in the table and xml file sharing a key field, but the xml contents are less than what in the table.I want to show only the data shared between the two queries, how can I do that?

e.g.:

Table emp:

e_id | e_name | e_sal
023 | John | 6000
143 | Tom | 9000
876 | Chi | 4000
987 | Alen | 7800

XML File

<e_id>
143
876

So, I want the output to be:

e_id | e_name | e_sal | e_fee
143 | Tom | 9000 | 300
876 | Chi | 4000 | 100

View 2 Replies View Related

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

SQL & PL/SQL :: Oracle Query Joining A Table With Existing Query?

Jun 19, 2012

I have the following four tables with the following structures Table A

ColA1 ColA2 ColA3 ColA4 ColA5 AA 100 CC DD EE

Table B

ColB1 ColB2 ColB3 ColB4 ColB5 AA 100 40452 A9 CDE

when these two tables were joined like the following:

Select colA1,ColA2, ColA3, ColA4, ColB3,ColB4, ColB5 from table A Left outer join (select ColB3, ColB4, ColB5 from table B where colB3 = (select max(colB3) from table B ) on (colA1 = colB1 and ColA2 = col B2)

Now i have to join the next table C with table B

Table C structure is

ColD1 ColD2 ColD3 Desc1 A9 Executive Desc1 A7 Engineer

I have the common column such as ColD2 and colB4 to get the Col D3

how do i join the existing query + join between table b and table c?

View 4 Replies View Related

Forms :: F11(Query Mode) And Execute Query In Oracle?

Jul 17, 2011

how to achieve F11(Query mode) and Execute Query in Oracle Forms?

View 1 Replies View Related

SQL & PL/SQL :: Query Using DBLink Returns More Rows Than Direct Query?

Apr 6, 2010

I have a query that is pulling back more rows when I use the dblink than when I hit the linked database directly.

For example:

select
x,y,z
from
mytable@dblink

returns 788,324 rows

while
select
x,y,z
from
mytable

returns 712,102 rows

It's the exact same query, with the only difference being the dblink. It's not pulling the data into a cursor or array, it's a simple, straightforward query on a remote database.

View 10 Replies View Related

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

SQL & PL/SQL :: How To Read Field From Query In Another Query

Sep 19, 2010

I am having a Select query(below Query1) and I want to use one column(sum(col4)) from this Select query to be displayed in another Select query(Query 2). how to display this.

Query 1 :-
select a.col1,a.col2,b.col3,sum(b.col4)
from tab a, tab b
where a.key1=b.key1 and a.key2=b.key2
group by a.col1,a.col2,b.col3

Query 2 :-
select a.col1,a.col2,b.col3,sum(b.col6)
from tab a, tab b
where a.key1=b.key1 and a.key2=b.key2
group by a.col1,a.col2,b.col3,b.col5

View 4 Replies View Related

PL/SQL :: Converting Inner Query To Sub Query

Sep 18, 2012

This query is written in inner join, can any one try to write using sub query.

SELECT B.CNO
FROM CUSTEN A
INNER JOIN ORDS B
ON A.CNO = B.CNO
AND A.PRNO = B.PRNO

[Code]...

View 4 Replies View Related

SQL & PL/SQL :: Divide Count From One Query By Count From Another Query

May 24, 2010

I have the folloiwng two queries:

Query_1: select count(*) yy from table1;
Query_2: select count(*) zz from table2;

I need to compute the following:

var:=(yy/zz)*100

How can I achieve this in a single query?

View 3 Replies View Related

How To Get COUNT On Query

Mar 4, 2009

I have this query and I want to get the COUNT:

SELECT first_np AS n_p FROM dvc
UNION
SELECT second_np AS n_p FROM dvc
UNION
SELECT n_p FROM dc;

This returns one column which is the n_p; how do I get the count of n_p?

View 2 Replies View Related

Query In 10g Oracle

Apr 18, 2008

I am facing problem with a select query in oracle 10g database from vb.net.It was working for oracle 9. The select statement I have written is as follows

Str=" select UCC.table_name, UCC.constraint_name, UCC.column_name, UCC.position, UC.constraint_type " & "from USER_CONS_COLUMNS UCC,USER_CONSTRAINTS UC " & "where (UCC.constraint_name = UC.constraint_name) " & "and UC.constraint_type = 'P' " & "and UCC.table_name = " & " '" & TableName & "'"

View 2 Replies View Related

One Query In 3 Conditions?

Jun 25, 2013

0 down vote favorite

I have one table in database that contains 3 foreign keys to another tables(this three tables name are: manager,worker and employee). in each row only one foreign key is filled.I need to write one query that with attention which column of fk is filled in where clause specified condition is performed. I write simple query in jpa but doesn't work properly

select b from allEmployees b where b.manager.name= :name OR b.worker.name = :name OR b.employee.name= :name

View 1 Replies View Related

Get A Query Of Each Sequence?

Jul 24, 2010

How do I get a query of each sequence and who has the permissions to it?

View 1 Replies View Related

How To See Query Plan

May 12, 2013

How to use see the query plan.

View 1 Replies View Related

How To Get Daily Max (Value) Query

Dec 18, 2012

I am looking to get the maximum value for every 24 hour period for a month. So for example my date range can be defined by...

select to_date('&date','mm yyyy')-1 + level as DateRange
from dual
connect by level <= '&days'

...where I can provide the first date of the month and number of days in the month or a lesser value if less time is required. So, the results of the above query plus 24 for the range. I thought a some Googling would provide me what I needed, but my search came up empty.

I was hoping to do something like this...

select utctime, max(value) from table where utctime between.

View 3 Replies View Related

Generating XML Using SQL Query?

Oct 13, 2011

I am facing the following issue while generating xml using a sql query. I get the below given table using a query.

CODE ID MARK
==================================
1 4 2331 809
2 4 1772 802
3 4 2331 845
4 5 2331 804
5 5 2331 800
6 5 2210 801

I need to generate the below given xml using a query

<data>
<CODE>4</CODE>
<IDS>
<ID>2331</ID>
<ID>1772</ID>
</IDS>
<MARKS>

[code].....

NOTe: IDS which are distinct needs to be displayed. ALL MARKS should be displayed though there are duplicates.

View 1 Replies View Related

Query To Use Joins

Mar 11, 2009

SELECT so.* FROM shipping_order so WHERE (so.submitter = 20)
OR (so.requestor_id IN (SELECT poc.objid FROM point_of_contact poc WHERE poc.ain = 20))
OR so.objid IN (SELECT ats.shipping_order_id FROM ac_to_so ats WHERE (ats.access_control_id IN (selectac. objid FROM access_control ac WHERE ac.ain = 20
OR ac.group IN ('buyers', 'managers'))))

rewrite this query to use joins. That would greatly simplify my sql query building code. The ids, objids, submitter, ain are numeric and group is a varchar.

View 1 Replies View Related

Query In SQL Plus (Oracle)

Feb 24, 2009

"Determine what departments are in each of the 4 regions. That is, what are the names of the departments that reside in each of the regions." So basically it wants me to list the departments in each region.

This is what the tables look like that it could possibly be drawn from...

Regions
region_id
region_name

Departments
department_id
department_name
manager_id
location_id

Locations
location_id
street_address
postal_code
city
state_province
country_id

Countries
country_id
country_name
region_id

View 3 Replies View Related

SQL & PL/SQL :: Query Row To Column

Mar 4, 2013

query for below requiremnet :-

I have table A having column name Varchar2(10), Seq Number ,Address varchar2(20)

Data in table as
name SeqAddress
A1bangalore
A2karnataka
A3India
B1Mumbai
B2Maharastra
B3India

I need to write query to get below Output

Abangalore,karnataka,India
BMumbai,Maharastra,India

I can not use any inbulit function of oracle like "SYS_CONNECT_BY_PATH", "LIST_AGGR" or any other function , even can not use any user defined function.Need to write only SQL to get this result.

how can we get above result

View 6 Replies View Related

SQL & PL/SQL :: Query With %type?

Sep 18, 2011

I have a query in this code?

declare
old_name varchar2(20) not null:='stephen';
new_name old_name%type;
begin
dbms_output.put_line(new_name);
end;

will new_name variable will inherit the datatype only or the default value as well?The output of this code will be stephen or not?

View 7 Replies View Related

SQL & PL/SQL :: Query With Where Condition

Jun 12, 2013

I have EMPLOYEE table that have 3 records with EMP_ID 1, 2, 3. Now I want to run below query

select emp_id from employee where emp_id in (1, 2, 3, 4, 5);

It will return only 3 records but i want those records also which is not available in employee table. Is this possible without using another table or creating another table. Actually I don't have enough privileges to create table.

& want output like below

EMP_ID
1
2
3
4 Not Found
5 Not Found

Here emp_id 4, 5 is not available in employee table, but query should return those value also with comments like "Not Found"

View 6 Replies View Related

SQL & PL/SQL :: How To Do Conditional Query

Mar 22, 2010

how to do a conditional range in a query. For example, I have two tables:

table A:
a1 char(1)
a2 char(1)

table B:
b1 char(1)
b2 int

In my query I want to range on a different value in the b2 field depending on what is in the fields in the A table. For example, if the a1 field is Y, I want to look up the record in B with a b2=20. If a2 is Y, I want to look up b2=5. Otherwise I want to look up b2=1. I attempted the following:

select b1 from b, a where b2=(if a1='Y' then 20; elsif a2='Y' then 5; else 1)

but that doesn't work.

View 8 Replies View Related

SQL & PL/SQL :: How To Run Query Between 2 Schemas

Jan 8, 2013

Is there any way to run a select command between 2 different schemas without using DB links ?

View 1 Replies View Related

SQL & PL/SQL :: Using N In Query Type

Aug 23, 2011

what is the use of using 'n' in a query type as mentioned below:

select * from scott.emp a where n =

View 11 Replies View Related

SQL & PL/SQL :: Query For Previous Day And Day Before

Jan 15, 2013

The main condition in SQL is like this.

SELECT TO_DATE (TO_CHAR (doc_date, 'MON-YY'), 'MON-YY') "INV_MTH",
SUM (inv_amt) INV_TOTAL
FROM table_x
WHERE doc_date BETWEEN TRUNC (SYSDATE, 'YYYY')
AND LAST_DAY (
ADD_MONTHS (TRUNC (SYSDATE, 'YYYY'), 11)
);

My Output from if run in JAN as of now 16 Jan.

INV_MTHINV_TOTAL
Jan-136260830.42

I want an sql until previous day of that month for example 15 Jan and another sql until day before previous day of that month for example 14 Jan.

View 15 Replies View Related

SQL & PL/SQL :: Matrix Query

Oct 24, 2010

i want to write a query in the below format

score days days days
0-20 20-40 40-60

0-200 * * *
200-400 * * *
400-above * * *

* --> no: of matches

test_1 and test_2 are two tables , and in test_1, "score" and "matchno:" are columns and in test_2, "days" is column. in score values are 0 to 1000 and in days values are 0-100

View 3 Replies View Related

Query MP3 ID3 Information?

Apr 25, 2007

Is it possible to query a mp3 file's ID3 information, or the properties where you can put in artist, album, etc. information?

View 1 Replies View Related