SQL & PL/SQL :: Avoiding Repetition Of Condition In Not Exist Clause?
Dec 7, 2010
Given two tables in a 1:N relationship, I want to retrieve, for each parent row, any child row which either satisfies some condition cond1 or, if no child rows matching cond1 exist, which satisfies another condition cond2.
I can easily write such a query using NOT EXISTS, but I am forced to repeat the condition cond1. This is inconvenient, as the repeated condition is quite long (involving other tables as well), and I would like to write it only once.
For example (using the HR schema included in Oracle 10g XE): I want to retrieve, for each department, all the "new" employees (hired after a certain date) or, if no "new" employees exist, the department manager.
SELECT d.department_id,
d.department_name,
d.manager_id,
e.employee_id,
e.first_name,
e.last_name,
[code].....
View 3 Replies
ADVERTISEMENT
Oct 11, 2012
i am using one if exist condition in sql server that is like this.
if exists(select 1 from ShiftEmployee where Empid=@EmpID and Month=@month and year=@year)
begin
some value is passing here...
end
how to use the same in oracle?
View 4 Replies
View Related
Jul 22, 2010
Based on same salary i need to retrieve data like below whose hire date should be within a week (7days). The table may have 200000 records.
empno Hiredate salary
1234 03-JUN-10 3000
1354 21-MAY-10 3000
1834 01-JUL-10 3000
1954 08-JUL-10 3000
View 9 Replies
View Related
Jul 23, 2013
,I have a queryIn my query iam passing parameters using IN clause.The parameters contains in two tablesfor ex..select a.deptno, b.deptnofrom dept1 a, dept2 bwhere a.deptno = b.deptnoand NVL (a.deptno, b.deptno) in (10, 20, 30,.....) is this correct way to use NVL like this.i have a deptno consists in both the tables. How can i use this condition.
View 4 Replies
View Related
Sep 15, 2011
I have the following situation and need support:
create table try_x
(a number PRIMARY KEY,
b NUMBER,
c NUMBER,
f_text VARCHAR2(10));
insert ALL
into try_x values (0,1,1,'abc')
into try_x values (1,1,1,'abc')
into try_x values (2,1,1,'xyz')
into try_x values (3,1,2,'abc')
into try_x values (4,1,2,'abc')
into try_x values (5,1,2,'abc')
into try_x values (6,1,3,'abc')
into try_x values (7,1,3,'abc1')
into try_x values (8,1,3,'abc2')
into try_x values (9,1,3,'abc2')
select * from DUAL;
Although a is the PK, records with similar b,c,f_text are considered redundant and I need to delete all occurrences in the table where b, c, d are redundant and leave the unique ones. So I need the result to look like:
a b c f_text
-----------------
0 1 1 abc
2 1 1 xyz
3 1 2 abc
6 1 3 abc
7 1 3 abc1
8 1 3 abc2
View 15 Replies
View Related
Jan 5, 2012
I have table like below.
Name Gender
----- ------
f1 Female
f2 Female
m1 Male
m2 Male
f3 Female
m3 Male
m4 Male
but I need the output like below
Male Female
----- ------
m1 f1
m2 f2
m3 f3
m4
I have tried to get the above O/p but getting along with null values.
SQL> ed
Wrote file afiedt.buf
1 select case when gender='male' then name end male,
2 case when gender='female' then name end female
3* from details s1
4 /
MALE FEMALE
--------------- ---------------
f1
f2
m1
m2
f3
m3
m4
7 rows selected.
how the get the above o/p with out null values.
View 9 Replies
View Related
Nov 21, 2010
I've got situation where one of the procedure causing insert into table rows but some of them are not needed Temporary I need to disable this and exclude some type of data being inserted by changing procedure.
I wouldn't want to change the procedure is there a way to write a trigger that would not allow to insert a row into a table if condition is met? Something of a logic:
CREATE OR REPLACE TRIGGER avoid_insert
BEFORE (AFTER?) INSERT
if entity_id = 123 DO NOT DO THE INSERT (DO NOT DO ANYTHING)
View 6 Replies
View Related
Oct 16, 2010
I have two tables A and B
CREATE TABLE A(EMP_ID NUMBER, EMP_NAME VARCHAR2(100))
CREATE TABLE B(EMP_ID NUMBER, EMP_ATT1 VARCHAR2(10), EMP_ATT2 VARCHAR2(10))
INSERT INTO A VALUES(1, 'ONE');
INSERT INTO A VALUES(2, 'TWO');
INSERT INTO A VALUES(3, 'THREE');
[Code]....
This query returns all the matching row of A and B
SELECT A.EMP_ID, A.EMP_NAME, B.EMP_ATT1, B.EMP_ATT2
FROM A
INNER JOIN B ON A.EMP_ID=B.EMP_ID
The output for this shows:
EMP_ID EMP_NAME EMP_ATT1 EMP_ATT2
1 ONE 1ATT1 1ATT2
2 TWO 2ATT1 2ATT2
2 TWO 2ATT1.1 2ATT2.1
3 THREE 3ATT1 3ATT2
The requirement is to avoid duplicate rows even if matched:
EMP_ID EMP_NAME EMP_ATT1 EMP_ATT2
1 ONE 1ATT1 1ATT2
2 TWO 2ATT1 2ATT2
3 THREE 3ATT1 3ATT2
View 8 Replies
View Related
Jan 31, 2013
I have these records in a table:
CODE AMOUNT DESCRIPTION
AAA 5 five dollars for pizza
AAA 2 two dollars for tips
AAA 1 one dollar for dogsitting
BBB 6 six dollars for babysitting
BBB 1 one dollar for tips
My goal is to list all records, "grouping" by code, with sum(amount), but the final display has to show all descriptions, one for row, avoiding to repeat the "CODE" column and "sum(AMOUNT)" column.The result should be like this:
CODE SUM(AMOUNT) DESCRIPTION
AAA 8 five dollars for pizza
two dollars for tips
one dollar for dogsitting
BBB 7 six dollars for babysitting
one dollar for tips
That is, the "CODE" is displayed only the first row, with its sum of "amount".I think I have to use the analytics functions, but I was a little stuck.
View 4 Replies
View Related
Jun 15, 2010
We are on oracle 10.2.0.4 on Solaris 10 and have a perf. issue with a bind variable using query. The query is in java application. I want to test its performance when the query doesn't use bind variable and instead uses the passed value as literal. How can it be done?
As example lets say the query is:
SQL> variable vn varchar2(20);
SQL> EXEC :vn :='ADAMS';
PL/SQL procedure successfully completed.
SQL> select * from emp where ename=:vn;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
1 row selected.
SQL> EXEC :vn :='KING';
PL/SQL procedure successfully completed.
SQL> select * from emp where ename=:vn;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10
1 row selected.
But the statements will be taken as similar statements by oracle (due to :vn). Now I want oracle to take it as literal and the change for this has to be done in java code in my actual scenario which has a different query (but conceptually it uses bind variable and I want it to use passed value as literal). How can it be done?
View 9 Replies
View Related
Apr 23, 2010
can we use something like this
"select ... order by emp from emp"
what is to be done? so that this qurey runs. no co-related subquery to be used.
View 6 Replies
View Related
Apr 23, 2013
i installed oracle XE and odac tool kit for asp.net in my pc.when i am trying to run InstallOracleProfile.sql i am getting
ORA-01917: user or role 'ORA_ASPNET_PROF_FULLACCESS' does not exist.
View 2 Replies
View Related
Feb 3, 2012
i wrote a program to that takes tablename as input parameter and returns true or false based on table exist or not
the below code works differently when table has data and when table does not have data ? how to improve the below code to make sure my function always retuns true if table exists and false if does not exist regardless of 0 records or more than one record
CREATE OR REPLACE FUNCTION is_tab_present_g (pi_tab_name IN VARCHAR2)
RETURN BOOLEAN
IS
row_cnt NUMBER := 0;
sql_stm VARCHAR2 (4000);
l_tab_name VARCHAR2 (4000);
[Code]....
my verification block
BEGIN
if is_tab_present_g('chk_pk') then
dbms_output.put_line('yessssss');
execute immediate 'drop table chk_pk cascade constraints';
else
dbms_output.put_line('nooo');
end if;
END;
/
View 8 Replies
View Related
Mar 31, 2010
I've been trying to write some code to add a column if it does not exist as the code will be run numerous times and will be parameterized in other software to run across multiple tables.
Here is what I have made so far:
DECLARE
COLEXISTS integer;
vCmdStr varchar2(4000);
[Code]....
I can't figure out where the invalid character is.
View 3 Replies
View Related
Nov 25, 2010
In Oracle11g (R1 and R2) we can create the user in +ASM instance just like normal oracle database. But If the user already exist I get an error
CREATE USER test IDENTIFIED BY test
*
ERROR at line 1:
ORA-01920: user name 'TEST' conflicts with another user or role name
So, I would like to know if there is any data dictionary view which can tell me if the user already exist or not.
View 5 Replies
View Related
Nov 22, 2011
finding the difference between IN and EXIST.
View 15 Replies
View Related
Jul 28, 2012
I need to put amount '0' if the row exist but has no amount in my "where " conditions. the orginal commad is :
select t.aaa, count (t.bbb), sum (t.ccc) from nrb t where t.vvv IN ('3','4','5','6','D','E','F') and t.ddd like '50%' and t.eee >= TO_DATE('2012/03/21','YYYY/MM/DD') and t.eee <= TO_DATE('2012/07/21','YYYY/MM/DD') group by t.aaa order by t.aaa
and the result is : "result" tab in excel atached file.i need this result: "result 2" tab in excel atached file.
View 8 Replies
View Related
Aug 27, 2010
I created a stored procedure(A) in user1@db1. This stored procedure will insert data from db1 to tables in user2@db2 using synonyms created in user1@db1 referencing table at user2@db2.
In the stored procedure (A),
insert into synonym select seq_xx.nextval from table@db1;
Then I will execute (A) in user2@db1. Execute right on (A) is given to user2@db1.Select and insert grants on db2 tables are given to user1@db1. Seq is created in db1. Compilation is successful, however when i execute (A) it has an error of
ORA-02289: sequence does not exist
ORA-02063: preceding line from db1
ORA-02063: preceding 2 lines from db2
View 6 Replies
View Related
Jan 18, 2013
delete table if exist
How do we write this code in oracle / sqlplus
View 4 Replies
View Related
Nov 20, 2012
In my 11g Oracle i have the following issue:
create table TEST
(
COL_DATE DATE
)
partition by range (COL_DATE)
(
partition TEST2012 values less than (TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
[Code]....
when i execute this i receive the following error:
ORA-02149: Specified partition does not exist?
View 2 Replies
View Related
Apr 3, 2012
I am trying to do a impdp using a network link and it fails with the ORA-31626: job does not exist.It worked with a different database on the same server. The network link is there, data pump directory exists, the read and write privileges are granted to the oracle user.There are no other data pump jobs running:
SQL> select JOB_NAME,STATE from DBA_DATAPUMP_JOBS;
no rows selected
My database details:
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
The whole error is listed below:
omdx16dd$ impdp oracle@xtst080 SCHEMAS=BRM REMAP_SCHEMA=BRM:MBR_SLN CONTENT=METADATA_ONLY DIRECTORY=DATA_PUMP_DIR NETWORK_LINK=po02
Import: Release 10.2.0.4.0 - 64bit Production on Tuesday, 03 April, 2012 17:20:33
Copyright © 2003, 2007, Oracle. All rights reserved.Password:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-00942: table or view does not exist
ORA-00942: table or view does not exist
[code]...
View 1 Replies
View Related
Apr 19, 2011
Can there be an impact on performance if below two index exists on the same table:
t_idx(col1,col2, nvl(col7,col6));
t_idx1(col1,col2,nvl(col6,col7));
View 5 Replies
View Related
Mar 21, 2013
I listed invalid object, but when compile object, say that not exist.
SQL> SELECT OBJECT_NAME, OBJECT_TYPE FROM DBA_OBJECTS WHERE STATUS='INVALID' AND OBJECT_NAME ='PCK_PIR_NFEL_MILLENIUM' AND OWNER='PIRAMIDE'
OBJECT_NAME OBJECT_TYPE
--------------------------------- --------------------
PCK_PIR_NFEL_MILLENIUM PACKAGE
SQL> ALTER PACKAGE PCK_PIR_NFEL_MILLENIUM COMPILE;
ALTER PACKAGE PCK_PIR_NFEL_MILLENIUM COMPILE
*
ERROR at line 1:
ORA-04043: object PCK_PIR_NFEL_MILLENIUM does not exist
View 2 Replies
View Related
Feb 10, 2011
I am running Oracle 10g on my machine. I have create an ER diagram in Toad Data Modeler, which includes all the keys, contraints etc.I have generated a DDL script which I want to biuld my tables with in Oracle.I have loaded the DDL script using SQL*Plus Worksheet and there are no problems.then tryed to insert some test data into my tables and I keep getting an error code of:
ORA-00942, table or view does not exist.Now I know the tables have been created and also by verifying this with the data dictionary using
select table_name
from user_tables;
TABLE_NAME
=========
Table1
Table2
Table3
.
.
etc
It then displays all 20 of my tables, as above. I am using a Visual Basic front end and I can see all 20 tables listed there also, with attribute names for each table.Also I get the following by typing:
select owner, object_type from
all objects where object name = 'Customer';
OWNER OBJECT_TYPE
------------------ -----------------
SYSADMIN TABLE
View 4 Replies
View Related
Apr 25, 2007
I created a table by doing so:
create table Playlist (Artist string, Album string, Track int, Song string, Genre string);
But I get the following error message when trying to perform a query:
SQL> select artist
2 from playlist;
from playlist
*
ERROR at line 2:
ORA-00942: table or view does not exist
I entered data in for the artist, so why doesn't it work?
View 4 Replies
View Related
Oct 30, 2012
I have two tables.
First one is
STAFF
======
STAFFNUM NAME
======== ====
1 A
2 B
3 C
SUBJECT
=======
SUBCODE SUBNAME LECTURER
======= ======= ========
A1 ABC 1
A2 EFG 2
A3 HIJ 1
I did the following query
SELECT STAFF.STAFFNUM, STAFF.NAME, SUBJECT.LECTURER
FROM STAFF,SUBJECT
WHERE STAFF.STAFFNUM NOT IN SUBJECT.LECTURER
It will show me..
B and C is not teaching A1.
A and C is not Teaching A2.
B and C is not teaching A3.
This is not what I want.
What I want is to show who is not teaching any subjects.So the expected result is only C coming out.
View 10 Replies
View Related
May 26, 2011
I just created a role as
CREATE ROLE XXXX;
Then tried to grant a privilege, but getting error as below:
grant alter on schema.table_name to XXXX;
ORA-01917: user or role 'MACH_ALTER_ROAMXDB' does not exist
View 4 Replies
View Related
Jul 11, 2011
When I create a Materialized view using:
CREATE MATERIALIZED VIEW LOAD.M_VW_ABN_PROD_921
[color=red][i]REFRESH FORCE ON DEMAND START WITH sysdate+0
NEXT (TRUNC(SYSDATE+1) + 9.5/24)[/i][/color]AS
SELECT
...............
......................
it creates a DBMS_JOB for me with the required interval.
But now my requirement is that I want to create a job on the existing MV.
Now I tried the code mentioned in the previous post to schedule the job.
This job does not work. When I tried to execute it manually using:
DBMS_IJOB.RUN(12345) as sys user is throws the follwing error.
Error listed in the alert.log file is as below:
ORA-12012: error on auto execute of job 17497
ORA-23404: refresh group "CLARITY"."M_MV_INBASKET_ANALYSIS"
does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.DBMS_REFRESH", line 23
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1
Sun Jul 10 20:07:51 2011
View 5 Replies
View Related
May 13, 2011
I am trying to run the following code. The issue i am having is when running it in a function or procedure. (the SELECT statement works on it's own - so why doesn't it would in a procedure?)
SELECT LAST_DDL_TIME from SYS.dba_objects
WHERE object_type='TABLE'
AND OBJECT_NAME = 'CONT_ASSESSMENT'
i get an error saying "PL/SQL: ORA-00942: table or view does not exist"..I a quite new to oracle / SQL.
View 1 Replies
View Related
Jun 17, 2011
I'm updating a big table with an other one (see topic
[URL]........
I was wondering if I could do it without using the WHERE EXISTS condition. Let me explain better:
create table TEST
(
ENT_SIG VARCHAR2(10),
EMP_ID VARCHAR2(10),
DATE_START DATE,
DATE_END DATE
);
create table TEST2
(
ENT_SIG VARCHAR2(10),
EMP_ID VARCHAR2(10),
DATE_START DATE,
DATE_END DATE
);
insert into TEST (ENT_SIG, EMP_ID, DATE_START, DATE_END)
values ('014', '120', TO_DATE('20080101','YYYYMMDD'), TO_DATE('20080131','YYYYMMDD'));
insert into TEST (ENT_SIG, EMP_ID, DATE_START, DATE_END)
values ('014', '121', TO_DATE('20080201','YYYYMMDD'), TO_DATE('20080228','YYYYMMDD'));
[code].....
I'm asking if there's a way to do this:
update test a
set (date_Start, date_end) = (select date_start, date_end
from test2 b
where b.emp_id = a.emp_id
[code].......
Without using the WHERE EXISTS. I don't want to make two accesses to table2, I would like instead to do something like "If subquery returns a row, use it, else keep what you have in destination table".
Is there a way to do it without entering test2 twice?
View 10 Replies
View Related