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
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
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
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
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
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
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
Nov 29, 2010
I want to show all the data except 1 organization, Report is grouped on organization.
View 2 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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