I have this sample:
the column data1 is datetime datatype with
t as ( select 'SMITH' nom,to_date('21/09/2013 07:30:00') data1
from dual union all select 'ALLEN',to_date('21/09/2013 07:40:00')
from dual union all -- select 'WARD',to_date('21/09/2013 07:50:00')
from dual union all
select 'JONES',to_date('21/09/2013 08:00:00')
from dual union all
[Code]..
How can I write a select to check that If I input 10 minutes to nom 'ALLEN' it's ok because the time 07:40 + 10 minutes = 07:50 the row not exists, (the next)but If input 20 it exists because the sum = 08:00 and row isn't free , indeed, there is 'JONES'?
I am trying to break down a row of data that has nine periods that I want to enter into a table one period at a time. I am trying to use "IF EXISTS ( SELECT * FROM TABLENAME WHERE FIELD1 IS NOT NULL) THEN" through all nine fields. It keeps returning lines that have a null column as well as not null columns. if there is a better way of doing this. Here is an example of what I am trying to do:
IF EXISTS (SELECT * FROM TABLE_NAME WHERE LAST_DT9 IS NOT NULL) THEN SELECT NUMBER, 1COL9, 2COL9, 3COL9, FIRST_DT9, LAST_DT9, 4COL9 FROM TABLE_NAME; ELSE IF EXISTS (SELECT * FROM TABLE_NAME WHERE LAST_DT8 IS NOT NULL) THEN
select * from test_1 IDNameTotal ----------- 1A100 2B100 3C100 4D100
test_2 table contains the concatination of ID's with comma seperated. Actually in this table ID column is of datatype varchar2. select * from test_2 ID ---- 1,2,3
My requirement is to select the data from test_1 table where the id values in this table exists in test_2 table. I tried with the belowselect statement, but could not get any data.
SELECT * FROM test_1 WHERE to_char(id) IN (SELECT id FROM test_2)
create table test_1 (id number, name varchar2(100), total number) create table test_2(id varchar2(100)) insert into test_1 values (1,'A',100) insert into test_1 values (2,'B',100) insert into test_1 values (3,'C',100) insert into test_1 values (4,'D',100)
select count(*) from invoice where inv_id in (SELECT inv_id FROM invoice_hist WHERE to_char(timestamp) between TO_char('05/12/2010 18:22:00', 'MM/DD/YYYY HH24:MM:SS') and TO_char('05/21/2010 18:22:00', 'MM/DD/YYYY HH24:MM:SS'));.
Here timestamp is of type DATE in the invlice_hist table.
But while inserting the output of the above query in my table it throws error like this... ORA-01830: date format picture ends before converting entire input string Is there any possibility to achieve this.
Is there a way I can find what the last date/time and index was used for a select...
I have a table with several indexes on them, which I beleive are not being accessed.
I use the following the query to find indexes that where not accessed in a while but this I believe is limited my my workload repository retention, which is set to 90 days.
select index_name from dba_indexes where table_name='<table name>' and index_name not in (select c1 from( select p.object_name c1, p.operation c2, p.options c3, count(1) c4 from dba_hist_sql_plan p, dba_hist_sqlstat s where p.object_owner = 'MTAS' and p.operation like '%INDEX%' and p.sql_id = s.sql_id group by p.object_name, p.operation, p.options order by 1,2,3))
Without increasing my repository retention is there a way I can get the last date/time, which an index was used instead of just saying it has not been used in 90 days (retention setting). Is this information kept in the SQL plan?
I wanted to select data which is inserted on the same time.
Is that possible? I am trying with below query but facing isses.
SQL> SELECT a,b,c,d FROM tb;
no rows selected
SQL> SELECT a,b,c,d FROM (INSERT INTO tb VALUES(1,1,1,1)); SELECT a,b,c,d FROM (INSERT INTO tb VALUES(1,1,1,1)) * ERROR at line 1: ORA-00903: invalid table name
My company use a sybase database that runs business jobs. Currently we run SQL queries from Perl to gather time information on the jobs. Now we have an application that is using Oracle. The server it is on, doesn't have perl, so I am using a shell script to login to sqlplus and run a query for a job and it's end time. I have accomplished this. However, here is the 2 problems I am having.
1. The query reults are returned in Scientific time, I'm able to convert that to EPOCH time in the SQL syntax, however, it comes back with a 13 digit time, instead of 10. The last 3 digits are zero. How can you remove the last 3 digits in the query or convert the 13 digits to Human Time. Right now when you see the select statement, I am doing a to_char to get it to EPOCH time.
2. How to only show the latest time in the query and not show ALL job end times from it's past runs.
Here is my shell script, and I do realize this maybe a select statement syntax solution to one or both, but the UNIX time stamp is puzzling.
#!/usr/bin/sh sqlplus -S username/password@JAWSPROD <<eof> myfile set heading off feedback off verify off select JAWS_APP.JAWSJOB.JOBNAME, to_char(JAWS_APP.JOBRUN.ENDTIME) from JAWS_APP.JAWSJOB, JAWS_APP.JOBRUN where JAWS_APP.JAWSJOB.JOBID = JAWS_APP.JOBRUN.JOBID and JAWS_APP.JAWSJOB.JOBNAME in ('pa_box_settle'); exit
My oracle database version is 11.2.0.3.0 where i am having one schema in that schema i am having 3 same tables with same structure same data but with different name.
but problem is in first table when i perform select query it takes 5 sec, in another table it is taking 0 sec and in third table it is taking 10 sec.
when I select I have to using AND creation_dt>='17-JUL-12' is there a way to change my select to creation_dt='17-JUL-12' ? on meantime my local format is 18-JUL-12(18-JUL-12) is thee anything I can do?
Our application servers will be running a SELECT which returns zero rows all the time.This SELECT is put into a package and this package will be called by application servers very frequently which is causing unnecessary CPU.
Original query and plan
SQL> SELECT SEGMENT_JOB_ID, SEGMENT_SET_JOB_ID, SEGMENT_ID, TARGET_VERSION FROM AIMUSER.SEGMENT_JOBS WHERE SEGMENT_JOB_ID NOT IN (SELECT SEGMENT_JOB_ID FROM AIMUSER.SEGMENT_JOBS) 2 3 4 5 ; [code]....
Which option will be better or do we have other options?They need to pass the column's with zero rows to a ref cursor.
select serialnumber from product where productid in (select /*+ full parallel(producttask 16) */productid from producttask where startedtimestamp > to_date('2013-07-04 00:00:00', 'YYYY-MM-DD HH24:MI:SS') and startedtimestamp < to_date('2013-07-05 00:00:00', 'YYYY-MM-DD HH24:MI:SS') and producttasktypeid in
I am trying to set the intial value of "Select List" to APP_USER the only first time when the page loads and use it to filter the report, any other time once the page is loaded the user should select another value from the Select List then click the submit button to display the details in the report, i tried to achieve this by setting "select List" creating LOV for Select List then set default=APP_USER but now the problem any time you click the submit button it reset the "Select List" from selected value back to default so how can you achieve this to set the value of Select List only the first time the page loads?
I have a requirement while A4 printing..When printing each time user have to select file -> page setup -> size(A4)..THEN printing format become A4 Size. How to achieve this one without user have to go on setting.
Errors in file /oracle/BWP/saptrace/usertrace/bwp_ora_2728058.trc: ORA-01114: IO error writing block to file 1030 (block # 602122) ORA-27063: number of bytes read/written is incorrect IBM AIX RISC System/6000 Error: 28: No space left on device Additional information: -1 Additional information: 180224
But this file_id i don't have in my database, i am making these queries:
SQL> select FILE_ID from dba_temp_files order by FILE_ID;
FILE_ID ---------- 1 2 3 4 5 6 7 8 9 [code]....
I don't have this file_id, why alert.log is showing me it? Of course, nobody has created this datafile and nobody has removed it too.
i have a procedure like create or replace procedure studrec( a in sid%rowtype)asi sid%rowtype;beginselect sid into ifrom students; i need to check whether sid is exist in the variable i or not
But i think if table CON_TEST doen't exist, an error message will appear. I know that in SQL Server we can check if table exists or not. So, i wonder if we can do that in Oracle?
By the way, is there any way to run a file script that contents TABLES, STORED, ... on a developed PC connect to oracle db server? (in case, i'm developing on PC, using Net Service Name to conect to Oracle DB Server)
following is a query which i find difficult to understand why EXISTS is failing. There are two scenarios where if i block LINE 30 and unblock line 31 of the code then one record is returned.
Which is the best way to check whether a record exists.
DECLARE l_count NUMBER; BEGIN SELECT COUNT(*) INTO l_count FROM emp WHERE empno=7839; IF l_count=1 THEN dbms_output.put_line('exists'); ELSE dbms_output.put_line('not exists'); END IF; END;
For the customer ID 123 I want to return Z1, for customer 678 I want to return S2 and for customer ID 345 I want 11
Problem is that I'm new to the concept of looping. I know how to write a function that accepts customer_id as a value write a cursor and then check IF hierarchy = 1 the return FUNCTION_CODE IF hierarchy - 2 THEN ...
but I need something more universal as some of the customers may have hierarchy function 1 and that would be the top one for him but others might have function of hierarchy 10 as top and checking all of the possibilities using if would be just stupid. So how to write something universal ? And of course if function did not find any customer_id then return null.
i have also another concern as per below xml file.
My requirement is to identify perticular node ,whose having PriorValue attribute present in <pi:the Actual_Comp_Change> tag,those record should return.
I have a single table with a TOTAL_TIME column which I want to increment by a certain amount every time I get a request from a specific user. If the row for that user does not exist, it should be created and the TOTAL_TIME column should be set to the value that just came in. Otherwise, if it does exist, it should be incremented by the value passed in.
How can I accomplish this in oracle? I don't want to just first do a select, then insert, because that can cause race conditions. I want something that'll do the check and insert/update in one statement (locked).
where difference lies between these operators or clause, & whether there is any "except" operator in oracle, i know other three are used bt dont think oracle uses "except" too..as far as i have seen "NOT IN", "MINUS" , "NOT EXISTS" are exactly similar?
