SQL> select * from year;
YEAR YEARGOAL
1992 5000
1995 5000
1993 5000
1994 5500
[code]...
what would the query be toList the donor number and last name of donors who did not contribute in 1994, in order by donor number.
I have been trying to construct a query in Oracle that allows me to do the following:
For example if I have the data below:
EmpNo DOB SickDays
Alex 445 15/06/1985 7
Tom 778 22/08/1981 4
James 992 07/10/1978 5
I need to write and a query to lists the employee number and the amount of days sick that they have had and also add a column that compares the number of sick days to the average number of suck days by ALL employees.
I can calculate the average sick days etc, but It wont see to allow me to find the difference between that and the amount of sick days that each person has had. I have tried this many ways and have not been able to come up with a solution.
how do I count a list of number value eg 1,1,1,1,3,3,6,4 and find the one with maximum number which is 1
View 5 Replies View RelatedBelow is an overs implication of what I need to extract from a donor list and am having some difficulty pulling the correct targets. I need to pull a donor who has had only one topic in the past year.
I only want id number 100 and not 120 since 120 has three topics in the past year, I tried using not exists...etc and can't seem to get the donors with the one topic 'HC' that is being requested.
example:
create table Topic(Idnumber number(8),topic varchar2(4));
INSERT INTO Topic(idnumber,topic)
VALUES (100, 'HC')
/
INSERT INTO Topic(idnumber,topic)
VALUES (120, 'HC')
/
[code].......
IDNUMBER TOPI
---------- ----
100 HC
120 IRS
120 PRS
120 HC
SQL> select idnumber from topic where topic in('HC');
IDNUMBER
----------
100
120
SQL> select idnumber from topic where topic in ('HC') and topic not in('IRS','PRS');
IDNUMBER
----------
100
120
I need a query to get the below.
Source :
select * from test;
LVL
1
2
3
1
2
3
4
Output:
LVLSEQ
11
21
31
12
22
32
42
I need above to uniquely identify the set of data.
we use oracle 11g. 11.2. I have a situation where in a pl-sql package, there is a modification needs to be done . Every User is associated with destination codes. For eg, user1==>0101,0104 There is a destination_number and country field in the table1.Each of the destination number is associated with a country code and this combination is unique. for eg:
dest_code country_code 0101 CHINA 0202 UK 0101 Mexico 0104 Mexico
For example, if user1 logs in to the system, then pl sql logic will look if that dest_code is in a table1,If the dest_code is present in the table1, then that dest_code number wont be added to the dest_code list formed for that user. For example,if user1 got dest_codes 0101,0104 associated with him.IF 0101 is in exception table then 0101 and 0104wont be used to form dest_code list. If NOT, a dest_code list will formed like 0101||0104. I am able to form the logic like I have a cursor that loops for each dest_code.For each dest_code , it will get that dest_code into v_dest_code_no number type variable. open cursor fetch dest_code no into v_dest_code v_dummy number:=0;v_dest_code_list number:=0; LOOP v_dest_code_list:=v_dest_code ||v_dest_code ; v_dest_code :=0; END LOOP: How to make sure that for each dest_code set, if one of them is in table1, then the next number also will not be added to the list.
I have the following select query that works perfectly fine. Returns 25 rows based on the descending order of the price.But, I want add one more expression to this list of columns in this query (apart from customer_id).
the expression should look like Cust-01 for the first customer from the below query all the way to Cust-25 for the last customer.But how can I can generate 01 to 25 in oracle?
select customer_id from
(select customer_id from capitalPLAN
where member_status = 'MEMBER' AND customer_id NOT in ('156','201','1385','2125','3906','165')
order by price desc
)
where rownum <= 25
Actually m working on oracle 10g forms. I have a list item in which no. of records displayed is 10. I am populating this list item through a record group. I want when user select a value from first record then he should not be able to select the same value in further record of that list item.
View 12 Replies View Relatedwe are using database 11g.
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
i am getting one error while selecting the data from table.
SELECT interco_type, entity, chapter_entity, tran_currency, source_id_entity,
tran_id_entity, mirror_id, gaap_type, counterpart
FROM t_gri_reconid_dtl_agg_gcr;
or
select * from t_gri_reconid_dtl_agg_gcr where rownum = 1 ;
Error:- ORA-01795: maximum number of expression in a list is 1000 ( error is same in both the case )
When i'm sorting on a calculated field i'm getting the following error:
ORDER BY item must be the number of a SELECT-list expression
Ive a requirement which ive overlooked for a while now as below,
On my tabular form i have fields: Empid as Select List and when the value is picked from this Select List EmpID i want the associated/corresponding phone# displayed in its TEXT FIELD in that same row,
apex 4.1.1/Oracle 11gR2,
also i tried the below as a workaround but it doesnt fulfill my requirement, [URL]....
I am trying to generate a list of years, starting with 2010 and counting up through 5 years from the current date. For example, if it is currently 2013, the list would include 2010 through 2018. 2 years from now, I want the list to go from 2010 through 2020.
I am currently using
SELECT to_char(add_months(to_date('2010','YYYY'),12 * (rownum-1)),'YYYY')
FROM dual
CONNECT BY level <= 10to get a list with 10 entries.
How can I make this dynamic enough to return a variable number of records depending on the current year?
I want to write a query that lists last 11 days. I tried the following query using CONNECT BY...
SQL> SELECT MIN_DT + LEVEL - 1 DAYS FROM
(
SELECT TRUNC(SYSDATE -10) MIN_DT, TRUNC(SYSDATE) MAX_DT FROM DUAL
)
CONNECT BY LEVEL <= MAX_DT - MIN_DT + 1;
DAYS
--------------
20100514000000
For some reason it gives only the first day.. But this one works -
SQL> SELECT MIN_DT + LEVEL - 1 DAYS FROM
(
WITH MYDUAL AS (
SELECT 1 FROM DUAL
)
SELECT TRUNC(SYSDATE -10) MIN_DT, TRUNC(SYSDATE) MAX_DT FROM MYDUAL
)
CONNECT BY LEVEL <= MAX_DT - MIN_DT + 1;
DAYS
--------------
20100514000000
20100515000000
20100516000000
20100517000000
20100518000000
20100519000000
20100520000000
20100521000000
20100522000000
20100523000000
20100524000000
11 rows selected. I am not able to understand what is the difference between the two queries! Why is the second query able to do what the first query cannot?
I have table called Orders with lots of fields from Imports Fields are ConsigneeKey,Shipto,ProductCode. and another table called Product. Main fields are ProductCode,Price,Weight,StdCube. Now I have two list of Consineekey,One which is shipto X area and another is of Y area. I want to make query like separate list of Orders which is shipto X,Y and Common Products in Both X and Y. I will supply list of Consigneekey in where clause.
View 8 Replies View RelatedI wanted a query that will give me the list of views using three tables
when i fire
select * from dba_views where text like '%SELECT%';
it is giving me error as expected number got long.
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.
i want to make a list item from query..i have a form on emp
the list item is on deptno showing the department name its work fine the form are opened when the user enter the new empno they need a other department
the others users or same user are inserting the department from master form
user open the list item it will not show the inserted values until they have to reopen the form because the trigger code inserted on when new form instance but i need a refesh list when user click on list item without reopen form or any refersh button
I want to list the tables in a schema which contains a particular value or data.
View 2 Replies View RelatedI need to get multiple code values and put it into a variable which later need to pass into the where clause of an sql. But i am not getting any results even i pass those values in the variable of an where clause: below is my Procedure:
declare
TYPE crMain_record is RECORD (
v_code dummy.v_code%type,
n_no dummy.n_no%type,
[Code].....
END;"lv_character" is going to hold the multiple code values which i need to pass into whare clause of the above SQL: the totlal number of these mulitipe codes can be more then 50..
And lv_character values are commung from a setup table
lv_character varchar2(32767):= '('||''''||'COMMIS'||''''||' , '||''''||'AGY BUILDING BENS'||''''||')';
--And lv_character values are commung from a setup table.where "lv_character" holdes multipe code values...
And lv_character values are commung from a setup table and upper(d.v_code)in lv_characterif the
Its a simple query to retrieve data with the Order_ID.If the record is there its fine and can retrieve the data. But my query is to retrieve the list of order_id which has no records in database.
View 12 Replies View Relatedi am using Apex 3.2 ver.
i want to use below code in LOV select list
BEGIN
IF UPPER(:P23_SERVICE_TYPE) like 'GUIDE%' THEN
SELECT NAME D, CODE R FROM SPECIAL_SERV_MAS
WHERE NVL(ACTIVE_FLG,'N') = 'Y'
AND NVL(GUIDE_FLAG,'N') = 'Y'
and CITY_CODE LIKE NVL (:P23_CITY_CODE, '%')
[code]....
When i put this code in my LOV Select list Section then display me Error
Not Found The requested URL /pls/apex/f was not found on this server.
Oracle-Application-Server-10g/10.1.2.0.2 Oracle-HTTP-Server Server at tidevserv1 Port 7777
I am trying to create a tabular form based on a SQL query, that has a query-based select list with a where clause that references a column in the originating SQL query.
The situation is, I have a table that stores client_id, source_id and build_id, lets call it client_source. I have a second table, build_source, that contains source_id and build_id, with a one to many relationship between the two (source_id of 1 could have build_id of 1-7).
Using a tabular form, I want to select the corresponding build_id to be used in client_source, but the select list must only contain the build_id's for that rows particular source_id.
Here is an example of the SQL source of my tabular form;
select
s.ROWID,
s.CLIENT_ID,
s.SOURCE_ID,
APEX_ITEM.SELECT_LIST_FROM_QUERY(1, s.BUILD_ID,
'select b.build_id display, b.build_id return from
build_source b where b.source_id = s.SOURCE_ID ') lst
from client_source s
... what I am trying to achieve is that the source_id fields in bold match. When the query is built this way I get an 'invalid identifier' Oracle error on s.SOURCE_ID at runtime.
Is there some special tags that need to be used to reference the outside column?I am running on Application Express 4.1.0.00.32, on a Oracle 10g release 10.2.0.4.0 database.
I have this situation:
a tabular form with two "select list (query based LOV)" fields. The first one must affect elements in the second one. And so the second query based LOV field should has a "where attr1 = first_list_selected_value" in the query.
I created a "Select List" in the Shared Components area as follows:
My goal is to create a shared select list that can be used on any form for a particular Field.
IF :P10_FK_RSTA_CODE is NULL THEN
RETURN
'select col_description, col_code_pk
from CODE_TABLE
where col_active_ind = ''Y''';
[code].....
Which works great, but how can I make the select list work for the same column on a different form? Because :P10_FK_RSTA_CODE is hard-coded in the function, I can't reuse this Function from a different form. (I could rename the Field on every form to the same name, but I'm hoping there is a better way.)
Is there a way I can pass in a variable from the Field Item to make this dynamic where I can use it on any form?
create TABLE pan_number(pan CHAR(10) NOT NULL);
INSERT INTO pan_number(pan) VALUES('ABCDE1234A');
INSERT INTO pan_number(pan) VALUES('FGHIG5678F');
INSERT INTO pan_number(pan) VALUES('ABCDE12345');
INSERT INTO pan_number(pan) VALUES('ABCD1234A');
select * from pan_number;
now i need to validate valid PAN number which is of " The first 5 letters should be alphabets & last letter as alphabet & total length of PAN no. should be 10 digit"
i need to display valid PAN number , only first two rows are valid PAN numbers.
How can i print serial no of records in sql query? I know I can achieve this with selecting rownum but it gives wrong data if used with group by like below
select rownum,deptid from emp group by rownum,deptid order by rwnum
query to know number of columns in a table i.e.
if I want to know how many number of colums are present in a specific table then what would be the query.
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?
I have Table Data in format :-
--------------------------
ABC
ABC
ABC
XYZ
XYZ
and i have a requirement where I need the output in this format.
-------------
ABC 1
ABC 2
ABC 3
XYZ 1
XYZ 2
what query to be used for this
I need to find out that assume i have a table having 2 column
Num Name
1 Adam
2 Akanksha
1 barren
2 bosli
3 Benergee
4 Bhawna
3 anjani
I want a query as if A is there 2 times then there should be 1 then 2 then there is b coming in 4 places then it should be 1 2 3 4 and again there is anjani so 3 should be there as 1 and 2 in first 2 places and the num should be automatically generated number based on the count of the alphabets