PL/SQL :: Select Exists On Time

Sep 17, 2013

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'?

View 4 Replies


ADVERTISEMENT

IF EXISTS (Select) - Enter Into Table One Period At A Time?

Apr 21, 2011

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

[code]...

View 1 Replies View Related

SQL & PL/SQL :: Select Data From Test-1 Table Where ID Values In Table Exists In 2?

Aug 31, 2010

I have the below data in table test_1.

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)

View 4 Replies View Related

SQL & PL/SQL :: Time Based Select

May 24, 2010

The following query gives error.

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.

What change is needed?

View 9 Replies View Related

PL/SQL :: Select Time From Sysdate

Sep 20, 2013

I have to concatenate a date(not sysdate) with the system time and store it in a column having datatype as DATE... So I tried like this....

SELECT TO_CHAR (C_DATE, 'DD-MON-YYYY')      || ' '    || TO_CHAR (SYSDATE, 'HH:MI:SS PM')    FROM   DUAL; 

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.

View 6 Replies View Related

Last Date / Time When Index Used For Select

Jul 26, 2011

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?

View 2 Replies View Related

PL/SQL :: Select Data Which Is Inserted At Same Time?

Mar 20, 2013

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

View 11 Replies View Related

Select Statement To Oracle DB - Time Converting?

Aug 27, 2009

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

View 1 Replies View Related

Table Has No Rows - Select Taking Time

Aug 23, 2010

We were trying to insert approx 76 million records worth of approx 4 GB in a table when the insert operation failed with the archive log error.

The database was hanged, we re-started the database and currently there are no records in the table but when we are firing a select

select count(*)
from table

It's taking approx 2 mins to come out with the result.

We checked and found that there are no locks on the table currently.

what do we need to do to get the performance back

View 5 Replies View Related

Oracle 11.2.0.3.0 - Select Query Taking Time

Jan 24, 2013

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.

View 1 Replies View Related

Time Format - Way To Change My Select To Creation

Jul 18, 2012

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?

View 5 Replies View Related

SQLPLUS Select Statement To Get Last Failed Time From Each User

Aug 13, 2010

I'm new to the sqlplus,

User ID Failed_timestamp
----------------------------------
userA 20100801 11:22:33
userB 20100802 11:22:33
userA 20100802 12:22:44
userA 20100803 13:34:55
userC 20100803 20:21:23
userB 20100804 16:34:56

I wanna to get the last failed time from each user. what is the select statement?

my expected result will be:

userA 20100803 13:34:55
userB 20100804 16:34:56
userC 20100803 20:21:23

View 3 Replies View Related

Performance Tuning :: Servers Will Be Running SELECT Which Returns Zero Rows All Time

Feb 11, 2011

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.

View 6 Replies View Related

Performance Tuning :: Select Query Taking Time Even After Using Parallel Hint?

Sep 25, 2013

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

[code]....

Explain plan output:

Plan hash value: 2779236890
-----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name| Rows| Bytes | Cost (%CPU)| Time| Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT || 1 | 29 | 9633M (8)|999:59:59 |||
|* 1 | FILTER |||| ||||
| 2 | PARTITION RANGE ALL || 738M| 19G| 6321K (1)| 21:04:17 | 1 | 6821 |

[code]....

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (<not feasible>)
4 - filter("PRODUCTID"=:B1)
5 - filter(ROWNUM<100)
12 - access("MODELID"=:B1)

[code]....

Note: - SQL profile "SYS_SQLPROF_014153616b850002" used for this statement

View 2 Replies View Related

Application Express :: Assigning Value To Select List Only First Time Page Loads

Jan 3, 2013

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?

View 1 Replies View Related

Reports & Discoverer :: When Printing Each Time User Have To Select File / Page Setup

Oct 21, 2011

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.

View 1 Replies View Related

SQL & PL/SQL :: Not In Not Exists?

Aug 29, 2012

I have a question i wanted to know that " Is it possible to write the Following Query Using NOT EXISTS

