Im supposed to fetch first names, last names and age of some people based on their birthdate. In my code so far i manage to get the names and the birthdates but what i actually need is a name and lastname and age for example 65 years.
here is the code :
SET SERVEROUTPUT ON
DECLARE
CURSOR C_kundålder IS
SELECT FNAMN,ENAMN,PNR
FROM BILÄGARE;
v_fnamn bilägare.fnamn%TYPE;
[code].....
I got a issue with a query to fetch records between two dates for fixed timings
Date
From 29-09-2011 to 04-10-2011
Time
From 00:00:00hrs to 08:00:00hrs
I tried the below queries, it doesnt work
select a.detectorid,sum(b.totalvolume),a.updatetime,a.averagespeed from traffic_data a left outer join volume_data b on a.traffic_id=b.traffic_data_id
where pollinterval=1 and detectorid=�AIDC_0154� and updatetime between to_date(�29-aug-2011:00:00:00�,�DD-MON-YYYY:HH24:MI:SS�)
[code]...
I have around 1 million users in my table. I currently have their birth date in the table and for reporting purposes i want to put them into three different columns.
For example, For User A, DOB is 01/01/2005,
User B, DOB is 01/01/2002,
user C,DOB is 01/01/1998.
user D, DOB is 01/01/1990
I want a view like below.
Cat1. Cat2. Cat3. User
"0-10" '0-5' '0-2' 'USER A'
"0-10" '0-5' '3-5' 'USER B'
"0-10" '6-10' '8-10' 'USER C'
"11-20" '16-20' '16-17' 'USER D'
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.
need to write a program for date of birth is always less than sysdate, using exception handling
View 10 Replies View RelatedFRM-40501: ORACLE error: unable to reserve record for update or delete.
ORA-24374: define not done before fetch or execute and fetch
My master-detail form has single canvas. For both blocks, master and detail, two tables joined together in each. One table to be updated, second table has some info for reference (query only).
I am getting these errors when in detail block the item from LOV is selected for existing record. This does not happen for new record inserted in detail block.
create or replace PROCEDURE newprocedur(outname OUT VARCHAR2,outroll OUT NUMBER) AS
CURSOR c1 IS
select Name,Rollno,Section from emp;
BEGIN
Open c1;
fetch c1 into outname,outroll;
Here out of 3 columns in cursor is it possible to fetch only two columns using FETCH like i did above?
I want to find the dates which have a date plus with in 2 days after this date. I mean group by 3 days each even the date i missing between two days. Actualy I want to find the start date where the employ was missing on job.
Basic concept is employes have allowed to use 10 personal leaves of a year. Each leave can be use for maximum 3 days.
If employ did not come on the job for one day or two days or three days, it shoul be count as ONE personal leave. And If employ is missing at job for four or five days, it should be count as 2 personal leaves.
seq date
------------------------------
101.01.10
205.01.10
306.01.10
410.01.10
512.01.10
613.01.10
714.01.10
815.01.10
916.01.10
1018.01.10
1119.01.10
1220.01.10
1321.01.10
1423.01.10
1526.01.10
1627.01.10
1729.01.10
1831.01.10
The result should be (Don't use Pl/Sql)
seq date
------------------------------
101.01.10
205.01.10
310.01.10
413.01.10
516.01.10
619.01.10
723.01.10
826.01.10
929.01.10
After finding these days I want to select the starting date of 5th personal leave. (which is 16.01.10).
I am not a expert of using SQL, but I think it could be possible with using partitioning a table on the givin reslult and further partition the reslut on rownum() as rn and the using case statement where rn = 5.
Split a date into new dates according to black out dates!
Here is my tables:
CREATE TABLE travel
(
start_date,
end_date
)
AS
SELECT DATE '0000-01-01', DATE '9999-12-31' FROM DUAL;
[code]....
I have lets say a "travel date" and black out dates. I will split the travel date into pieces according to the black out dates.
Note: Travel Date can be between 0000-01-01 - 9999 12 31
Sample:
Travel Date:
Travel | START DATE | END DATE
T | 2011 01 04 | 2011 12 11
Black Out Dates:
BO | START DATE | END DATE
A | 2010 11 01 | 2011 02 11
B | 2011 01 20 | 2011 02 15
C | 2011 03 13 | 2011 04 10
D | 2011 03 20 | 2011 06 29
Excepted Result:
New Travel | START DATE | END DATE
X1 | 2011 02 16 | 2011 03 12
X2 | 2011 06 30 | 2011 12 11
Visually:
Travel Date : -----[--------------------------]--
A : --[------]-------------------------
B : ------[---]------------------------
C : --------------[---]----------------
D : ----------------[------]-----------
Result :
X1 : -----------[--]--------------------
X2 : -----------------------[--------]--
Sample 2:
Travel Date : -[--------------------------------]--
BO Date A : ----[------]-------------------------
BO Date B : -------------------------[---]-------
BO Date C : ----------------[---]----------------
BO Date D : ------------------[------]-----------
Result X1 : -[-]-------------------------------
Result X2 : -----------[--]--------------------
Result X3 : -----------------------------[--]--
How can I do it using PL SQL ?
How can I select all of the dates between two dates? For example, given the start date 12/2/2003 and the end date 12/5/2003 I want to return:
12/2/2003
12/3/2003
12/4/2003
12/5/2003
Is there a built in function for this? Is there a way for a function to return multiple rows? It has to be a function because I need to use it within other SQL statements.
Table structure:
table x
x1 x2 (Columns)
1 x
2 y
1 z
now i need to o/p like this
1 x,z
2 y
how to fetch data in above format.
I want to write a SELECT query on the data which are collected in a PLSQL table which is having 4 columns.
Looping through all the records in the PLSQL table will not get my requirement. Because I need to group the data based on two columns and need to fetch the count of groups.
Is there any way to query that PLSQL table?
what ios wrong in the following code
create or replace type testobj as object(col1 number);
create or replace type tabtest as table of testobj;
create or replace procedure proc(a out tabtest) is
cursor c is
[code]..
the view from where we can fetch information for a foreign key viz. reference table, referenced columns etc.
View 3 Replies View Relatedhow to get name of coloumn and table name which is being modified in trigger
View 8 Replies View Relatedwhen i run this code i get a invalid number error
create or replace
PROCEDURE BULK_REJECT(System_name VARCHAR2)
as
MDM_run_id VARCHAR2(14);
V_sql clob;
cursor Job_metric is
select rowid_job,system_name, table_display_name, run_status
[code]....
In my sql query, how can i fetch the row with max row count? the query has around 10 columns.
View 2 Replies View RelatedOS : widows 2003
Oracle 9i release 2
I need to Fetch 1 lakh rows from table (all rows) in fastest way.
How to do it?
I need to fetch the data from a particular column and display it as a row of data.
For example,
Table
Col1 col2 col3
1 6 10
1 7 11
1 8 12
2 6 18
2 7 null
2 8 19
Data should be diplayed using col 2 as partition as below
col1 col3 col3 col3
1 10 11 12
2 18 null 19
Note: I have also referred to this link [URL]....
I have created two tab pages.when i change tab then it should be display automatically all the records in that tab without pressing any.
View 1 Replies View Relatedi am fetching one record based on date but not able to fetch the data.
in my table pdate column is there with date datatype and value is in that is:15-OCT-12 so i am fetching empid based on dates.
select empid from masterprocessdailydata where pdate=to_date('15/OCT/12','dd/MM/yy'); but not getting the empid.
create table test_exp (oid number, ioid number, status varchar2(20));
Below are the insert statements.
Insert into TEST_EXP (OID, IOID, STATUS) Values (1, 100, 'NEW');
Insert into TEST_EXP (OID, IOID, STATUS) Values (1, 101, 'DISCO');
Insert into TEST_EXP (OID, IOID, STATUS) Values (1, 102, 'CANCELLED');
Insert into TEST_EXP (OID, IOID, STATUS) Values (2, 103, 'NEW');
Insert into TEST_EXP (OID, IOID, STATUS) Values (3, 104, 'DISCO');
Insert into TEST_EXP (OID, IOID, STATUS) Values (4, 105, 'DISCO');
Insert into TEST_EXP (OID, IOID, STATUS) Values (4, 106, 'NEW');
Insert into TEST_EXP (OID, IOID, STATUS) Values (5, 107, 'NEW');
Insert into TEST_EXP (OID, IOID, STATUS) Values (5, 108, 'CANCELLED');
[code]...
Now my problem is we should fetch the data based on the below rules
If an OID contains 2 IOIDs for which there is a NEW and DISCO status attached, then fetch the 2 records
If an OID has only 1 of these status, then ignore the same
If an OID has none of the 2 status, then ignore the same.
The expected output will be like below:
OID|IOID|STATUS
1 |100 |NEW
1 |101 |DISCO
4 |105 |DISCO
4 |106 |NEW
I want to fetch the data through the cursor and cursor is getting the value of group_code through the variable 'a'. but when i am writing the code like this it is not coming.
My code is like this :
declare
a varchar2(400):='';
cursor c1 is select ref_no,ref_code,company_id from stock_detail where company_id=:global.company_id
[Code]....
i am running one query which is here
INSERT INTO shiftsample (Empid, PPDate, Inpunch, outpunch)( SELECT Emp_ID, PDate, In_Punch, Out_Punch FROM ProcessDailyData WHERE PDate = to_char(2012-10-15,'yyyy-MM-dd') AND Emp_ID = '00000001' );
in this query pdate has timestamp datatype and in shift sample ppdate column has date type. so i am not able to insert value from process daily data table.
getting this error.
SQL Error: ORA-01481: invalid number format model
01481. 00000 - "invalid number format model"
*Cause: The user is attempting to either convert a number to a string
via TO_CHAR or a string to a number via TO_NUMBER and has
supplied an invalid number format model parameter.
declare
cursor c1 is select distinct edt, regno, name, desgn, form_no, form_status, recd_on
from ol_registration_vu
where RECD_ON between :control.REC_FROM and :control.REC_TO ;
[Code]....
this coding giving me only one record i.e the last record of the table, whereas the table has 50 records. how can i get all records from the table in the form datablock.
This is my data
ID TIME_IDVCL_IDIDLE_END_TIME START_AT
351601834326910110/29/2009 1:18 10/29/2009 1:18
351601837056910110/29/2009 1:51 10/29/2009 1:51
351601838106910110/29/2009 2:39 10/29/2009 2:39
351601840626910110/29/2009 3:04 10/29/2009 3:04
351601841466910110/29/2009 3:09 10/29/2009 3:09
I want to minus 1st row of START_AT from 2nd row of END_TIME means (10/29/2009 1:51 - 10/29/2009 1:18)
i want to load all rows of single columns into one variable. After that, that variable will use in any side of the pl sql block.
SET serveroutput ON
DECLARE
CURSOR CUR_DATA
IS
SELECT DAS_SECURITY_CODE FROM SECURITY_TYPE WHERE ASSET_TYPE='DERIVATIVES';
rec_data CUR_DATA%rowtype;
[Code]....
how to resolve this. above query,variable holds last value which fetched from cursor.
I have the following piece of
CREATE OR REPLACE PROCEDURE COMP_RECORDS
IS
l_query VARCHAR2 (10000) := '';
CURSOR TBL1
IS
SELECT TABLE_NAME, COLUMN_NAME FROM COLS_TO_COMP WHERE TABLE_NAME='ACE_HIST';
TBL1_REC APP.COLS_TO_COMP%rowtype;
[Code]..
However I am getting an ORA-00923 exception with message as "FROM keyword not found where expected". know if I can/cannot use a cursor to fetch column names for a table?
We upload a file on our library on weekly basis. I wanted to fetch the latest uploaded file path/URL from a folder through PL/SQL, but not sure if it's possible.
View 12 Replies View Relatedcreate or replace function get_date(nn date)
return date as age date;
BEGIN
age := sysdate;
END;
i want to return that value in front end using callable statament..