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


ADVERTISEMENT

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

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

ORA-08103 - Object No Longer Exists?

Sep 10, 2011

I am facing a problem in my database that whenever I load any kind of database objects in a particular tablespace then it gives:-

ORA-08103: object no longer exists

If I load in any other tablespace, everything works fine. I am getting error while export/import, create a table etc. How can I resolve it. I was wondering that may be it is facing problem while writing to a particular datafile.

View 4 Replies View Related

Forms :: Form Exists When Using DataLink

Dec 5, 2011

I am using Forms 6i and oracle 10 g . I am able to use the datalink from the SQL. But when i put it in the form, and try to compile, it exists from the form builder.

View 10 Replies View Related

SQL & PL/SQL :: Replace Not In / Not Exists With Outer Join

Aug 30, 2010

I have a basic requirement to report rows from one table where the second table do not have the same record (basically same key).I understand that we could achieve this using not in or not exists clause. But I think using outer join probably be the simplest one. However, I could not achieve what I actually wanted using outer join

Test Case:

create table tab_1 (a number(1), b varchar2(20));
insert into tab_1 values (1,'one');
insert into tab_1 values (2,'two');
insert into tab_1 values (3,'three');

create table tab_2 (a number(1), b varchar2(20));
insert into tab_2 values (1,'one');
insert into tab_2 values (2,'two');
commit;

Now, I framed the query as

select y.a ya, x.a xa, x.b xb
from tab_1 x, tab_2 y
where x.a = y.a (+);
/

YA XA XB
---- ---- ----
1 1 one
2 2 two
3 three

In this case, as there is no corresponding record in second table, the value of YA is showing as null as shown above.Hence, I changed the query as

select y.a ya, x.a xa, x.b xb
from tab_1 x, tab_2 y
where x.a = y.a (+)
and y.a(+) is null;
/
YA XA XB
---- ---- ----
1 one
2 two
3 three

I dont understand why it is behaving in that way. I am rather expecting the output to come as:

YA XA XB
---- ---- ----
3 three

View 5 Replies View Related

SQL & PL/SQL :: Columns From Table Used In Exists Block?

Jun 22, 2011

I have 2 queries one which gives me unique records and second which I created using EXISTS to eliminate duplication.i.e. First query gives me 4 records but when I put the table which is in the EXISTS block in the normal join, it gives me 8 records.

But the issue is I want data from the query which I have put in the EXISTS block.

View 8 Replies View Related

SQL & PL/SQL :: Find Given Month Already Exists In Database

Apr 3, 2011

if i have data like this

case 1
code start month end month
winter march march
summer april january
spring feburary feburary

case 2

winter January January
summer february september
spring october december

and if i am trying to enter again start month and end month . i should validate if the month i am giving is already present in the table for any of the season

i want a do the validate with a query so that i will not enter duplicates.

View 5 Replies View Related

SQL & PL/SQL :: How To Retrieve Data That Does Not Exists In A Table

Dec 13, 2011

I have the following table with the some sample data:

|| Filename || receiving_site || originating_site ||
| fileA | siteA | HQ |
| fileA | siteA | siteA |
| fileB | siteA | HQ |
| fileA | siteB | HQ |
| fileA | siteB | siteB |
| fileB | siteB | HQ |
| fileB | siteB | siteB |

The table works in such way:

1. For each of the Filename, if originating_site is not the same as receiving_site, it means that the file has been sent to receiving_site but has not been acknowledged received yet.

2. For each of the Filename, if originating_site is the same as receiving_site, it means the file has been sent and received by the receiving_site.

My task is to list out all the Filename per receiving_site that has been sent, but not received yet by the receiving_site. For example from the sample data above, I am expecting to see that siteA, fileB has not been received yet.

How can I do that? I had tried MINUS and NOT EXISTS command, but I am just not able to get the result that I want.

View 2 Replies View Related

SQL & PL/SQL :: How To Check Record Exists Validation

Jan 18, 2011

1. Below TXN_LOG table Expected to have 0.5 to 1 million records & TXN_LOG_HIST table will have max 3 million records.

2. On daily basis system will move records from TXN_LOG to TXN_LOG_HIST.

3. The below mentioned check will be performed for each transaction, With out this check currently my process is doing 10tps. (transaction per second).

