SQL & PL/SQL :: SELECT Query For Displaying Aggregate Values?

Jul 1, 2011

i have table with following data.

yearquartersales Revenue
2004Q145678
2004Q287456
2004Q356732
2004Q4120986
2005Q12398
2005Q23900
2005Q36522
2005Q42763

I want the output in following way.tell me the select query for this

yearquarterSales Revenue
2004 Q145678
Q287456
Q356732
Q4120986
2004 total Sales310852
2005 Q12398

[code]....

View 4 Replies


ADVERTISEMENT

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

Storing List Of Values Which Are Returned By A Select Query?

Oct 9, 2012

I have a requirement like getting list of values from one table and inserting them into another table.I have tried with sub querying but didn't worked out because the select query is returning multiple values.

how to proceed further and the ways how can I write this requirement.

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

Application Express :: Displaying Multiple Values In Auto-complete Item

Aug 15, 2013

I am creating multiple entries for auto populate item as shown in this jquery plugin link below but i am trying to change this so values come from table query. how can i achieve this even using web services in apex 4.1.1 but i am trying NOT to use remote url or external file?   

[URL]......

View 3 Replies View Related

Client Tools :: Passing Variable And Displaying Select Data In TOAD?

Apr 22, 2010

All I want to do is run a procedure (DO_H_RUN) that returns a number into "v1" and then use that variable to return the result sets in three selects. This is all in TOAD. If I do not have a where clause, the code executes fine (allowing me to see the data in multiple Grid tabs)! But, I want to filter the rows.

I've tried doing a number of different things (finding all sorts of stuff on the Web) like declaring cursor variables and the like but after spinning on this for a couple of hours, I'm stuck.

Here's my attempt (the names have been changed to protect the innocent!):

declare
v1 Numeric;
BEGIN
DO_H_RUN('Me', v1);
--DBMS_OUTPUT.PUT_LINE(v1); --This line works okay!
select * from h_run where h_run_id=v1; --NO GO

[code]....

View 10 Replies View Related

SQL & PL/SQL :: Displaying Multiple Row Values As Multiple Column And Row Values

May 4, 2010

find the Test Case below.

--Creation of Table

create table tb1
(ID number(4),
event varchar2(20),
vdate date);

--Inserting Values into the Table.

INSERT ALL INTO tb1 (ID, event, vdate) VALUES (01, 'V1', '01-JAN-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V2', '02-FEB-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V3', '04-MAR-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V4', '03-APR-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V5', '05-MAY-2009')

[Code]...

--Selecting data from Table.

SELECT * FROM TB1;

ID EVENT VDATE
---------- -------------------- ---------
1 V1 01-JAN-09
1 V2 02-FEB-09
1 V3 04-MAR-09
1 V4 03-APR-09
1 V5 05-MAY-09
2 V1 01-JAN-10
2 V2 02-FEB-10
2 V3 04-MAR-10
2 V4 03-APR-10
2 V5 05-MAY-10

10 rows selected.

how can i display the data as below format using Oracle 9i SQL.

IDV1 V2 V3 V4 V5
--- ---------------- ------------ --------------- -------------- ------------
11-Jan-092-Feb-094-Mar-093-Apr-095-May-09
21-Jan-102-Feb-104-Mar-103-Apr-105-May-10

View 4 Replies View Related

Displaying Only Max Value Of The Count In Query

Jun 18, 2007

I' m doing a query on multiple tables willing to get only top scorers from a certain round. Here's the relevant part of relation:

SOCCER_TEAM(TEAMID, NAME, CITY)
PLAYER (PLAYERID, NAME_SURNAME, DOB, TEAMID)
GAME_STATS(ROUNDID, GAMEID, TIME, PLAYERID, STATTYPE)

TIME is No between 1-90 representing the minute of the game
STATTYPE is IN('GOAL', 'OWN GOAL', 'RED', 'YELLOW')

Here's my sql code for the query:

SELECT ROUNDID, NAME_SURNAME, NAME, COUNT(STATTYPE)
FROM GAME_STATS, PLAYER, SOCCER_TEAM
WHERE PLAYER.PLAYERID IN (SELECT GAME_STATS.PLAYERID FROM GAME_STATS WHERE STATTYPE='GOAL' AND PLAYER.TEAMID = SOCCER_TEAM.TEAMID)
AND STATTYPE='GOAL'
AND GAME_STATS.PLAYERID = PLAYER.PLAYERID
GROUP BY ROUNDID, NAME_SURNAME, NAME
ORDER BY ROUNDID, COUNT(STATTYPE) DESC

