PL/SQL :: Query To Get List Of Years?
Feb 15, 2013
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?
View 1 Replies
ADVERTISEMENT
Nov 2, 2012
I have two table APPLICATION_1 and APPLICATION_2, where user's login date is captured.There will be multiple records for each user.Same user may or may not be present in both tables.
The requirement is to find those users who have not logged-in in the last 3 years.
-- Test Case
DROP TABLE application_1;
DROP TABLE application_2;
CREATE TABLE application_1
(
seq NUMBER ,
user_id VARCHAR2(30),
[code].....
Using the below query I'm able to get the desired output.
SELECT user_id, MAX(login_date) last_login_date FROM (
SELECT user_id, MAX(login_date) login_date FROM application_1 GROUP BY user_id
UNION ALL
SELECT user_id, MAX(login_date) login_date FROM application_2 GROUP BY user_id
) GROUP BY user_id
[code].....
View 10 Replies
View Related
Jul 22, 2009
I'm trying to do a pivot query in oracle to get the years from a column and make a separate column for each. I found an example of the code to use on the internet and i changed it for my own tables but i'm getting errors. Namely a "FROM keyword not where expected" error at the beginning of the 'avg(...' statements.
I have copied the code used in
select stud_id, 2006, 2007, 2008, 2009
from (
select stud_id,
avg(case when year=2006 then ((present/poss)*100) else null end) 2006,
avg(case when year=2007 then ((present/poss)*100) else null end) 2007,
avg(case when year=2008 then ((present/poss)*100) else null end) 2008,
avg(case when year=2009 then ((present/poss)*100) else null end) 2009
from attendance.vw_all_attendance_perc
group by stud_id
);
View 11 Replies
View Related
May 24, 2010
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?
View 5 Replies
View Related
Mar 16, 2012
for making the query for following data,when we give start and end date then query need to calculate number of years.
if less than one year then return 0
if exact one year then return 1
if exact 1.5 years (18 months) then return 2
if exact 2 years (24 months) then also return 2
if exact 2.5 years (30 months) then return 3
if exact 3 years (36 months) then also return 3
if exact 3.5 years (42 months) then return 4
if exact 4 years (48 months) then also return 4
and so.
also we need to add leap year 1 day if exist in start and end date.
YearSalary
11
1.52
22
2.53
33
3.54
44
4.55
55
[code]....
View 15 Replies
View Related
May 11, 2012
Our "client" is delivering data that contains an "age" field. However, they've put the AGE in DAYS instead of years, while our system can only work with years. So I needed to convert the age to years.
We are getting 2 parameters: LOWAGE and HIGHAGE (both in days). However, for the same "test" (laboratory test), we are also getting some other records
eg:
TEST 1 lowage = 0 upperage = 5 OTHER values = xxx
TEST 1 lowage = 6 upperage = 100 OTHER values = yyy
So if the person would be 5 years or less, the values xxx would apply. If they are 6 years and older, the yyy values apply. Now, the problem is that we are not getting this data in years, but in days. And whichever function I try, the upperage from the first test also seems to be equal to the lowerage from the second test. And this gives an error, because both would apply to this person....
I've tried stuff like:
age/365
age/365.25 (leap years)
trunc(months_between(sysdate+ranges.lowerage,sysdate)/12)
View 6 Replies
View Related
Mar 1, 2010
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 Related
Mar 7, 2010
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.
View 2 Replies
View Related
Aug 16, 2011
I 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.
View 2 Replies
View Related
Apr 5, 2011
I require to find the years between any given date range. For example what are the years between the dates '01/12/2010' and '01/02/2012'? Answer must be '2010,2011,2012'. how to code the query for this result?
View 2 Replies
View Related
Aug 8, 2010
I have a question to write a pl/sql for Chinese zodiac but i cant do it, even i Google it i cant find any solution..the question is as below:
The Chinese zodiac associates birth years with the following; animals:
Birth Year Animal
1924,1936.1948,1960,1972,1984,1996 Rat
1925,1937,1949,1961,1973,1985,1997 Cow
1926,1938,1950,1962,1974,1986,1998 Tiger
1927,1939,1951,1963,1975,1987,1999 Rabbit
1928,1940,1952.1964,1976,1988,2000 Dragon
1929,1941,1953,1965,1977,1989,2001 Snake
1930,1942,1954,1966.1978,1990,2002 Horse
1931,1943,1955.1967,1979,1991,2003 Sheep
1932,1944,1956,1968,1980,1992,2004 Monkey
1933,1945,1957,1969,1981,1993,2005 Chicken
1934.1946,1958,1970,1982,1994,2006 Dog
1935,1947,1959,1971,1983,1995,2007 Pig
Write a command to declare a date variable named birth_date, and assign to it your birth date. Use an IF/ELS1F structure to test every year and determine the animal associated with your birth year. Then display your birth year and the associated animal name. For example, the program would display the following output for someone born in 1984:
I was born in 1984, which is the year of the Rat. Declare and use additional variables as needed.
View 18 Replies
View Related
Jul 30, 2012
I would like to convert a number to years & months.
e.g. 119 = 9 years and 11 months
This would be displayed as 9.11
Is there an oracle function or sql that can calculate this value,
View 8 Replies
View Related
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
Apr 19, 2010
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
View 4 Replies
View Related
Feb 3, 2012
I want to list the tables in a schema which contains a particular value or data.
View 2 Replies
View Related
Sep 18, 2012
I 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
View 3 Replies
View Related
Jul 20, 2010
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 Related
Mar 26, 2011
I am trying to set a item to system date + 18 years. What type of formula or where to put such a thing. i tried putting- select add_months(sysdate,216) from dual; into the calculation-formula in property palette with no success.
View 1 Replies
View Related
Apr 19, 2009
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.
View 3 Replies
View Related
Jun 11, 2012
i 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
View 3 Replies
View Related
May 10, 2013
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.
View 2 Replies
View Related
Aug 24, 2012
I need to calcuate balance for last two cooprative years on input date.
example
if input date is-->"01-AUG-2012" if (month >=7) -->then i need to calculate from 01-july-2010 to 30-june-2011 and 01-july-2011 to 30-june-2012 and 01-july-2012 to input date.
if input date is-->"01-june-2012" if (month<7) -->then i need to calculate from 01-july-2009 to 30-june-2010 and 01-july-2010 to 30-june-2011 and 01-july-2011 to input date.
how to achieve this logic
View 4 Replies
View Related
Dec 27, 2012
For Just learning purpose This is an example found in text book but while i try to execute it fails..I am trying to set Boolean flag to true if the hire_date is greater than 5 years otherwise boolean flag to false
DELARE
v_Hire_date date :='12-Dec-2005';
v_five_years BOOLEAN;
BEGIN
IF
[code].....
View 17 Replies
View Related
Jul 10, 2012
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.
View 3 Replies
View Related
Sep 4, 2012
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?
View 1 Replies
View Related
Sep 27, 2011
I am creating the Dynamic list but when i am compiling the form it gives the compilation error "No list elements defined for the list item".
I can eliminate it by entering the dummy list element but this dummy value will be displayed at form run time.
View 1 Replies
View Related
Dec 14, 2011
Im trying to list the products list of a client grouped by type of the product. Ex:
product type
prod.A acid
prod.B flavour
prod.C acid
prod.D cleaner
prod.E flavour
I want to list something as:
Acid
Prod.A
Prod.C
Cleaner
prod.D
Flavour
prod.B
prod.E
View 1 Replies
View Related
Oct 30, 2011
DECLARE
CURSOR GRP IS
SELECT RowNum rn, Letter_Group_ID||'-'||A_Desc AName,Letter_Group_ID
FROM Hrs_Group;
BEGIN
Clear_list('Letter_Group_ID');
FOR I IN GRP LOOP
Add_List_Element('Letter_Group_ID',I.rn,I.AName,I.Letter_Group_ID);
end loop;
END;
FRM-30351: No list elements defined for list item.
List LETTER_GROUP_ID
View 4 Replies
View Related
Sep 27, 2007
I am a novice in oracle
I have 2 columns in my table
->Col1 with experience in years entered as an integer
->Col2 with current date
I need to add another column as a date value adn for that i need to subtract Currentdate-Col1 when i tried currentdate-Col1 it just subtracted the days i need the formula to subtract years and give a date
I have worked in DB2 and all u need to do there was add the keyword years at the end but in oracle the same does not work
View 8 Replies
View Related
Apr 22, 2013
I just want to list and group all my tables that are linked together by constraints. I just want my tables to be able to be listed together as one particular database. my tables are , CUSTOMER, ORDER_INFO, ORDER_LINE, PRODUCT. They're all linked together by way of constraint and I want to list and print them all together as one DB. HOW DO I put them all in one schema and then also list them all together and print/illustrate them as one. also, I tried to import them into their own scheme but i ran into a series of probs regaurding the .dmp file being read.
View 2 Replies
View Related
