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'
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;
I need to display dynamically a bunch of categories and I need the SQL algorithm to determine their size and names.
So, I have a table with one column and a variable number of records and I need to display them in GUI in pages, displaying also the page name which should be something like short name for first item to short name of last item in category.
Maximum items per page in 10 but I don't want to simply display blocks of 10 records. If the name of the 8th item starts with B and the name of the 9th item starts with C I want the first page to contain only the first 8 records. Minimum accepted items per page is 8, maximum is 10. The split should be based on the first 2 characters. Page name should be something like: first 4 chars of first item in category - first 4 chars of the last item in category.
I need to display dynamically a bunch of categories and I need the SQL algorithm to determine their size and names.
So, I have a table with one column and a variable number of records and I need to display them in GUI in pages, displaying also the page name which should be something like short name for first item to short name of last item in category.
Maximum items per page in 10 but I don't want to simply display blocks of 10 records. If the name of the 8th item starts with B and the name of the 9th item starts with C I want the first page to contain only the first 8 records. Minimum accepted items per page is 8, maximum is 10. The split should be based on the first 2 characters. Page name should be something like: first 4 chars of first item in category - first 4 chars of the last item in category.
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.
I have to display count of employees that belongs to different categories.
is the situatio There is a category table CATEGORY with three columns (PK,NAME,TREEPOSITION) and we have categories A, B, C these three categories can further have sub-categories so the treeposition for the sub categories will be followed by their root category with _ 'symbol'
Now I have table for the employees with 3 columns (pk,name,category_id), where employees.category_id=category.pk So I want to calculate the number of employees in each category or sub-category.
since the number of categories will be large and each will be having different names so going through names will be bad option left is grouping through the treepostion the problem is I cant use like using IN for the TREEPOSITION. .
Version : 4.1.1, I have a tabular form on a DB table. One of the columns is a date field. When the user hits the "add Row" button on the tabular form, I want the Date field to be defaulted to sysdate. Here is what I have tried so far,
1. Created a "hidden" item P1_SYSDATE and populated the default value with sysdate. After this, under the DB tabular report date field, I used default type - Item/application on this page and entered P1_sYSDATE
2. Instead of populating the default value of the P1_SYSDATE hidden item, I created a before regions process and added
:P1_SYSDATE := sysdate
and added P1_SYSDATE to default type of the tabular date field with default type as "ITem/application on this page.
I get the error
ORA-01790: expression must have same datatype as corresponding expression
I tried to_Char(sysdate,'dd-mon-yyyy') and then converting it back to to_date. still no luck.
When I run a query form the the Query Window in Visuial Studios 2012 all the date fields truncated to 'mm/dd/yyyy', but i need the full date returned. I am able to get full date from TO_char(MyDateField, 'yyyy-mm-dd hh24:mi:ss'), but if I do TO_DATE(MyDateField, 'yyyy-mm-dd hh24:mi:ss') it only returns 'mm/dd/yyyy'. I'm sure this is a simple setting in Visual studios but I cant find it to save my life. Is there there a way to have the full date returned by default?
->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
I would like to inquire how do I code my select statement if my user enter a search date range(search startDt: 01/08/2012 and search endDt :30/09/2012) and I will like to retrieve only the 7records out from my table as shown below ?
Write a program to declare a date variable and assign it to the current system date. Depending on the day of the month the program should print the following:
If day is 1-10 then print "It is day<day number> of <month name>. It is early in the month". If day is 11-20 then print "It is day<day number> of <month name>. It is the middle of the month". If day is 21-31 then print "It is day<day number> of <month name>. It is nearly the end of the month".
For example, if the day is November 30, then print "It is day 30 of November. It is nearly the end of the month".
How can we partition a table based on date if it does not have a date column.
Actually I have to compare two tables on daily basis and fetch few rows from those two tables and enter it to a third table.But both these tables does not have a date column.
I am confused if i need to alter those tables and add date column or if there is some way in which i can compare the data from the two tables for that particular day only and not the whole table data.
My requirment is to find out the period names and transactions which are in valid date formats and are less than sysdate and the non date formats are adjustments made by different users for their transactions
I want to load data from a file using sqlldr.I have a table commissions ( technician_id char(5) , tech_name char(30) , Comm_rcd_date DATE , Comm_Paid_date DATE , comm_amt number(10,2) )
my file is 00001,TIMOTHY TROENDLY,2011-03-04T01:45:12+0006,2011-03-04T01:45:12+0007,123.56 00002,KENNETH KLEMENZ,2011-03-04T01:45:12+0006,2011-03-04T01:45:12+0009,123.56 00003,SHUNDAR ARDERY,2011-03-04T01:45:12+0006,2011-03-04T01:45:12+0005,123.56 write a ctl file to load this data.
I want to get data for month to date. For example, If I pass today or any day date as parameter then i should get data for that month(month of passing date) up to passing(parameter) date. As well as i have to get year to date.For example, If I pass today or any day date as parameter then i should get data for that financial year(year of passing date) up to passing(parameter) date. how to get month to date and year to date data.
A field named xxx_date is a text item which we have to enter manually so as to update a record in that particular date. This is a mandatory field without which we cannot continue the data entry..
I am getting this error while trying to update the record
FRM-40509 :Oracle error :unable to update record
I have kept the enabled = yes required=no data type=Date.. in the property pallet
I want to reset my date to this format: 12/31/2012 11:59:59 PM - see code below:
DECLARE v_latest_close DATE; BEGIN v_latest_close := TO_DATE ('12/31/2012 23:59:59 ','MM/DD/YYYY HH24:MI:SS'); DBMS_OUTPUT.PUT_LINE('The new date format is : '|| v_latest_close); END;
the code above displays only : 12/31/2012 instead of 12/31/2012 11:59:59 PM
I have a date field that should be filled everyday with today's date and I need to get the days that were not entered.
i.e. :
CREATE TABLE TRY_F (DAT DATE);
INSERT ALL INTO TRY_F VALUES (to_date('01/01/2011','DD/MM/YYYY')) INTO TRY_F VALUES (to_date('02/01/2011','DD/MM/YYYY')) INTO TRY_F VALUES (to_date('04/01/2011','DD/MM/YYYY')) INTO TRY_F VALUES (to_date('05/01/2011','DD/MM/YYYY')) INTO TRY_F VALUES (to_date('06/01/2011','DD/MM/YYYY')) INTO TRY_F VALUES (to_date('08/01/2011','DD/MM/YYYY')) INTO TRY_F VALUES (to_date('10/01/2011','DD/MM/YYYY')) INTO TRY_F VALUES (to_date('14/01/2011','DD/MM/YYYY')) SELECT * FROM DUAL;
I need a smart way of getting the dates that were missed in DAT.
I want the query to resolve overlapping dates as well as merge contiguous segment and leave non-contiguous segments as is. The final result of the query should be like this.
I am having problems with the XMLTable function. I cant get it to see the entire date/time value in a date field. This wont work
select x1.* from XMLTABLE('/DOCUMENT' passing xmltype('<DOCUMENT><STR>abc def ghi</STR><NUM>1234</NUM><DT>2013-02-17T04:24:02</DT></DOCUMENT>') columns STR varchar2(25), NUM number, DT date) x1;
However if I change the DT tag to just the date only "2013-02-17" it works. Why wont Oracle see the entire date/time format even if its ISO 8601 compliant?
1 0 111 Jan-02-07 N 1 1 111 Feb-02-07 N 1 2 111 Mar-02-07 Y 1 3 111 Apr-02-07 Y 1 4 111 May-02-07 Y 1 1 222 Feb-06-07 N 1 1 222 Mar-06-07 N 1 1 222 Jun-06-07 Y 1 1 222 Jul-06-07 Y
The table has incorrect data. meaning for each consumer_id we want the ACTIVE_FLAG to be 'Y' only for it's latest record and the rest to be inactive. i.e. we want the data as follows:
A_ID, B_ID, CONSUMER_ID, U_DATE, ACTIVE_FLAG.
A_ID, B_ID, CONSUMER_ID, U_DATE, ACTIVE_FLAG.
1 0 111 Jan-02-07 N 1 1 111 Feb-02-07 N 1 2 111 Mar-02-07 N 1 3 111 Apr-02-07 N 1 4 111 May-02-07 Y 1 1 222 Feb-06-07 N 1 1 222 Mar-06-07 N 1 1 222 Jun-06-07 N 1 1 222 Jul-06-07 Y
I'm trying to run a script that will sum column values from a table using only a certain period of time in example below only January submissions. However, some of those rows have the same date and I want to have the latest time row of that day in the count.
