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]....
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
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.
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]....
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]......
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]....
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
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
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?
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).
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.
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.
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 RelatedIs 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
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;
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]....
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;
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.
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
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]...
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
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..?
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
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?
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.
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.
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.
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.
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
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 ?