This results in correctly displaying all scorers from all the rounds, yet I haven't been able to construct the HAVING clause to display ONLY the top scorers from each round (there can be multiple of them scoring equal top amount of goals and I need to show them all)

p.s. I have underlined primary keys, while foreign keys are in cursive, if it is of any relevance

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

Select Into With Null Values?

Apr 20, 2007

i have a stored proc where i am selecting a value into a variable like so:

SELECT FUNCTION
INTO V_FUNCTION
FROM FUNCTION_TABLE
WHERE FUNCTION = P_INPUT;

Now, my problem lies in where there is no value returned (oracle will throw an error).

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

Select Records With Same Values Of Field?

Oct 9, 2007

there are some data in the table que_history (seqnbr is the key), e.g.

SEQNBR DN SL_TIME
20070927003668 (024)2272 AD182040 2007-9-27 15:15:00
20070928001343 (024)2272 AD182040 2007-9-28 9:55:14
20070928001624 (024)2272 AD182040 2007-9-28 10:30:06
20070928000910 (024)25672 AD000002 2007-9-28 9:06:59
20070928001288 (024)25672 AD000002 2007-9-28 9:49:13
20070923003834 (024)2585 AD210076 2007-9-23 17:15:13
20070923003890 (024)2585 AD210076 2007-9-23 17:23:54
20071001001593 (024)2589 AD000018 2007-10-1 11:54:39
20071003002814 (024)2589 AD000018 2007-10-3 16:53:52
20070923003320 (024)8831 AD000110 2007-9-23 15:24:39

I wanted to use this SQL to get the records ( dn is the same and the sl_time's interval is 600minutes) .

select A.* from que_history A,que_history B
where A.dn=B.dn and A.seqnbr<>B.seqnbr
and (A.sl_time-B.sl_time)*24*60 between -600 and 600
order by A.dn;

but the result is not the right.

View 3 Replies View Related

Forms :: LOV / Auto Select Values

Apr 13, 2012

I have an LOV on my form which holds a list of course units for a student to select and insert. However some units on the LOV must be COMPULSORY(not optional).... so i was wondering is there a way to have these auto selected from the LOV?

View 4 Replies View Related

SQL & PL/SQL :: Select From List Of Literal Values?

Jul 18, 2011

Is there a way to loop through a list of literal values.

For instance
create table car(
name varchar2(11),
passengers int,
price int
);

insert into car values ('fiat',1,1000);
insert into car values ('bmw',2,2500)
insert into car values ('ford',2,1500)
insert into car values ('ferrari',4,5000)

select
max(price)
from car
where passengers=1

How can i in a single query do this for where passengers = 1
then passengers = 2
then passengers = 3 etc
where i have a list of possible values for passengers.

Just to update I realise this can be done with

select
name,
max(price)
from car
where passengers in (1,2,3)
group by name

but in just wanted to know if there is a way of iterating through a literal list in tsql

View 1 Replies View Related

SQL & PL/SQL :: Select Column Values Into Array

Sep 10, 2013

Is there any way in PL/SQL to select the values from all columns of a table record into an array?

For example:

C1|C2|C3
0 |1 |2

v_array(0) value is 0
v_array(1) values is 1
v_array(2) values is 2

or

v_array(C1) value is 0
v_array(C2) values is 1
v_array(C3) values is 2

But i need to do this without mention the column names, something like: SELECT * FROM TABLE WHERE id=1 INTO v_array;

View 10 Replies View Related

Spatial :: Select Repeating Values

Sep 7, 2012

some sample data in my point geometry table.

every POLYID has two rows with NAME value, i need to select the two rows if NAME is same for a given POLYID.

example: POLYID 4351 has same name N, then i need to select two rows with PILYID 4351.

POLYID POINTID           NAME
-----------------------------------------------------------------
4348 5763           N
4348 5764           F
4351 5741           N
4351 5756           N
4367 5721           M7

[Code]....

View 6 Replies View Related

How To Select Data Where Non-numeric Values Omitted

Nov 13, 2008

I am attempting to use the following select to get a specific emplid. However, the ps_names table contains some alphabetic characters. I want to only focus on the emplid's that contains numbers. Is there a way to modify the following select to do this?

bubbagumpshrimp
"ORA-01722: invalid number"
SELECT x.y
from (select PERCENTILE_CONT(0.10) WITHIN GROUP (ORDER BY to_number(emplid)) over () y
from PS_NAMES
where emplid > '000000000' and emplid < '999999999') x
where rownum = 1;

View 6 Replies View Related

SQL & PL/SQL :: Select Only Rows Where Certain Column Repeating Values

Mar 6, 2012

I am trying to come up with a sql select statement that provides all rows for employees with 2 or more cities.

with sample_table as (
select 'John' name,'city' ValueType,'Toronto' Value from dual union all
select 'John' name,'city' ValueType,'Vancouver' Value from dual union all
select 'Susan' name,'city' ValueType,'Toronto' Value from dual union all
select 'Susan' name,'city' ValueType,'Seattle' Value from dual union all
select 'Susan' name,'age' ValueType,30 Value from dual union all
select 'Susan' name,'city' ValueType,'Atlanta' Value from dual union all

[Code]...

NAME VALUETYPE VALUE
----------- ------------- ------------
John City Toronto
John City Vancouver
Susan City Toronto
Susan City Seattle
Susan Age 30
Susan City Atlanta
David City Chicago
David age 35
David Status married
David City Dallas

The above code is just to describe the sample table and the desired result set. Please note that Mary is not on the result set since she has no city assigned to her. Also Julia is not on the result set since she only has one city assigned to her. The others are there because they had at least 2 cities assigned to them.

I need the sql syntax that would return this result set.

View 6 Replies View Related

SQL & PL/SQL :: How To Select Only Records That Have Multiple Values For A Column

Nov 21, 2011

I'm trying to select id's in a table that have 2 certain values for another column. Example below explains:

idCoupon Type
123Amount
123Percent
456Amount
789Percent

I would like to write a sql statement that would select all rows where id=123, because id 123 has both coupon types "Amount" and "Percent". So the result set of the sql statement would look like:

idCoupon Type
123Amount
123Percent

View 6 Replies View Related

SQL & PL/SQL :: Select Date Values From A Table Between Two Dates?

Feb 16, 2010

Oracle 10g

In a table I have a column update_date and its type is DATE. Sample values from this column are as follows. I am using the following query to select all update_date lie between sysdate and sysdate-90.

select update_date from table1
where update_date between sysdate and sysdate-90

The above query retrun no data even data is there in the table for this range.

Update_date

11-FEB-10
08-FEB-10
08-FEB-10
08-FEB-10
08-FEB-10

[code]...

View 7 Replies View Related

SQL & PL/SQL :: Select Numeric Values From A Varchar Column

Feb 5, 2011

select numeric values from a varchar column

For Example:

select * from t1 ;

ID
----------
00300
ABCXY
04230
xyzab

i need to fetch only numeric values from column id

My output should be

00300
04230

View 8 Replies View Related

SQL & PL/SQL :: Select Multiple Values Into Single Column

Oct 5, 2011

I have following tables with data as under:

table1: table2:
column1 (char) column1 (char) column2 (num)
A A 10
B A 20
C B 15
D C 12
E D 25
D 9

I need to generate output as :

column1 column2
A A10, A20
B B15
C C12
D D25,D9
E null

Is there anyway to achieve this thru simple SELECT ...and if not, then thru any PL/SQL construct..?

View 5 Replies View Related

PL/SQL :: Total Users Who Select Value From Q1 Group By Values

Dec 5, 2012

i have a table below; sql> desc css_survey

SURVEY_ID NOT NULL NUMBER(5)
USER_ID NOT NULL VARCHAR2(15)
ACADEMIC_SEMESTER VARCHAR2(25)
Q1 NUMBER(1)
Q2 NUMBER(1)

below are the records

survey_id user_id academic_semester q1 q2

1 1 2012 2 3
2 2 2012 2 3
3 3 2012 3 4
4 4 2012 3 4
5 6 2012 2 4

the Q1 and Q2 could have values 1,2,3,4,5 thats means total are 5 questions. i need to know the total users who select value from q1 group by the values from 1..5 the toal_users who select value from q2 values( a group).i need the following result

total_users question_Q1_select question_Q2_select
3 2
2 3
3 4
2 3

View 2 Replies View Related

SQL & PL/SQL :: Function To Select Column Values Separated By Comma?

May 23, 2012

I need to write a function which will take table name as input and should return all the columns separated by coma (,).

For example I have a table product as

PROD_ID PROD_NAME FAMILY_ID
------------------------------------
100006Acetaminophen100005
100013Simvastatin100007
100014Ezetimibe100008
100015Simvastatin+Ezetimibe Oral Family100009
100003Abacavir100003
100007Amlodipine100006
100001Cetirizine HCl Oral Solution100001

My function should return the output as

100006,Acetaminophen,100005
100013,Simvastatin,100007
100014,Ezetimibe,100008
100015,Simvastatin+Ezetimibe Oral Family,100009
100003,Abacavir,100003
100007,Amlodipine,100006
100001,Cetirizine HCl Oral Solution,100001

Is there any inbuilt function available?

View 10 Replies View Related

PL/SQL :: Select Records Based On First N Distinct Values Of Column

Sep 25, 2012

I need to write a query in plsql to select records for first 3 distinct values of a single column (below example, ID )and all the rows for next 3 distinct values of the column and so on till the end of count of distinct values of a column.

eg:
ID name age
1 abc 10
1 def 20
2 ghi 10
2 jkl 20
2 mno 60
3 pqr 10
4 rst 10
4 tuv 10
5 vwx 10
6 xyz 10
6 hij 10
7 lmn 10
.
.
.
so on... (till some count)
Result should be
Query 1 should result --->
ID name age
1 abc 10
1 def 20
2 ghi 10
2 jkl 20
2 mno 60
3 pqr 10

query 2 should result -->
4 rst 10
4 tuv 10
5 vwx 10
6 xyz 10
6 hij 10

query 3 should result -->
7 lmn 10
.
.
9 .. ..
so on..

How to write a query for this inside a loop.

View 5 Replies View Related

Reports & Discoverer :: Select List Of Values From Parameter Form?

Jun 9, 2012

I am trying to select multiple values from a parameter form based on a select statement.

I created the parameter and write the select statement under list-of-value property However what I want is to let users choose multiple values from the select statement not only one value.

View 1 Replies View Related

Forms :: How To Select 1st Record From The Duplicate Values In Table Without Using Rownum And Rowid

Apr 23, 2010

how to select 1st record from duplicate vales in a table.

If we created one table with out primary key column In form in search block have uwi value and top_depth value when i enter uwi and top_depth value then when i click search button then it will display all values in master block.

but here duplicate values r there.

SQL> select rownum,uwi,top_depth,base_depth,test_start_date from well_pre_header;

ROWNUM UWI TOP_DEPTH BASE_DEPTH TEST_STAR
---------- ---------------- ---------- ---------- ---------
1 100 453.05 458.08 09-SEP-10
2 100 200 288 23-AUG-00
3 1001 200 289 25-AUG-01
4 1001 200 201 24-MAY-87

if uwi = 1001 and top_depth=200 and i will click search button it should be display 3 record & when i click next button then it will show 4th record.

View 3 Replies View Related

SQL & PL/SQL :: DML Query To Interchange Row Values

Feb 24, 2010

I need a DML Query to interchange row values

for example: i had a table called Tab1.there are 3 columns in that table

Tab1:

id name salary
----------------------
1 raj 5000
2 ram 7000
3 ravi 10000

i need query to display like below

id name salary
----------------------
1 ravi 5000
2 raj 7000
3 ram 10000

One DML Statement to get the above result.

View 2 Replies View Related

SQL & PL/SQL :: Aggregate Between Two Dates

Jul 13, 2010

I need to aggregate the data based the two dates criteria.

I have two tables

1. Table1
Number Date1 Date2
1 10-Jun-2010 30-Jun-2010
2 10-Feb-2010 30-Feb-2010
----------------------------------
2. Table2
Number Date Revenue
1 11-Jun-2010 100
1 09-Jun-2010 100
1 12-Jun-2010 100
2 11-Feb-2010 100
2 12-Feb-2010 100
2 13-Feb-2010 100
......................
......................
So on

Output:

Number Revenue Date2
1 200 30-Jun-2010
2 300 30-Feb-2010

View 14 Replies View Related

Aggregate Two Columns

Oct 25, 2011

I have two table and I want to merge them

TERMS_TABLE
ID | TERMS
309 | 'hardware'
309 | 'software'
309 | 'computer'

TFIDF_TABLE
ID | TERMS
309 |'computer,phone,mp3....'

Now I want to add TERMS column of TERMS_TABLE to terms column of TFIDF_TABLE but If TFIDF_TABLE already contains TERMS of TERMS_TABLE then I should not insert this term to the NEW_TFIDF_TABLE , like that

result should be:

NEW_TFIDF_TABLE
ID | TERMS
309 |'computer,phone,mp3....,hardware,software'

How can I do that ?

View 1 Replies View Related







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