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
DECLARE
P_LAST_UPDATE_DATE KPC_MMS_STD_CHKPOINTS.LAST_UPDATE_DATE%TYPE;
BEGIN
INSERT INTO KPC_MMS_STD_CHKPOINTS (CHK_POINT_CODE,CHK_POINT_DESC,CHK_POINT_FREQUENCY,CREATED_BY,
CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE)
VALUES (:P13_CHK_POINT_CODE,:P13_CHK_POINT_DESC,:P13_CHK_POINT_FREQUENCY,:P13_CREATED_BY,:P13_CREATION_DATE,
:P13_LAST_UPDATED_BY,:P13_LAST_U PDATE_DATE);
COMMIT;
END;
I am switching database from access to oracle 11g. I have create all the required tables, but I am stuck at one point. The previous person who created access database had auto increment with SG0101, SG0102,........ In oracle I know we can auto increment primary keys but only with the numbers not with characters.
So I have customerid which is a primary key and it automatically increments the number, but I have one more column with memberid where I am inserting all the ids that start with SG0101 bla bla.....
I already have 800 member ID's that start with SG, but that value doesnt automatically increment because I dont have any sequence or trigger to do that.
So how do I create a sequence and trigger that will automatically start value with SG and keeps auto incrementing?
I'm trying to create a sequence for a primary key to simply auto-increment by the default of 1. I have a sql script written to generate mt tables, and I'm not sure how to modify the script to include the sequence. I also just want the sequence for a specific column, ie, PK, not the PK in all tables.
Here's a snippet from my script:
create table image
(
image_id int NOT NULL,
source_id int NOT NULL,
CONSTRAINT image_id_pk PRIMARY KEY (image_id),
CONSTRAINT fk_source_id FOREIGN KEY (source_id) REFERENCES source(source_id)
);
Would I add the create sequence statement right after the create table, and if so, how do I apply the sequence to only 1 table and a single column?
I want to increment a counter in a loop based on a condition.
Here is how my xml looks like
<result>
<resultset id=1>
<value>
[Code]....
I need to look at each and every resultset and check if the value is >400 and if it is then display some text. Something like this
<outputvalue>
Yes, there is a value greater than 400
</outputvalue>
In my XSL, I declared a variable called count with value 0. I created a for-loop which goes through these results. Then inside the loop there is a condition to cehck if the value is > 400. If the value is >400, then the counter is incremented.
````````````````````
<outputvalue>
<xsl:variable name="Count" select="0"></xsl:variable>
<xsl:for-each select="/results/resultset">
[Code]....
There is a problem with the above code is that result is like this
<outputvalue>
*1*
Yes there is a resultset with greater than 400
</outputvalue>
Is there a way I can just remove the character 1 from the output? Or is there a better way to increment?
How to check for the increment of a space of the tablespace based on the particular table. (i.e.) Say a scenario, if am trying to load the data for a particular table, for first I loaded some 10000 records and then again loading 50000 records ,so based on the icrement of the reocrds the tablespace size also increases gradually . so for this scenario how to monitor the increment of the space.
View 13 Replies View RelatedI have a problem I am making a rooster I want to increment dates. User will put input the first date then it have to incremented by user click i used Key_Next_item on Date Field.
if System.cursor_record >1 then
Doctor_Rooster_Details.Week_Date =:Doctor_Rooster_Details.Week_Date +1
Its gives me error on KeyNext_ITem of ORA
I have fields
Week_Date Day St_Time EndTime Available
01/02/2011 Friday 10:00 12:15 Y
I want to increment Week_date when user click on next record it will incremented. ST_Time, End_Time and available user will input that.
01/02/2011 Friday 10:00 12:15 Y
02/02/2011 Sat
I have a table with ID_ITEMITEM_NAMEITEM_DEV_DATEITEM_PROD_DATEITEM_TESTING_DATE
I need to put the ITEM_NAME+'DEV', ITEM_NAME+'PROD', ITEM_NAME+'TESTING' on one calendar,
the calendar provided by APEX is supposed to ask one date per item, so how I can put more than a date per item? So for example
ITEM ITEM_NAME='Release 1'ITEM_DEV_DATE='15-May'ITEM_TESTING_DATE='21-May'ITEM_PROD_DATE='30-May'
I want to add a date picker calendar on my form on the date field. I learned that process in institute but now forget the process.. Tell me the process and share the calender and standard file.
View 4 Replies View RelatedI am new to the Oracle forms,i want to attach a calender to my form. I followed the below process:
--> I have attached the LOV&Record Group of the item for which the calender is attached as ENABLE_LIST_LAMP LOV which is included in the TEMPLATE.fmb
--> Set the validate from list to 'NO' in the property palette
--> Then after in the trigger KEY_LISTVAL,wrote the code
calendar.show;
while compile the form i got an error component 'SHOW' must be declared
How to set date persian to georgian calender.
View 1 Replies View RelatedI want to find the row with invalid day, month which are not matching with calendar day and month. Also the program should capture the data if the year <1900
with xx as
(select 101 as ID, '24/05/1899' as create_date from dual
union all
select 101, '32/03/2012' from dual
union all
select 102 ,'30/02/2012' from dual
union all
select 101 , '29/02/2013' from dual
[code]...
I have a calendar region based on a simple query from a table which has date and name columns. The calendar type is monthly with 3 buttons - previous, today & next. It works perfectly.
I have added a date picker in a calendar region now. If i choose any date, the calendar month should be refreshed accordingly. Ie... by default it would show July 2012 month. When i choose the month as March 2012 in the date picker, it should show March month data.
i have changed date's format and the calendar type in sqlplus through the following command so the date's format and the calender type both changed successfully and work, but in oracle form the they haven't changed.
alter session set nls_calendar='persian';
ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD';
ALTER SYSTEM SET NLS_DATE_FORMAT='YYYY/MM/DD' SCOPE=SPFILE;
Note: i know session would change the format temporally but i want to change it permanently so i tried alter_system but still no result
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.
Oracle 11.2.0.1
Windows
Is there any way by which I can get next character and date as we gets the number from sequence. Suppose in my table below is data :
AA
AB
AC
AD
..
..
AX
AY
AZ
BA
BB
BC
...
BX
BY
BZ
and I wish to get CA from sequence kind of object / function. Similar function if possible, for date also
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
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].........
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
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
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].....
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)
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')
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.
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
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.
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 Relatedi'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.
I am trying to create a table that will increment my ID by one using the following commands:
/*Creates the log needed to increment ID*/
create sequence seq_log;
CREATE TABLE MESSAGE_LOG_TABLE
(
IDNUMBER(10)NOT NULLPRIMARY KEY,
[code]...
When I run the above my create sequence completes successfully but I get a ORA-00955: name is already used by an existing object error message on the create table. I have dropped all tables and sequences before running my command but I still get the same error message.
After it bombs out it appears that SQL+ want's more information for it begins to give me line numbers as if it is looking for a ";" to end the above command. I have to exit SQL+ and log back in to continue working.
SNO column should be incremented depending upon the total records to be fetched.if i get 4 records then sno numbers should be 1,2,3,4.i dont want to put rownum also in the GROUP BY clause.how to increment the serial number?
SELECT JC.A,
ROWNUM SNO, --serial number
JC.B,
SUM(CHR.AMOUNT),
SUM(CHR.FINALAMOUNT),
JC.C,
JC.D,
C.E,
JC.F,
JC.G
FROM CHARGES CHR
WHERE JC.B = '12111'
AND JC.STATUS = 'INVOICED'
GROUP BY JC.A, JC.B, JC.C, JC.D, JC.E, JC.F, JC.G;