SELECT * FROM DEPT WHERE DEPTNO NOT IN (SELECT UNIQUE DEPTNO FROM EMP);

And one more doubt is there, can we use join to get same result.

View 9 Replies View Related

Datafile 11 - Not Exists

Aug 3, 2009

i see in my alert.log this message:

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.

View 11 Replies View Related

PL/SQL :: Check Given Value Exists Or Not

Jun 18, 2013

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

View 5 Replies View Related

How To Check A Table If Exists

Oct 12, 2005

I have a script like this:

------------------------------------------------------------

DROP TABLE CON_TEST CASCADE CONSTRAINTS ;

CREATE TABLE CON_TEST (
IDI NUMBER(10,0),
USERID VARCHAR2(10),
PWD VARCHAR2(10),
NOTE VARCHAR(100)
)

----------------------

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)

View 9 Replies View Related

SQL & PL/SQL :: EXISTS Not Working In Oracle 10g

Mar 30, 2012

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.

SELECT A.ENTITY_CODE,
A.VRNO,
A.VRDATE,

[Code]....

View 4 Replies View Related

SQL & PL/SQL :: Checking Whether Record Exists

Jan 22, 2013

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;

[Code]...

View 7 Replies View Related

SQL & PL/SQL :: Function Returning Top Value If Not Exists Then Next One?

Nov 20, 2010

how to write a function that returns top value if not exists then next top for combination of customer_id and hierarchy.For instance :

If I've got table

customer_id ,hierarchy, function_code
123 |1 | Z1
123 |2 |67
123 |3 |5B
678 |10 |S2
678 |11 |Z2
345 |2 |11

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.

View 9 Replies View Related

SQL & PL/SQL :: Using Where Not Exists Against A List Of Values?

Jul 13, 2012

I have a list of values from a spreadsheet and want to know which values are NOT matched in columns of a table

here's the list (really 4000+ long)

1234,
2345,
3244,
and I want to find the values that are not in the table 'table_name' like this
....

where not exists (Select number_n from table_name
where number_n in ('1234', '2345', '3244', ...(the list above))

View 11 Replies View Related

SQL & PL/SQL :: How To Check If Primary Key Exists

Jan 19, 2010

I have a table called TRANS, and a primary key field tran_id. How would i check if there is a record matching tran_id 'DUP7927' ?

View 25 Replies View Related

PL/SQL :: How To Check Particular Attributes Exists Or Not In XML Tag

Aug 14, 2012

As per the earlier post,I am able to parse now.

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.

<?xml version="1.0" encoding="UTF-8"?>
<pi:Extract_Employees xmlns:pi="urn:com.workday/picof">
<pi:Employee>
<pi:Employee_ID>1100</pi:Employee_ID>

[Code]....

OUTPUT:
EmployeeID_ Name JobTitle_     Grade ActualComp_Change_
1100     Surana     Intern - Master¿s     A     500000
1000     roy     Intern - Master¿s     B     216000
1000     roy     Intern - Master¿s     00     266000

But my requirement is to display only those employeeID ,where Actual_Comp_Change tag having PriorValue attribute.

The required OutPUT should be :

EmployeeID_ Name JobTitle_     Grade ActualComp_Change_
1100     Surana     Intern - Master¿s     A     500000

is there any possibility to use ExistNode() function to the above quer or is there any alternative solution.

View 5 Replies View Related

SQL & PL/SQL :: Difference Between IN And EXISTS Operator

Jan 10, 2012

what the difference between IN and EXISTS operator. Why should we use EXISTS operator?

View 1 Replies View Related

Insert If Not Exists Else Update

Aug 10, 2007

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).

View 4 Replies View Related

SQL & PL/SQL :: Rewrite Query Using NOT EXISTS Instead Of NOT IN?

Jul 12, 2012

How to rewrite this query using NOT EXISTS instead of NOT IN

SELECT STUD_ID,
join_date,
stud_nme
FROM code_balance

[code]...

View 1 Replies View Related

Difference Between NOT IN - MINUS - NOT EXISTS / EXCEPT Operators?

Nov 19, 2011

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?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved