SQL & PL/SQL :: Exclude Records

Sep 19, 2011

I have a table zTEST with several products and ID's. Now I want to exclude all products which are in table zTEST_EXCL and have ID's up to ID+2. The Result is then in table zTEST_RESULT.

What is the smartest way to do this?

create table zTEST
( PRODUCT number,
ID number);
insert into zTEST (PRODUCT, ID) values ( 1 , 1 );
insert into zTEST (PRODUCT, ID) values ( 1 , 2 );
insert into zTEST (PRODUCT, ID) values ( 1 , 3 );
insert into zTEST (PRODUCT, ID) values ( 1 , 4 );

[Code]...

View 7 Replies


ADVERTISEMENT

SQL & PL/SQL :: Parameter That Exclude Some Dates

Sep 22, 2011

Any SQL Query

1) Attendance and All type of Leave including Week rest.
2) Parameter that exclude some Dates.

View 2 Replies View Related

SQL & PL/SQL :: How To Exclude Value From Random Generating Function

Jun 25, 2011

I am in urgent need of Generating Random numbers for one of the application .The number once generated is getting stored in table

For this purpose i am using Oracle In-Built function

Quote:Select round(dbms_random.value(1,30)) into a from dual;

Problem is :How to eliminate those numbers to generate which are already stored in Table.

View 6 Replies View Related

SQL & PL/SQL :: Exclude Rows While Inserting Into A Table

Jul 12, 2011

I have an insert statement like below.

insert into emp (select empno,ename,sal);

Here I need to exclude the rows having sal<0 from the SELECT query and insert those into some other table simultaneously.

View 4 Replies View Related

SQL & PL/SQL :: Include / Exclude In One Query Statement

Aug 31, 2011

how to write this query using all three tables in one query statement.

Result:

Input value of "GM" would return "GM@EMAIL.COM"

Input value of "GM" and "GMC" would return "GMC@EMAIL.COM"

If "GM" is supplied, then only return the "GM@EMAIL.COM" email.

If "GM" and "GMC" is supplied, then only return the "GMC@EMAIL.COM" email

Test Case : GM would return "GM@EMAIL.COM" email

Test Case : GM and GMC would return "GMC@EMAIL.COM" email

select email
from email a,
company b,
sub_company c
where (company = "GM" but no match found in sub_company)
(company = "GM" and sub_company_name = "GMC" match is found in sub_company)

[code]....

View 11 Replies View Related

Server Utilities :: How To Exclude Table

Feb 6, 2012

I tried to export a schema excluding some table, but expdp exit with this error:

ORA-39001: invalid argument value
ORA-39071: Value for EXCLUDE is badly formed.
ORA-00936: missing expression

The command that I use is:

expdp system/password@ORADB directory=EXPORT_ORA_DIR schemas=maxdb logfile=maxdb.log
dumpfile=maxdb.dump EXCLUDE=TABLE:"IN ('max_table_1','max_table_2')";

Where I made a mistake?

View 2 Replies View Related

Exclude Several Tables With Specific Prefixes

Jun 5, 2013

I need to exclude several tables with specific prefixes, all the tables where names do not starts with 'A_' or 'B_' or 'C_'I tried this:

EXCLUDE=TABLE:"NOT LIKE 'A!_%' ESCAPE '!' and NOT LIKE 'B!_%' ESCAPE '!' and NOT LIKE 'C!_%' ESCAPE '!'"
I have this errors:
ORA-39001: invalid argument value
ORA-39071: Value for EXCLUDE is badly formed.
ORA-00936: missing expression
ORA-06512: at "SYS.KUPM$MCP", line 3174

View 1 Replies View Related

Reports & Discoverer :: Exclude 1 Group In Report?

Nov 29, 2010

I want to show all the data except 1 organization, Report is grouped on organization.

View 2 Replies View Related

How To Exclude Externally Authenticated Users In Sql Statements

Sep 13, 2012

I want to exclude externally authenticated users from the following sql statement. i give the following command but still i get the ops$ schema.

SELECT DISTINCT GRANTEE FROM dba_role_privs WHERE GRANTED_ROLE LIKE '%DBA%' AND GRANTEE NOT IN ('SYS', 'SYSTEM','OPS$%');

