SQL & PL/SQL :: Create New Column Based On MIN DATE
Jun 26, 2012
CREATE TABLE DAN_DATES
(ID VARCHAR2(12),
YEAR VARCHAR2(,
TERM VARCHAR2(,
START_DATE VARCHAR2(12))
INSERT INTO DAN_DATES (ID,YEAR,TERM,START_DATE) VALUES ('1','2012','1201',to_date('20120227','YYYYMMDD'));
INSERT INTO DAN_DATES (ID,YEAR,TERM,START_DATE) VALUES ('1','2012','1201',to_date('20120626','YYYYMMDD'));
INSERT INTO DAN_DATES (ID,YEAR,TERM,START_DATE) VALUES ('2','2011','1101',to_date('20110226','YYYYMMDD'));
INSERT INTO DAN_DATES (ID,YEAR,TERM,START_DATE) VALUES ('2','2011','1101',to_date('20110725','YYYYMMDD'));
INSERT INTO DAN_DATES (ID,YEAR,TERM,START_DATE) VALUES ('2','2012','1201',to_date('20120227','YYYYMMDD'));
Want to take the Start_Date for that year, CREATE A NEW COLUMN and place that START_DATE (which is row 1 for the year (min)) in it. So for ID 1 TERM is 1201 and 1202 BUT we want the top start date (earliest start date) and CREATE a clumn (NEW_START_DATE) and place that date in there wherever year is 2012.
I want to get
IDYEARTERMSTART_DATEMIN_DATE
12012120127-Feb-1227-Feb-12
12012120126-Jun-1227-Feb-12
22011110126-Feb-1126-Feb-11
22011110125-Jul-1126-Feb-11
22011110126-Sep-1126-Feb-11
22012120227-Feb-1227-Feb-12
View 10 Replies
ADVERTISEMENT
Jul 30, 2012
My DBA gave me a table with only one date column say Table1.Date. Its in the format of Date and say it is = 7/23/2012.
Now i have to create my own Reporting View(which is used for reporting) based on that date column like below:
It should be a column with values in the following format =
2012-07
2012-06
2012-05 etc....upto
2010-01
So i started out my creating like this:
select
to_char(Table1.Date,'yyyy-mm')
from Table1
Union
[Code]....
.and so on till i get 2010-01.
there has to be a better way to do this.
View 7 Replies
View Related
Jun 21, 2012
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.
View 1 Replies
View Related
Oct 15, 2013
I have one hirarchical query which return the parent to child hirarch level data. it has 11 level child data. i want to create column based on number of child in hirarchy. though i know it is 11 but it can change also.Is there any way i can create the column dynamically
ORG_UNITCOST_CENTERORG_UNIT_NAMEPARENT_ORG_UNITLLSYS_CONNECT_BY_PATH(ORG_UNIT,'/')
500171960000022000Managing Director - LUL500169965/00000001/50000001/50017588/50016996/50017196
500018370000021241FSO500171966/00000001/50000001/50017588/50016996/50017196/50001837
502894940000021241Knowledge Management500018377/00000001/50000001/50017588/50016996/50017196/50001837/50289494
508014980000021241Finance500018377/00000001/50000001/50017588/50016996/50017196/50001837/50801498
View 1 Replies
View Related
Feb 9, 2011
I need to create a materialized view with a clob column based on a varchar2 column of a table.This is because in the mv the clob column data gets appended one after another.
View 2 Replies
View Related
Jun 1, 2010
I'm trying to work out how to take a table like this:
IDDate
12502-Feb-07
12516-Mar-07
12523-May-07
12524-May-07
12525-May-07
33302-Jan-09
33303-Jan-09
33304-Jan-09
33317-Mar-09
And display the data like this:
IDPeriodPeriod StartPeriod End
125102-Feb-0702-Feb-07
125216-Mar-0716-Mar-07
125323-May-0725-May-07
333102-Jan-0904-Jan-09
333217-Mar-0917-Mar-09
As you can see, it's split the entries into date ranges. If there is a 'lone' date, the 'period start' and the 'period end' are the same date.
View 13 Replies
View Related
May 15, 2011
i have two questions.
(1) how can i fill some value in a table column based on some existing column value automatically without user intervention. my actual problem is i have 'expiry date' column and 'status'. the 'status' column should get filled automatically based on the current system date. ex: if expiry date is '25-Apr-2011' and current date is '14-May-2011', then status should be filled as 'EXPIRED'
(2)hOw can i build 'select' query in a report (report 6i) so that it will show me list of items 'EXPIRED' or 'NOT EXPIRED' or both expired and not expired separately in a single report based on user choice. 'EXPIRED' & 'NOT EXPIRED' can be taken from the above question no. 1.
View 3 Replies
View Related
Sep 10, 2010
I am trying to create a trigger which changes the content of one column based on the information within another column.
For Example, if the 'ITEMQUANTITY' field drops below 1 then I want the STATUS column to say 'Out Of Stock'.
View 23 Replies
View Related
Jun 6, 2012
I am not able to load complete date along with time in the date column. here is my table desc
DESC STAGE
Name Null Type
----------------------------------
TABLE_NAME NOT NULL VARHCAR(20)
RECORDCOUNT NUMBER
CREATED_DATE NOT NULL DATE
my control file is like this
LOAD DATA
APPEND
INTO TABLE SCOOP.STAGE
FIELDS TERMINATED BY ","
( TABLE_NAME
,RECORDCOUNT
,CREATED_DATE DATE(16) "YYYYMMDDHH:Mi:SS"
)
the data gets loaded, but it appears like this in the table
HIGHSCHOOL3080606-JUN-12
MIDDLESCHOOL8768006-JUN-12
BUT I WANT COMPLETE DATE AND TIME (HH:MI:SS) , HOW CAN I GET IT (THIS IS HOW I WANT 06-JUN-12 11:07:33)
View 10 Replies
View Related
May 19, 2010
How to write a single query for the below scenario?I got only start date column, but I need show the end date based on next (start date-1). For the last row, need to show the same date in the end date column.
For Example:I have the data as shown below,
Start date End date
1-Jan-10
3-Mar-10
3-May-10
5-Jul-10
1-Aug-10
I wanted to output as shown below
Start date End date
1-Jan-10 2-Mar-10
3-Mar-10 2-May-10
3-May-10 4-Jul-10
5-Jul-10 31-Jul-10
1-Aug-10 1-Aug-10
View 10 Replies
View Related
May 18, 2011
I am trying to get records from a table based on date column. I should retrieve records if the date column is NULL
and if it is not null ,i shud get the records of MAX date.
How would i do this. Below is my query,by which i can get records based on either maxdate or Null date
select c.id, c.status,c.u_gr_code,
from
(
select id, status,u_gr_code,date_col,
max(record_valid_from) over (partition by entity_user_gr_id, status_id) max_date
[code].........
View 10 Replies
View Related
Aug 16, 2010
i have a table with the following description
create table gl_periods(period_name varchar2(10),transactions number (2) );
with the data as :
period_name transactions
------------ --------------
JAN-10 12
FEB-10 12
MAR-10 8
APR-10 23
ADJ_TOM-10 25
MAY-10 37
JUN-10 41
JUL-10 10
PHY_JAY-10 6
AUG-10 14
SEP-10 22
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
View 8 Replies
View Related
Oct 18, 2012
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.
View 6 Replies
View Related
Apr 27, 2010
I have a script which is used to run a job based on the users choice. For example: I have two table, Files and Requests
User select the files to be executed for each request. This data will be stored in Requests table.
Table 1: Files
files
======
file-1
file-2
file-3
..
..
file-n
Table 2: Requests
request file lup_date
==================================
request-1 file1,file2,file3 04-JAN-2009
request-2 file1,file4,file5 06-JAN-2009
request-3 file6,file2 021-JAN-2009
request-4 file1,file2 04-FEB-2009
request-5 file1,file2 08-JAN-2009
request-6 file1,file2 04-MAR-2009
.......... ........... ................
request-n-1 file6,file2,file4 04-DEC-2009
request-n file6,file3,file4 04-DEC-2009
how to get the output in below format. Count how many times each file is selected in a month.
Output format should be like below..
==============================================
File_Name Jan Feb Mar Apr ---------- Dec
==============================================
file1 2 1 3 0 ---------- 2
file2 1 0 2 1 ---------- 3
file-n 8 2 3 0 ---------- 2
View 2 Replies
View Related
Dec 16, 2011
Problem:
Our term (strm) is dictated by the term_begin_dt and term_end_dt dates but I want to keep selecting that term until 1 week before the next term opens and then switch to that term.
Basically, I don't want any gaps between a term.
Output:
select strm when sysdate is between term_begin_dt and term_end_dt (strm would equal 3943)
select strm until 1 week before the start of the next term (4027) (strm would equal 3943)
select strm when 1 week before term_begin_dt (strm would equal 4027)
Repeat for the next term and so on 12/16/2011
select strm
from term
where trunc(sysdate) between trunc(term_begin_dt) and trunc(term_end_dt)
Output: 3943
12/17/2011 - 01/01/2012
select strm
from term
where ?
Output: 3943
01/02/2012 - 5/4/2012
select strm
from term
where ?
Output: 4027
Repeat.
Test Case:
CREATE TABLE TERM
(
STRM VARCHAR2(4 BYTE),
DESCR VARCHAR2(20 BYTE),
TERM_BEGIN_DT DATE,
TERM_END_DT DATE
)
Insert into TERM
(STRM, DESCR, TERM_BEGIN_DT, TERM_END_DT)
Values
('3943', '2011 Fall Semester', TO_DATE('08/22/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
TO_DATE('12/16/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TERM
[code].....
View 2 Replies
View Related
Mar 3, 2011
We have fact tables partitioned based on week.
We receive point of sale data on daily basis .
Some times we will receive data for previous week also.
In current system , we are analyzing only one partition(current partition).
Now , we want to analyze all old partitions also based on incoming old trading date.
how can i approach to find partitions for old data (trading_date s)
View 1 Replies
View Related
Sep 24, 2013
i need to be increasing the sequence no by 1 for every calender date.For example lets say if i receive 5 dumps of data for 24/09/2013 it should be as below. For next day the 25th the sequence no should again begin with 1.
24/09/2013 1
24/09/2013 2
24/09/2013 3
24/09/2013 4
24/09/2013 5
25/09/2013 1
View 6 Replies
View Related
Aug 14, 2011
is the definition of my table :
CREATE TABLE DATEFETC
(
ID VARCHAR2(10 BYTE),
DT DATE
)
And these are the data that are available,(select * from DATEFETC)
IDDT
00108-09-2011
00208-10-2011
00308-11-2011
That's fine.
Now i am executing this query ,but this is returning no rows.Why ?
select * from datefetc where dt between to_date('08-08-2011','mm-dd-yyyy') and to_date('08-12-2011','mm-dd-yyyy')
View 1 Replies
View Related
Jul 31, 2013
I want to create an XML based on xsd and then i want to validate the same xml with xsd ,
View 1 Replies
View Related
Dec 12, 2012
i have the below query
select to_char(report_date, 'YYYY MM Mon'), count(1) no_of_times
from (
select to_date('&&YYYYMMDD', 'YYYYMMDD')+rownum report_date
, mod(rownum,14) mod_result
from all_objects
[code]...
need to convert as procedure based on input date parameter.I will pass the input date from java environment and need to see the sql query output in front end.
View 7 Replies
View Related
Oct 29, 2012
I want to get top two rows based on ACCT_UNIT & order by status_date, if there is only one row on acct_unit, get one row. IF more than two rows available, want to get the top two rows based on status_date.
SELECT ACTIVE_STATUS, ACCT_UNIT, DESCRIPTION, DIRECTOR, DIRECTOR2, STATUS_DATE, OBJ_ID, STATUS_FLAG, SUR_KEY
FROM STSI
View 10 Replies
View Related
Mar 20, 2011
I have one view which displays the information of all the receipts with date now i want to make ageing report based on this date. the columns will be 0-30 , 30-60,60-180 and > 180 , i want to distribute the receipts qty based on number of days.
View 1 Replies
View Related
Oct 10, 2012
CREATE TABLE test1
(strt_num NUMBER ,
end_num NUMBER ,
des VARCHAR2(5),
CONSTRAINT pk_strt_num PRIMARY KEY (strt_num)
);
INSERT INTO test1 VALUES (5, 8, 'GC');
INSERT INTO test1 VALUES (10, 25, 'AB');
INSERT INTO test1 VALUES (12, 35, 'PC');
INSERT INTO test1 VALUES (22, 65, 'LJ');
SELECT * FROM test1
STRT_NUM END_NUM DES
-------------- ------------ -------------
5 8 GC
10 25 AB
12 35 PC
22 65 LJ
The requirement is the records should be split based on below conditions
1. Split only those records WHERE (end_num - strt_num) > 10
2. If TRUNC((end_num - strt_num)/10) = n, then n + 1 number of rows should be created for that record
3. While splitting the records,
-> For first record , START_NUM = Original STRT_NUM and END_NUM = START_NUM + 10
-> Second record , STRT_NUM = previous END_NUM + 1 and END_NUM = previous END_NUM + 10
And this should continue for all records except the last record
-> For last record, STRT_NUM = previous END_NUM + 1 AND END_NUM = Original END_NUM
This table has 5 million records. Only for 2000 records (end_num - strt_num) > 10.
Expected Output.
STRT_NUM END_NUM DES
------------- -------- ---------
5 8 GC -- No chage, END_NUM - STRT_NUM < 10
10 20 AB
21 25 AB
12 22 PC
23 32 PC
33 35 PC
22 32 LJ -- STRT_NUM = Original STRT_NUm, END_NUM = STRT_NUM + 10
33 42 LJ -- STRT_NUM = Previous END_NUM + 1, END_NUM = previous END_NUM + 10
43 52 LJ -- STRT_NUM = Previous END_NUM + 1, END_NUM = previous END_NUM + 10
53 62 LJ -- STRT_NUM = Previous END_NUM + 1, END_NUM = previous END_NUM + 10
63 65 LJ -- STRT_NUM = Previous END_NUM + 1, END_NUM = Original END_NUM
View 7 Replies
View Related
Jul 26, 2012
i'm trying to do an export/import process using command prompt and the idea is export a records based on the date condition. and the date will be the parameter. my code is like this:
exp <username>/<password>@<database> file=<table_name>.dmp tables=<source_table> query="where <date> between &start_date AND &end_date";
is it possible to do like this, that it should prompt you to enter the start and end date?
then my import script:
imp <username>/<password>@<database> dumpfile=<table_name>.dmp tables=<target_table>;
the idea is get only the records from ProdDB based on the date condition, and append it to the MISDB.
View 12 Replies
View Related
Jul 2, 2010
Using oracle 10g R2 on sun-solaris 10 (sparc-64) Well in the MIS system we have lot of ad-hoc queries coming up. We have proper indexing. Say an example which runs very slow;
SELECT GLKCO, GLDCT, GLDOC, GLDGJ, GLJELN, GLEXTL, GLPOST, GLICU, GLICUT, GLDICJ, GLDSYJ,
GLTICU, GLCO, GLANI, GLAM, GLAID, GLMCU, GLOBJ, GLSUB, GLSBL, GLSBLT, GLLT, GLPN, GLCTRY, GLFY, GLFQ,GLCRCD, GLCRR, GLHCRR, GLHDGJ, GLAA, GLU, GLUM, GLGLC, GLRE, GLEXA, GLEXR, GLUPMJ, GLUPMT, GLBCRC, GLCRRM, GLACR, GLAN8
FROM "PRODDTA"."F0911"
WHERE GLUPMJ <> 0
AND TO_DATE('1 JAN' || (19+substr( GLUPMJ , 1, 1)) || substr( GLUPMJ ,2,2)) + substr( GLUPMJ , 4, 3 ) -1 BETWEEN SYSDATE - 365 AND SYSDATE
[code]....
Here GLUPMJ already indexed so the second query returing an index scan but the first query does a FTS naturally.Now even if I plan to create a function based index on 'the bold highlighted' but how.
create index glupmj_idx on f0911(TO_DATE('1 JAN' || (19+substr( GLUPMJ , 1, 1)) || substr( GLUPMJ ,2,2)) + substr( GLUPMJ , 4, 3 ));..Error If I don't use a FBI my query will result in FTS.
1> how to create a FBI here in this case
2> In MIS systems where 'n' no of ad-hoc queries can come up, how to avoid FTS.
View 4 Replies
View Related
Oct 21, 2011
I have a csv file which is in this format -
qwerty SCHEMATIC FILE
; Version 2007.7.1
Project,qwerty Project,,1,,7,1.5,1.5,1,1,0,,0,1,0,0,0,0,3,1,1,0,1,0,0,0,0,2,3,1,0,1,0,0,0,0,3,3,1,1,1,0,0,0,0,0,0,0,0,
abc,25150-28407.dat,,102,192,42,12632256,1,1,102,192,42,1,12632256,0,6,1,0,32896,1,0,0,0,0,,,-1,-1,-1,-1,0,0,0,-1,-1,-1
xyz, , , 0, 102, 0, 0, 0, 0, 0, 0, 0, 0, 0, , , , , , , , , , , 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
Product,00094416505678,19133,"24-36""X80"" FOLD DR VIA MEH",,4.5,81,3, 8421504,,0,,L.T.L. WHOLESALE.,abc,,0,0,0,
Product,00094416502345,37154,"24-36""X80"" xyz WHITE",,5,81,3, 8421504,,0,,L.T.L. WHOLESALE.,abc,,0,0,
Product,00094416501111,83120,"24-36""X80"" abc WH",,5,81,3, 8421504,,0,,L.T.L. WHOLESALE.,abc,,0
Can I create an external table for loading this file based on selection of fields driven by the record identifier which is the first field of each record, except for the firt two?
For e.g In the above file, for records which say 'abc' in it's first field, I want to load the 2,3,6,8 fields delimited by a comma (the values may as well be null in those fields) and for records which say 'Product' in their first fields, I want to load 5,8,9 and 10 fields from the same file. Basically, I want to know if we can use instr function while choosing the fields line by line based on a search criteria in the file. There will be less columns in my table than the number of fields in the csv file, so I guess I got to mention 'MISSING FIELD VALUES ARE NULL' option. There is another challenge too - I have to skip loading the first two lines from the file to the table.
I have written a big pl/sql proc for doing the same, using utl_file.get_line option, but it is still untested and I would be extremely happy to believe that this can be achieved by creating an external table too.
View 11 Replies
View Related
Jun 2, 2011
can we create two datablocks(header&line) based on one table in oracle forms.any way to create?
View 7 Replies
View Related
Mar 3, 2011
How to get the max row based on a value in a column
View 1 Replies
View Related
Dec 25, 2012
I have a partitioned table with ~50 million rows that was setup with a number(10) instead of a date column. All the data in the table is ALWATS in this format YYYYMMDD
CREATE TABLE T1.monthly
(
SEQ_NUM NUMBER(10) NOT NULL,
DAY_DK NUMBER(10) NOT NULL
)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
[code]........
some sample data
SEQ_NUM DAY_DK
---------- ----------
990 20121225
991 20121225
992 20121225
993 20121225
994 20121225
995 20121225
996 20121225
997 20121225
998 20121225
999 20121225
When I use the exchange partition method the parition is able to move the data from "monthly" table to "mth" table.
desc t1.mth; ### my temorary table
Name Null? Type
----------------------------------------- -------- ----------------------------
SEQ_NUM NUMBER(10)
DAY_DK NUMBER(10)
Than when I try to alter my temp table "mth". I get an error table must be empty to change column types.
alter table n546830.mth modify (DAY_DK date);
Next I tried making my temporary table "mth" a date column. When I an the exchange partition command I get the following error:
alter table t1.monthly exchange partition DEC_2012
with table t1.mth without validation;
alter table n546830.monthly exchange partition DEC_2012 with table n546830.mth without validation
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
Method I can use to convert a number(10) to date column and keep the information in a table. Note, I don't care about HH:MM:SS as I never had that information to begin with and would be happy to set that part of the date column to all zeroes "00:00:00"
View 12 Replies
View Related
Sep 9, 2013
I have a table with columns job_id, jan, feb, mar ... , and year
I need to create a select query that will get the data from 18 months ago based on sysdate.
So something like:
Select to_char(add_months(sysdate, -18),'MON') from table1 where job_id = 56947 and year = to_char(add_months(sysdate, -18),'YYYY');
However I need the result of to_char(add_months(sysdate, -18),'MON') to actually act as a column name, not a string result.
View 23 Replies
View Related
