PL/SQL :: Create Reporting View Based On Date Column
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
ADVERTISEMENT
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
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 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
Apr 13, 2011
I have table with values :
PROV_IDMEASURE_IDPERCENTAGE
Z0000221P114 45
Z0000135P115 68
For the column all possible values are ( P102,P101,P103 etc toP124). I want to create a view ( if possible ) from the above with data output as :
PROV_ID P101 P102 P103 ............................P124
z000234 23 45 60 72
basically this view has columns based on the previous tables column ( MEASURE_ID) values and the values will be corresponding value in column Percentage.
View 2 Replies
View Related
Feb 22, 2010
I have a table PRODUCT with following structure:
Productcode Productname Quantity
AF Ade Fgh 100
LO Ldo Ope 50
SK Ske Kro 47
....
There may be any number of records in the table. Now I need to print distinct products in different columns such as.
AF LO SK
100 50 47
There may be any number of products...I need to print all those products as different columns as shown above. How to write the Stored procedure for this?
View 2 Replies
View Related
Jun 7, 2013
I am working on a simple form which will get the user to fetch required columns of employee master.
For that i have created a form which will display the column names and select option. When user clicks on query, the form will display the selected columns in a block. After going thru if required the user can download it to excel.
As of now I do not have filtering option. For this I thought of creating view based on the selection and then fetch records from the view and display it in form.
To achieve this I would like to know How I create or replace a view dynamically based on values selected. I remember doing this sort of thing long back, but could not recollect it.
View 3 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
Oct 8, 2010
is it possible to create primary key on view and use this view for creating foreign key .
View 3 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
May 7, 2013
i have a form based on view just to display. when i execute query,records are getting displayed more than once. make them get displayed just once.
View 12 Replies
View Related
Apr 1, 2009
I have a Materialized view based on 2 views. I modified one column in both the views.Do i need to refresh the Materialized view.
E.g. :
In view1 and view2 i changed column like this
nvl(test.no,'n/a') testdata --changed
nvl(test.id,'n/a') testdata --original
CREATE MATERIALIZED VIEW MV_test
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
[code].........
View 3 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
Sep 23, 2013
is it possible to base a Materialized View on results returned from a stored procedure?If not, do you see any other way except of filling a table with data from the stored procedure and then basing the MV on it?
View 4 Replies
View Related
Aug 15, 2012
I am searching for a decent method / example code to subdivide a large table (into a global temp table (GTT) for further processing) based on a list of numeric/alphanumeric which is the resultset from a view.
I am groping with the following strategy in PL/SQL:
1 -- set up cursor, execute the view (so I have the list of identifiers)
2 -- create a second cursor (or loop?) which: accepts each of the identifiers in turn executes a query (EXECUTE IMMEDIATE?) on the larger table INSERTs (or appends?) each resultset into the GTT
3 -- Then the GTT contains just the requires subset of the larger table for further processing and eventual import into iReport for reporting.
GTT is defined and ready to go, the larger table contains approx 40,000 rows and I need to extract a dozen subsets or so which add up to approx 1000 rows.
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