GRANTEE
------------------------------
OPS$ORAB
OPS$BPRA

View 8 Replies View Related

PL/SQL :: Exclude Duplicate Values On SQL Where Clause Statement?

Jul 15, 2013

Are some posibilities to exclude duplicate values do not using sql aggregate functions in main select statement?  Priview SQL statement

SELECT * FROM
(
select id,hin_id,name,code,valid_date_from,valid_date_to
from diaries
)

[Code]....

 In this case i got duplicate of entry TT2 id 50513  In main select statement cant use agregate functions are even posible to exclude this value from result modifying only the QLRST WHERE clause (TRUNC need to be here)

View 5 Replies View Related

Query To Exclude All Rows Based On Single Row Filter?

Jun 10, 2011

I'm using Oracle 10g.

Question: How can I write query to return just ID only if all the codes for that ID end in 6.

If I use
SQLselect id from table_a where code like '%6'
then I also get ID=1, which I don't want.

TABLE_A
ID code
=======
1 100
1 106
2 206
3 306
3 336
4 400

Desired Result
ID
==
2
3

View 1 Replies View Related

Backup & Recovery :: Check The DB Size Exclude Particular Table A?

Aug 3, 2012

I am trying to calculate the compressed size of backup of my database by excluding table space ( and all the tables in that table space).

I know how to calculate the size of particular user DB size.

select sum(bytes/1024/1024)"size" from dba_segments where owner='ABC';

View 7 Replies View Related

Server Utilities :: ORA-39071 / Value For Exclude Badly Formed

Sep 1, 2011

Im trying to use IMPDP, this is my EXCLUDE:

Exclude=INDEX,STATISTICS,SCHEMA:"IN ('AWTEST','AWPROD','DBSNMP','ORCACLE_OCM','MGMT_VIEW',
'OUTLN','PAFO','PERFSTAT','RMAN','SYS','SYSTEM','SYSMAN','TSMSYS','WMSYS')

But I get this:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39071: Value for EXCLUDE is badly formed.
ORA-00920: invalid relational operator

How do I write a correct EXCLUDE?

View 3 Replies View Related

Export/Import/SQL Loader :: How To Exclude All Tables Of A Certain Type

Aug 15, 2013

How can I exclude during the import (with impdp) all tables of a certain type, es. statistics?I tried with .... STATISTICS=NONE, but this command seem ignored during the import,

View 6 Replies View Related

SQL & PL/SQL :: Exclude Single Schema From Autostats Gathering Feature In 11g

Jul 5, 2011

I need to exclude a single schema from the autostats gathering feature in 11g. The tables in this schema are analyzed at the appropriate time via the application code. The autostats gathering job sometimes kicks in at a time in which the tables are getting updated or loaded which can skew explain plans during the updates/inserts.

I've searched through the oracle documentation and cannot find a way to simply "exclude" the schema without locking it. I see it is possible to disable the autostats at the entire db level but not at the schema level.

View 5 Replies View Related

Server Utilities :: Using Expdp To Exclude The Stream Objects While Doing Import?

Feb 1, 2012

I am having one prod and one devl with prod having stream setup.

I have to refresh devl with prod , but if i will go by full expdp then db_links also get imported into the devl and may cause problem in devl.

Is there any other way using expdp to exclude the stream objects while doing import.

View 1 Replies View Related

SQL & PL/SQL :: Syntax To Substring Only English Character And Exclude Sanskrit Characters

Nov 6, 2010

i have a column full name which has the name of customer in both English and Sanskrit alphabets. I need to susbtring only the English name and exclude the Sanskrit name .

View 2 Replies View Related

Server Utilities :: Exclude View In IMPDP Using Network Link Giving Error

Jan 14, 2011

I am using below command to import a schema using network link. Command is :

impdp system directory = IMP_DIR schemas = XYZ network_link = PQR remap_schema = XYZ:XYZ exclude=view: "= 'XYZ.VW_ACCEPTDETAILS'"

This command is giving below error

LRM-00116: syntax error at 'view:' following '='

When I have tried Like in place of '=' sign i.e. EXCLUDE = VIEW:"LIKE '%VW_ACCEPTDETAILS%'" , it gives me below error:

UDI-00014: invalid value for parameter, 'exclude'

