SQL & PL/SQL :: Creating View On Basis Of Time Interval?
Apr 27, 2010
how to create a view from table which containes rows from every minutes. In view I want to select only rows at 15 or 30 minutes interval.
eg. my base table has 60 rows for 1 hour. The created view must contain 4 rows on 15 minutes interval basis at the boundary of that interval.
O/P of view should be like this.
Value Time
100 00:00:00
200 00:15:00
300 00:30:00
400 00:45:00
500 01:00:00
View 6 Replies
ADVERTISEMENT
Jul 25, 2013
how to set interval time every 4hrs in dbms_jobs but starting time 3.00am.
i am set trunc(sysdate)+4/24. but it will take starting at 12.00,4.00,.....in this way..
View 2 Replies
View Related
Sep 19, 2012
I have this requirement to pull the ACTIVE days that a participant holds true in a given month for a specific position he/she holds.The three date ranges here are: LOA dates(leave of absence), XFER dates(TRANSFER in/out of the position),Quality compliance(QUAL start/end dates).So,
INACTIVE DAYS = [(LOA + XFER + WW) - OVERLAPPING DAYS OF (LOA+XFER+QUAL)]
CREATE TABLE PAYEES
(
RUN_PERIOD DATE NOT NULL,
PAYEEID VARCHAR2(20 BYTE) NOT NULL,
LOA_START_DATE DATE,
LOA_END_DATE DATE,
[code]....
I am able to pull out the no of days on a individual basis(count of days for Xfer/LOA/Qual) but I am really not sure as to how should i go about determining the overlapping days between 3 date ranges to determine the ACTIVE/INactive days.
View 4 Replies
View Related
Jul 17, 2012
How to set time interval in schedule job i need five minutes interval..
View 5 Replies
View Related
Mar 28, 2011
IN OUT ROOM
1/20/2011 7:26:00 AM | 3/28/2011 10:34:51 AM | 1
3/23/2011 12:53:00 PM | 3/28/2011 10:34:51 AM | 2
3/14/2011 10:44:00 AM | 3/14/2011 3:05:00 PM | 3
2/24/2011 2:31:00 PM | 2/25/2011 9:02:00 AM | 1
2/22/2011 1:32:00 PM | 2/23/2011 9:56:00 AM | 2
2/22/2011 1:30:00 PM | 2/22/2011 1:32:00 PM | 1
12/9/2010 6:42:00 PM | 12/11/2010 1:38:00 PM | 3
3/8/2011 4:48:00 PM | 3/28/2011 10:34:51 AM | 1
Here I need an out put like below
room 1 2 3 Total
date 7am-12pm 12pm-7pm 7pm-7am 7am-12pm 12pm-7pm 7pm-7am 7am-12pm 12pm-7pm 7pm-7am
01/20/2011 2 3 1 1 3 3 2 4 1
01/21/2011 1 2 2 1 0 0 0 0
...........................................
.......................................
Total
i have to divide into 3 groups and take a count 7am-12pm, 12pm-7pm, 7pm-7am groups
It looks so complicated to me, because IN time and OUT time together how we do it.
suppose one person 6am IN and out 8PM means he will be in 7am-noon , noon to 7pm, 7pm-7am -- 1, 1 1 on 3 interval another scenario is if one person in 2am in the morning it has to be previous days count. Is this possible to do it in query.
View 1 Replies
View Related
Mar 26, 2011
I'm having trouble using interval data types in a procedure. I need to pass a number of minutes as a parameter, and then use them for arithmetic on a timestamp with time zone. This works no problem:
set serveroutput on
create or replace procedure tstz(mins varchar)
as begin
dbms_output.put_line(systimestamp - interval '10' minute);
end;
[code]...
I've tried a few variations of data type and type casting for the parameter, but I can't make it work.
View 5 Replies
View Related
Jul 30, 2012
I an using oracle10g in AIX..Can i restrict the shutdown operation within a time intervall....say for eg: "Shutdown immediate" should not be accepted by sqlplus, if it is issued after 10am and before 6:00Pm..
View 2 Replies
View Related
Sep 14, 2011
I have create a mview using a dblink with the refresh difference of every 10min. Suddenly I check the mview refresh date and time it was not getting refreshed automatically at the time interval specified.
drop MATERIALIZED view log on afccv.tbl_voicechat;
drop MATERIALIZED VIEW SMSCHAT.TBL_VOICECHAT_NEW1 ;
CREATE MATERIALIZED VIEW SMSCHAT.TBL_VOICECHAT_NEW1
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
[code]....
I am trying to find out what is the cause of not refreshing and also googled for the same. got some link also either not linked to same problem or not cleared.. Where should i checked regarding the error cause.
View 33 Replies
View Related
Apr 5, 2011
how can we load a Flat file into a Database At Regular Interval Time.
View 2 Replies
View Related
Oct 21, 2011
i am using oracle 10g on solaris 10 os.currently archived log is generated by size wise 52 mb.i want to know whar is the best practice for archive log generation . it should be time interval or size wise.
View 1 Replies
View Related
Dec 11, 2012
I'm trying to create a Materialized View on a remote database from a simple view. The reason is, the data owners don't want to grant explicit tables privileges to external subscribers.
A new schema is created to publish data in the form of a view. I've created mlogs on the master tables, and granted them to the subscriber, but it's still complaining about a missing primary key on the view. A primary key does exist in the master table.
Is there another work around for this situation without having to work inside the data sources' environment?
View 5 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
Mar 27, 2012
I have been updating data in my database by adding numerous rows and assume the insertion of rows started from 1st date of january month.
I have been entering somehow or the other part of data every day.And now the date is 25th of january.
How can i take a partial backup of my database of the transactions done between 15th of January to 20th of January Month
How to take Backup of a particular date say it be 12th of january month.
How to take backup of first 10 days i.e., 1st-10th of january.
View 7 Replies
View Related
Feb 7, 2013
There are 2 databases, database A and database B. Database A is Oracle 11.2.0.2 which runs on linux and Database B is Oracle 11.2.0.2 which runs on windows xp machine. In database A, there are 100's of tables which are being updated every 10 minutes or 15 minutes. For reporting purpose, the developer wants to run report for the tables. But since database A is being updated every now and then, generating reports takes almost 15 to 20 minutes. So the reports can be generated in Database B. Once in a day the database B should have the updated data from database A so that the reports can be generated in database B with less time. What could be the best solution for the database B to have the updated data on daily basis from database A in oracle?
View 3 Replies
View Related
Oct 26, 2011
I am having trouble with creating a view based on the following: -
Total hours flown EVER, for each crew of the FBN001 on 20th October 2011.
Here is what i got so far (only works for each members flown on a particular flight, NOT on their whole hours flown in a period of a lifetime) : -
SELECT e.Employee_Number, e.FirstName, e.LastName,
SUM( ( (s.ESTIMATED_ARRIVAL) - (s.ESTIMATED_DEPARTURE) )*24)AS
TOTAL_HOURS_FLOWN
FROM SCHEDULE s, CREW_ASSIGNMENT ca, EMPLOYEE e
WHERE
s.Flight_Number = ca.Flight_Number
AND
s.Serial_Number = ca.Serial_Number
[code]...
View 20 Replies
View Related
Mar 4, 2011
I have create a procedure as
create or replace procedure aa is
abc varchar2(1000);
begin
abc:='create or replace view abc_view as select * from abc';
execute immediate abc;
end;
When i try to execute the same as
BEGIN
aa;
END;
i got error as
ORA-01031: insufficient privileges
ORA-06512: at "EARS.AA", line 5
ORA-06512: at line 3
why i get this error. there is no problem in grants. the same create or replace view query works if i directly executes in sql.
View 8 Replies
View Related
Jan 14, 2011
i have a union all query (two quiries i suppose) that runs fine and returns the results i am after.... when i try to use this query to create a view I get
SQL Command: CREATE OR REPLACE FORCE Failed: Warning: execution completed with warning
CREATE OR REPLACE FORCE VIEW "schema1"."name_of_view" ("column1", "column2", "column3") AS
SELECT b.column1, a.column2, c.column3
[Code].....
View 4 Replies
View Related
Oct 3, 2013
I want to create a materialized view for the with same logic as below view
CREATE OR REPLACE FORCE VIEW GFL_MCP_CORE.VW_CLIENT_HIERARCHY
(KEY_INDENTED, CLIENT_LEVEL, ID, NAME, CLIENT_TYPE_CODE,
L2_CLIENT_ID, L2_CLIENT_NAME, TOP_CLIENT_ID, TOP_CLIENT_NAME, CLIENT_MCP_FLAG,
CLIENT_GROUP_NAME, ORG_REGION_NAME, COUNTRY_NAME, PROCESSOR_CLIENT_CODE)
[code]....
Here is the sample data for the client table and other tables are generic.
Row#IDNAMECOUNTRY_CODECLIENT_TYPE_CODECLIENT_GROUP_IDPARENT_CLIENT_IDSTATUS_FLAG
134801ZhuanshenguangchangCNBR10048232909A
212515229ConsolidatedFCUUSCL54812515A
314821286 Golden Mile BranchCABR53112712A
42050014513Bay CityUSBR56612009A
53864712-44-125 LeicesterGBBR56038630A
63869114736 Freestar BrickyardUSBR10020238686A
731141Walsall StoreGBL254631103A
View 1 Replies
View Related
Oct 1, 2012
Am trying to create a materialized view as below
SQL> CREATE MATERIALIZED VIEW emp_view_73 BUILD IMMEDIATE REFRESH COMPLETE ON COMMIT
2 AS
3 SELECT em.employee_id,
4 em.employee_fname
5 ||' '
6 ||em.employee_lname employee_name,
[code]....
ERROR at line 12: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
View 37 Replies
View Related
Feb 15, 2013
I am able to create view and the public synonym with the same name but when I am quering view result is showing .
Is there any logic behind this .
CREATE OR REPLACE VIEW emp_dtls as SELECT * FROM emp;
CREATE PUBLIC synonym emp_dtls for emp;
SELECT * FROM emp_dtls;
view result is showing
View 3 Replies
View Related
Aug 22, 2012
H want to Create a view which reside in one instance for example xxx Instance . I want to create the same view which reside in xxx instance to another instance for example YYY Instance How to acheive this ?I tried desc the View take the view script from XXX instance and copied the view script to YYY instance when I compiled Iam getting an Error
For Example My view in XXX Instance
CREATE OR REPLACE FORCE VIEW xxx.sample
(
First_name,
last_name
)
as
select "F_NAME",
"L_NAME"
FROM EMP;
When try to copy and compile the same view in my YYY Instance i am getting error
View 7 Replies
View Related
Aug 7, 2013
we have the following requirement for creating materialized view.
In database "DB1", a table "abc" is there in "user1" schema.
In database "DB2", in "user2", we are accessing the "abc" through DB link "DBL1" to DB1:
select * from user1.abc@DBL1;
we have created a public synonym for "user1.abc@DBL1" as "sabc".
In database "DB3", we need to create the materialized view to access "abc" in DB1 through DB2. we are not allowed to access "DB1" directly from "DB3". so we create a db link as "DBL2" in "DB3" to "DB2".
when i try to create a materialized view as below:
create materialized view abc_mv
as
select * from sabc@DBL2;
But got the error as "synonym translation invalid". When i access the SQL statement separately as below
select * from sabc@DBL2, its working but in Materialized view, it shown that error. Even i tried with schema name as well like "user2.sabc@DBL2" , but that also thrown same error.
Is there any option available for this type of creating materialized view?
View 1 Replies
View Related
May 24, 2011
I executed the following statements
SQL> CREATE OR REPLACE Function test
2 ( name_in IN varchar2 )
3 RETURN number
4 IS
5 cnumber number;
6
7 cursor c1 is
[code]....
how I can view the full statement of creating the function.
View 1 Replies
View Related
Nov 25, 2010
Following
create or replace view dept_list as
select 1 state, 1 depth, 'DEPARTMENT' displ_value, null icn, null pk, 'A' sort_value
from dual
union
select -1 state, 2 depth, dname displ_value, null icn, null pk, 'A' sort_value
from dept
union
select -1 state, 3 depth, 'EMPLOYEES' displ_value, null icn, null pk, 'A' sort_value
from dept;
is placed in Pre_Form Trigger it generated error.
Error 103 at line 1, column 1
Encountered the symbol "create" when expected the following.
View 4 Replies
View Related
Feb 13, 2013
I am getting error (ORA - 00972 : identifier too long) while creating the view.
create or replace view ELVW_ATM_REC_HANDOFF_1(Rectype,Recseq,Record_Type,Record_Sequence,MESSAGE_TYPE,PAN,PROCESSING_CODE,TRANSACTION_AMOUNT,TRANSACTION_CUR RENCY_CODE,SETTLEMENT_AMOUNT,SETTLEMENT_CURRENCY_CODE,Billing_AMOUNT,Billing_CURRENCY_CODE,Transaction_DATE,Settlement_Date,TRACE,REF
[code]...
i have checked the length of the column name. Its less than 30 characters
View 4 Replies
View Related
Aug 17, 2011
i am getting ORA-04063 error while creating view in oracle.
View 2 Replies
View Related
Apr 6, 2012
When i try to create a trigger , i ended up with error.
SQL> create or replace
2 TRIGGER LOGON_TRG AFTER LOGON ON DATABASE
3 BEGIN
4 INSERT
5 INTO
6 user_audit_log
7 SELECT
8 user#,
[code]....
Warning: Trigger created with compilation errors.
SQL>
SQL> show error
Errors for TRIGGER LOGON_TRG:
LINE/COL ERROR
-------- -----------------------------------------------------------
2/5 PL/SQL: SQL Statement ignored
17/17 PL/SQL: ORA-00942: table or view does not exist
The command used to resolve the error is
GRANT SELECT ON v_$session TO jack;
Jack user has sysdba privilege. My question is 'sysdba' is a super and special user which has all the privileges in database. Then why does it need SELECT privilege on v$session to user to create the trigger?
View 3 Replies
View Related
Dec 5, 2012
This is just for learning purposes. What if I would like to create my own materialized view utilizing only tables and a job.
View 28 Replies
View Related
Jul 18, 2013
I am trying to create a commit on refresh materialized view so that whenever I perform DML followed by commit on Master table it should get quickly reflect on my Materialized view. I have given SELECT on table privilege to user where this view will be created On Master:
GRANT SELECT ON HR.EMPLOYEES TO SCOTT. From Scott:CREATE MATERIALIZED VIEW scott.hremp REFRESH FAST ON COMMITAS SELECT * FROM HR.EMPLOYEES; ERROR at line 4:ORA-01031: insufficient privileges Then I Grant all on EMPLOYEES table to SCOTT. GRANT ALL ON hr.EMPLOYEES TO SCOTT; Then got following error while creating materilized view:SQL> CREATE MATERIALIZED VIEW scott.hremp 2 REFRESH FAST ON COMMIT 3 AS 4 SELECT * FROM HR.EMPLOYEES;SELECT * FROM HR.EMPLOYEES *ERROR at line 4:ORA-23413: table "HR"."EMPLOYEES" does not have a materialized view log I create materialized view log on Master:SQL> CREATE MATERIALIZED VIEW LOG on HR.EMPLOYEES;Materialized view log created.
Even after this I am not able to create materialized view and now getting Table not Exist error:
SQL> CREATE MATERIALIZED VIEW scott.hremp 2 REFRESH FAST ON COMMIT 3 AS 4 SELECT * FROM HR.EMPLOYEES;SELECT * FROM HR.EMPLOYEES *ERROR at line 4:ORA-12018: following error encountered during code generation for "SCOTT"."HREMP"ORA-00942: table or view does not exist. Also I want to know why I needed to GRANT ALL to HR for creating this materialized View.
View 6 Replies
View Related
Jul 9, 2013
Oracle Developer Suite 10g
I want to create List item with time .
like in pic when I click on upper side at list item increase time and other side decrease time.
View 1 Replies
View Related