CREATE TABLE TXN_LOG
(
REFNO VARCHAR2(16) PRIMARY KEY,
LOCATION VARCHAR2(3),
AC_NO VARCHAR2(20),
TXN_INIT_DATE DATE,
AMOUNT NUMBER(22,3),

[code]....

The below validation we are currently using which is overhead for our transactions processing. Also the below mentioned check will be performed for each transaction, With out this check currently my process is doing 10tps. (transaction per second).

BEGIN
SELECT COUNT(1)
INTO l_exists
FROMTXN_LOG
WHERE AC_NO = p_acho
TXN_INIT_DATE = p_txn_date
AMOUNT = p_amt

[code]....

Is there any fastest method to check above record exists validation from the both the table?

View 11 Replies View Related

ORA-08103 / Object No Longer Exists

Aug 30, 2013

Select query will run fine. But when will run full query (insert + select). we will get below error after 8-10Hrs. No rows insert on table but table is exist.

Oracle Database Version: 11.2.0.3

17:06:30 SQL> @PNLP.sql
17:06:46 128 /
insert into SN.I$_GLBL
*
ERROR at line 1:
ORA-08103: object no longer exists

[code]....

Note: PNLP.sql : Script contain insert + select statement:Select query don't have any issue but while running full query we are getting above error.

View 4 Replies View Related

SQL & PL/SQL :: Update Query Using Case When Exists?

Jan 4, 2013

I am trying to use the below query

update t_emp set TTL_FLG =
CASE
WHEN EXISTS
(SELECT 1 from Schema1.T_STG_LW_EMP E
WHERE E.Employee = Schema2.T_emp.EMPLOYEE_NUMBER
AND E.JB_CODE like '%TP%' or E.JB_CODE like '%DGD%' or E.JB_CODE like '%PDD%'
or E.JB_CODE like '%YND%'
)
THEN 'Y'
ELSE 'N'
END;

View 1 Replies View Related

SQL & PL/SQL :: ORA-08103 / Object No Longer Exists

Feb 25, 2010

Problem : print ref cursor gives error (ORA-08103: object no longer exists)/ and message "no rows selected" if ref cursor is based on GTT and commit is called in procedure.

CREATE GLOBAL TEMPORARY TABLE gtt_RB
(
A number
)
/
CREATE TABLE t_RB
(

[code]....

now with commit ;

create or replace procedure p_RB ( p_in in number, c out sys_refcursor)
as
begin
update t1_RB set a = p_in ;
insert into gtt_RB(A) values (1);

[code]....

no rows selected

1/ Once values are selected in ref cursor why it does not shows result set outside ?

2/ Are ref cursors are binded with temp tables ?

3/ if 'YES' is that should be the same case with normal tables ?

4/ Commit is necessary preserve incremental value in t1_rb how we commit with using ref cursors ?

5/ Is it a Bug?

View 9 Replies View Related

SQL & PL/SQL :: Error / Table Or View Does Not Exists

Dec 7, 2010

login as system

execute statement
SELECT * FROM scott.emp;

works, how ever when we try to create view as

CREATE OR REPLACE VIEW v_emp AS
SELECT * FROM scott.emp;

then it gives error that table or view does not exists...

View 12 Replies View Related

SQL & PL/SQL :: Query In Vs Exists - Both Are Giving Different Output?

Feb 3, 2012

SQL> select count(*) from emp where empno not in ( select mgr from emp );

COUNT(*)
----------
0

SQL> select count(*) from emp T1
2 where not exists ( select null from emp T2 where t2.mgr = t1.empno );

COUNT(*)
----------
9

I fired above query and I totally confused that why both are giving different output.

View 6 Replies View Related

SQL & PL/SQL :: Rewrite Query With NOT EXISTS Using Analytic Functions

Aug 25, 2011

How to re-write sql with NOT EXISTS using analytic functions?I have the following sql:

SELECT f1, f2
FROM t1 A2
WHERE NOT EXISTS (SELECT 1 FROM t1 A3 WHERE A3.f1=A2f1 AND A3.f2=A2.f2
AND A3.f3=A2.f3 AND A3.f4 = 0
)

View 6 Replies View Related

SQL & PL/SQL :: Advance Sub-queries (Exists With Correlated Subquery)?

Jul 18, 2013

concept of Exists, With and Correlated Subquery in Sql plus 10g from begining, what are they how do they work with step by step example from basic.

View -1 Replies View Related

Security :: ORA-00942 Table Or View Does Not Exists

Aug 31, 2010

I am receiving an "ORA-00942 Table or View does not exists" error message when I try to run the following SQL statement:

SELECT id_status INTO :ls_id_status
FROM USER_ADMINISTRATION
WHERERTRIM(user_id) = :as_userid;

where :as_userid is an argument passed to the SQL.

Here are some background facts in order to save time regarding actions already taken:

(1) The userid used to login successfully is the same one passed to the above SQL;

(2) I have confirmed that the table does exist - we are using it in an application;

(3) The case of the table name is upper case as seen above and is correct;

(4) The spelling of the table name is correct;

(5) The schema name was prefixed to the table and I still got the same error (not shown above);

(6) The userid that I used is assigned to a role and the role has SELECT permissions to the above table.

View 8 Replies View Related







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