View 4 Replies View Related

Forms :: Setup Form And Database Table For Time Entry Only (exclude Date)?

Feb 19, 2010

I have a form that the user has to enter a time of an event.

The time entered will most likely be in the past and NOT the current time.

What is the best way to set up a form and a database table for time entry only (exclude the date)?

I have tried datetime on the form with format HH12:MM PM and date on the database.

I have also tried the same with a timestamp on the database.

The time always appears to get entered correctly. However, the time is always stored as AM regardless of what is specified by the user.

View 2 Replies View Related

Export/Import/SQL Loader :: How To Exclude A Partition From Schema Mode Export

Aug 2, 2012

I am using Oracle 10g Data Pump Export utility expdp. What I am trying to do is to export a single schema, except for a certain partition P in table T.

I have tried:

expdp user/pass@db dumpfile=... logfile=... exclude=table:" = 'T:P' "

It doesn't work. The whole table T gets exported.

Is there a way to exclude partitions from schema mode export?

If not, is there a way I can achieve the same with DBMS_DATAPUMP API?

View 2 Replies View Related

SQL & PL/SQL :: Oracle 10g - Update Records In Target Table Based On Records Coming In From Source

Jun 1, 2010

I am trying to update records in the target table based on the records coming in from source. For instance, if the incoming record is present in the target table I would update them in the target else I would simply insert. I have over one million records in my source while my target has 46 million records. The target table is partitioned based on calendar key. I implement this whole logic using Informatica. Looking at the informatica session log I find that the informatica code is perfectly fine but its in the update part it takes long time (more than 5 days to update one million records). find the TARGET TABLE query and the UPDATE query as below.

TARGET TABLE:
CREATE TABLE OPERATIONS.DENIAL_REGRET_FACT
(
CALENDAR_KEY INTEGER NOT NULL,
DAY_TIME_KEY INTEGER NOT NULL,
SITE_KEY NUMBER NOT NULL,
RESERVATION_AGENT_KEY INTEGER NOT NULL,
LOSS_CODE VARCHAR2(30) NOT NULL,
PROP_ID VARCHAR2(5) NOT NULL,
[code].....

View 9 Replies View Related

SQL & PL/SQL :: Procedure To Delete Records / Count Number Of Records Has Been Deleted

Feb 21, 2011

I have written the following PL/SQL procedure to delete the records and count the number of records has been deleted.

CREATE OR REPLACE PROCEDURE Del_emp IS
del_records NUMBER:=0;
BEGIN
DELETE
FROM candidate c
WHERE empid in
(select c.empid
from employee e,
candidate c
where e.empid = c.empid
and e.emp_stat = 'TERMINATED'
);
[code]....

View 6 Replies View Related

SQL & PL/SQL :: Returning List Of Records That Do Not Have Associated Records From A Sub-query

Sep 6, 2010

I am running a query in our Clarity PPM database to return a list of all Support projects. This returns a simple list of project code and project name:

The query has the project resource tables associated with it, so I am able to list all resources allocated to the project. But for now i am only selecting a DISTINCT list of projects.

Query for anyone interested:

Select distinct
Project_code,
project_name
from
niku.nbi_project_current_facts nbip,
niku.odf_ca_project cst,
niku.prtask t,
[code]........

I have a separate query which returns a list of support resources.

select res.full_name, res.unique_name , dep.description
from niku.srm_resources res,
niku.pac_mnt_resources pac,
niku.departments dep
where res.unique_name = pac.resource_code
and pac.departcode = dep.departcode
and res.is_active = 1
and description like 'IMS%'
and UPPER(dep.description) like '%SUP%'

What I need to be able to do in the first query, is return only projects that do NOT have a resource that appears in the resource list in the second query.

(the res.unique_name field in the second query can be linked to the same in the first query)

Logically, the process would be:
1. Identify Support Project
2. Identify Resources allocated to the project team
3. Compare with List of Support Resources
4. If any Resources in that list do NOT appear on the project, then return project.

View 18 Replies View Related

SQL & PL/SQL :: Differences Among Records Tables And Tables Of Records

Sep 16, 2010

In Oracle database pl/sql 11.2: describe the differences between :

records,
tables, and
tables of records.

View 11 Replies View Related

SQL & PL/SQL :: How To Get Records For Max Value Only

Sep 21, 2010

I am working on the below query:-

SELECT adv_list.advisor_code advisor_code,
adv_list.available_wip, tot_score from(
SELECT advisor.advisor_code advisor_code ,
(wip.wip_limit - SUM (DECODE
(lead_status, 'F1', 1,0)))
available_wip,advisor.tot_score

[Code]...

The below query returns :-

Advisor_code Tot_Score
------------- ---------
00015946 10
00026742 10
00081276 10
00540272 5

My requirement is to get only those records that have the max(Tot_Score).

So I should get:-

Advisor_code Tot_Score
------------- ---------
00015946 10
00026742 10
00081276 10

I am trying to put MAX(TOT_SCORE) in the select list but its not giving the desied result.

I do not want to give it in the where clause as I will have to gain put the whole subquery again.

wherein I can get the MAX tot_score records only with minimum coding.

View 2 Replies View Related

SQL & PL/SQL :: Get AVG Of All Records

Sep 6, 2013

Type Specification :
CREATE OR REPLACE TYPE ArrayCounterSum AS OBJECT
(
-- AUTHOR : CLIVE.GREGORY
-- CREATED : 03-04-2010 14:44:02 14:44:02
-- Modified : S. Glass - Removed read function to increase performance

[code]...

Type Body :
TYPE BODY ArrayCounterSum AS
2
3
4
5

[code]...

So the output will be : 6_10_15_13_14 (Sum of all rows and return as an array)My Goal is to AVG the ROWS and retrun it into array. so what change should I make in above code in order to get the AVG of all records.

View 25 Replies View Related

Get Latest Records?

Jun 9, 2011

I have a table that contains history for vehicle positions. In order to find the latest positions quickly, I've included a LATEST column that is 1 if the record is the latest position and 0 otherwise. The table is maintained via a stored procedure. The procedure first sets the latest record for the vehicle to history...

UPDATE vehicle_positions SET latest = 0 WHERE vehicle_id = <vehicle ID> AND latest = 1

It then inserts the new latest record...

INSERT INTO vehicle_positions (vehicle_id, longitude, latitude, insert_time, latest) VALUES (<vehicle_id>, <x pos>, <y pos>, SYSDATE, 1)

Is it possible for me to end up with 2 latest records?Consider this scenario...

Session #1: UPDATE vehicle_positions SET latest = 0 WHERE vehicle_id = 123 AND latest = 1
Session #2: UPDATE vehicle_positions SET latest = 0 WHERE vehicle_id = 123 AND latest = 1
Session #1: INSERT INTO vehicle_positions (vehicle_id, longitude, latitude, insert_time, latest) VALUES (123, 32.8533, -117.1180, SYSDATE, 1)
Session #2: INSERT INTO vehicle_positions (vehicle_id, longitude, latitude, insert_time, latest) VALUES (123, 32.8534, -117.1178, SYSDATE, 1)

I'd end up with 2 latest records. How can I protect against this? I considered using SELECT FOR UPDATE, but seems like there are too many negatives going that route

View 4 Replies View Related

Fetching Last N Records?

Jun 5, 2012

oracle version: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

CREATE TABLE MACHINE_M (
IDVARCHAR(20)NOT NULL,
NAMEVARCHAR(20)
)

[Code]....

Now when there records in READING_DWR are 1 lakh, i am getting the o/p in 10 sec.
However when there are bulk records, it is taking 3 to 4 min.

Is there any way to improve my query performance?

View 1 Replies View Related

Edit Records In 10g EM?

Mar 26, 2009

I just loaded the 10g client EM on my Windows workstation to connect to our 10g database.Everything works great except I dont see any place where I can edit records. I have to use SQL Plus to edit records but was hoping to use a GUI like I had with 9i client OEM.Before we had 10g, I used to have the 9i OEM and it let me edit records in 9i OEM where I could add, delete and update records with a user friendly interface.

how I can edit records in 10g EM?

View 3 Replies View Related

How To Get Alternate Records

Oct 17, 2006

how to retrive alternate records from the table in the answer table the first row wil be in upper case and the second row wil be in lower case ...like wise wat is the querry

View 1 Replies View Related







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