SQL & PL/SQL :: Using Minus Operator To Get Changed Data Info Between Two Tables
Feb 14, 2013
I am using the below query to show the difference of data between two tables using minus operator.
teh result is correct, but is there a way can it show with a flag with new rows and updated rows something like "N" for new row and "U" for updated row.
select CURRENT_STG_GLACCTS.TABLE_NAME,
CURRENT_STG_GLACCTS.ACTIVE,
CURRENT_STG_GLACCTS.BUSINESSUNITID,
CURRENT_STG_GLACCTS.COST_CENTER,
CURRENT_STG_GLACCTS.GLACCT,
[code].......
View 7 Replies
ADVERTISEMENT
Feb 12, 2013
I have a two tables with same column name , I wanted to find different record in table1 when compared with table2
create table table1(col1 number,col2 number,col3 number,col4 number,col5 number);
create table table2(col1 number,col2 number,col3 number,col4 number,col5 number);
insert into table1 values(1,2,NULL,NULL,NULL);
insert into table2 values(1,2,NULL,NULL,NULL);
commit;
select col1 from (select col1,col2,col3,col4,col5 from table1 minus select col1,col2,col3,col4,col5 from table2);
no rows selected
how come i get no rows selected when col3,col4,col5 is having null values but NULL could be anything so
NULL-NULL cannot be equal to zero how is it possible
View 3 Replies
View Related
Aug 12, 2010
I'm trying to copy a particular set of rows into two tables. I want to change one varchar column to a different string in one of the tables, and create a new unique ID for the copied rows that will match on both tables. I also want that new unique ID to be one digit higer than the current highest unique ID being used across the tables as a whole.
create table testing_query_dtl
(query_is number not null, query_type varchar2(20) not null, area_code varchar(4), stn_code varchar2(3))
create table testing_query_hdr
(user_name varchar2(30) not null, query_indicator varchar2(1) not null
, query_created date not null,
query_last_used date not null,
query_name varchar2(40) not null
, query_is number not null)
[code]...
I then used 'mymax+test_seq.nextval' but that won't avoid the unique constraint issue on the table testing_query_hdr and on the table testing_ query_ dtl increments the ID on EVERY row even when I don't want it to.
View 4 Replies
View Related
Nov 30, 2012
create a procedure so that I could get minus query of 2 table as a result.
I have two table 1- src_table_list ,2- tgt_table_list both tables have 2 columns : serial_no,table_name and 100 records each. and details mentioned in column "table_name" are actually tables name which present in my testing database.
so I need one procedure which will pick one table_name from src_table_list and one table_name from tgt_table_name each time recursively and provide minus query as a result. as below.
select c1,c2,c3,c4 from table1 --(fetched from src_table_list)
minus
select b1,b2,b3,b4 from table2 --(fetched from tgt_table_list)
create the procedure..as I have to prepare minus query for more than 200 tables and then I need to test them for integration testing..
View 11 Replies
View Related
Jan 27, 2011
I need to alter a table to check that the data in a column is contained in a similar column in another table.
I have a STORES table and a STORE_REP table. Both tables have a column REP_ID.
I need to add a CHECK constraint into the STORES table to make sure that the info entered into its REP_ID column matches an entry in the STORE_REP table.
Both have a NUMBER(5) data type.
Will it make any difference if the REP_ID column in the STORE_REP table was originally created with a VARCHAR2(5) data type and was later converted to NUMBER(5), while the REP_ID column in the STORES table was created as NUMBER(5) when that table was created?
View 2 Replies
View Related
Nov 15, 2010
How similar is PL/SQL to SQL? I'm trying to retrieve all data from a customer table and print out each customer information one by one:
DECLARE
CURSOR all_customer IS
SELECT *
FROM customer;
BEGIN
DBMS_OUTPUT.PUT_LINE(all_customer);
END;
but I really don't know the correct syntax. The Powerpoint slides the professor gave is not useful at all.
View 13 Replies
View Related
Apr 1, 2011
I have database A (Working in Live environment) and Database B copy of Database (Not live) I have Restored whole database (A) RMAN backup file on Database (B) Previous week now i don't want to change anything in any schema and want to import only updated and new records in the table in Database B
There are around 20 schema If for example i have everything in new database B all required database objects like Procedure,functions, packages with indexes in all tables and data in tables, i just want to add new data and updated data.
IF i do following in source database
expdp directory=dpump_dir dumpfile=table_data.dmp content=data_only schemas=ACCMAIN,HRMAIN,..... include=TABLE
AND Import in destination database B, will it add new data and update existing one in table and not touch the table structure and indexes.
View 5 Replies
View Related
Dec 25, 2012
I am using the following query with like 'T_%', i am getting 80 rows out of which the first table_name doesn't even have a beginning part 'T_%'.
the first table name has not started with 'T_', why is it appearing.
*********************************************************************
SELECT 'Truncate table epic500.'||table_name
FROM user_tables where table_name like 'T_%' order by table_name;
*********************************************************************
output:
Truncate table epic500.TEMP_ENC_DEL
Truncate table epic500.T_ACCOMMODATION_CODE
View 4 Replies
View Related
Jul 27, 2011
I have a entry screen, where number of rows and columns are there, now end user can change in any row and in any column multiple times, now i have to send only last changed data row and column wise to database,to complete the transaction.
View 7 Replies
View Related
Aug 29, 2013
Quote:Oracle collects new statistics when enough of the data (about 10%) has changed.I read the above statement in the oracle documentation that Oracle collects new statistics when enough of the data (about 10%) has changed every day during 10 pm to 6am .
But still i see some object statistics are stale and empty.Do i need to collect statistics for these objects manually ? and would like to know why these objects statistics were not collected during maintenance window ?
View 4 Replies
View Related
Sep 14, 2011
Oracle Database Version : 9.2.0.8.0
Some of the datafiles status have been changed to 'RECOVER', because the datafiles are physically missing.
Now, how can i find that when (timestamp) the status of the datafiles have been changed, as i am unable to find when the datafiles have been physically lost?
Please consider both the case :
1) when the database is in ARCHIVELOG Mode.
2) when the database is in NOARCHIVELOG Mode.
View 1 Replies
View Related
Apr 2, 2013
My job is running at 2 am and that time no application user is connected. Even though, my exp utility shows error on 3 tables (2 are temp tables), everyday. But when expdp is running without error, which was scheduled at 4 am.
Below are the error -
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. . exporting table DW_TEST_MOTOR
EXP-00056: ORACLE error 1466 encountered
ORA-01466: unable to read data - table definition has changed
[Code].....
View 9 Replies
View Related
Sep 25, 2012
I am experimenting with the 4.2 version of Apex at URL.....
I have created a tabular form (based on a view with an instead of trigger) and have restricted this form to "Update only" mode. Whenever I modify a field value and submit the change I get the error "Current version of data in database has changed since user initiated update process".
View 6 Replies
View Related
May 18, 2011
I have to cleanup data from our tables (Production Environment) that contain millions of rows. The question is apart from the solution of the partitioned tables what alternative recommended solution suggests Oracle?
To delete these tables by using a cursor PL/SQL block or to import all the database and in the tables that we want to remove the old rows to use the QUERY option of the data pump utility.
I have used both ways and i have to admit that datapump solution is much much faster than the deletion that suffers from I/O disk.The question again is which method from these two is more reliable and less risky for the health of the database.
View 5 Replies
View Related
Sep 3, 2012
I came across an implementation where data from DB2 tables are moved to Oracle tables, for BI solutioning, using some oracle procedures called from MS SQL DTS packages which are scheduled jobs.Just being curious, can this be done using OWB or ODI rather than the above detour. I suppose there are some changes being done in those procedures before the data is being loaded into Oracle tables, can't this be done using OWB/ODI? Can it be scheduled too as jobs using OWB/ODI?
View 1 Replies
View Related
Nov 29, 2011
I got a two tables, table the_table_1 consist of customers and the other one the_table_2 consist of channels.
And I need to get list of channels by contract which are an assigned to customers.
The select below give me a only assigned channels but I need to get a contract_key too from the_table_1.
select o.channel_name from the_table_2 o
minus
select o.channel_name from the_table_1 a, the_table_2 o
where a.contract_key=237092201
and a.offer_channel_key=o.OFFER_CHANNEL_KEY
And the select under (after minus) gives a list of non assigned channels by contract_key:
select o.channel_name from the_table_1 a, the_table_2 o
where a.contract_key=237092201
and a.offer_channel_key=o.OFFER_CHANNEL_KEY
how to rewrite a select to get required result?
View 2 Replies
View Related
Sep 28, 2011
I am looking for an explanation for having the following query:
SELECT PK_SERIAL_NUMBER FROM TABLE1 MINUS (SELECT FK_SERIAL_NUMBER FROM TABLE2);
Which normally returns the values of SERIAL_NUMBER that are not passed to the child table TABLE2
and the query:
SELECT PK_SERIAL_NUMBER FROM TABLE1 WHERE PK_SERIAL_NUMBER
NOT IN
(
SELECT FK_SERIAL_NUMBER FROM TABLE2
);
which returns 0 rows!
View 6 Replies
View Related
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
Dec 1, 2010
I wish to delete records found with function MINUS from file1, which is a result of records not found in file2.
Both codes are taken way too long.
ps. none of the columms are unique, and some records have no value in some columns
DELETE
FROM file1 y
WHERE EXIST (SELECT *
FROM (
SELECT a.col1, a.col2, a.col3, a.col4
FROM file1 a
[Code]....
View 38 Replies
View Related
Aug 2, 2011
i have two tables having some fields .In table table1 there is 2 lakh rows and in table table2 there is 3 lakh rows.In both table 2 lakh rows are common.I have to find those 1 lakh rows which are distinct but the condition for this i have to use only sql joins not minus or subquery or any function.
when i doing this.select t2.id,t2.name,t2.loc from table1 t1,table2 t2 where t1.id <>t2.id;
View 8 Replies
View Related
Sep 15, 2011
I have 2 questions for this SQL Statement for tuning?
select * from a where id not in (select a_id from b)
1- How do i change this query with A MINUS OPERATOR
2- Shall i use a Hint for this query?
Explain plan is:
Execution Plan
--------------------------------------------------------------------------------
Plan hash value: 31652112322
--------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 299 | 21528 | 9 (12)| 00:00:01
|
|* 1 | HASH JOIN RIGHT ANTI| | 299 | 21528 | 9 (12)| 00:00:01
|
|* 2 | TABLE ACCESS FULL | b | 1 | 23 | 4 (0)| 00:00:01
|
| 3 | TABLE ACCESS FULL | a | 300 | 14700 | 4 (0)| 00:00:01
|
--------------------------------------------------------------------------------
-
How do i optimize it best?
View 2 Replies
View Related
Jul 27, 2012
What is the fundamental difference between MINUS keyword and LEFT outer join in Oracle.
I can achieve same results using either one of them.
View 8 Replies
View Related
Feb 20, 2013
i know the procedure "DBMS_SQL.DESCRIBE_COLUMNS" and the example,the example uses the "dbms_sql.open_cursor",not the ref cursor. so how to get the information of ref cursor by using describe_columns? or how to get the information of ref cursor by other?
View 4 Replies
View Related
Mar 10, 2011
Is there a way to retrieve information about a table through an sql query? In particular I need to retrieve the size of some varchar columns.
View 3 Replies
View Related
Feb 26, 2013
At my Workplace we have a large Orcle 11g Database with 30 different tables for production control issues.I try to get a couple of different information from the database, so i started with SQL Query's, but for this problem i was not able to write an working query.
In this case i have 2 tables:
Table 1:
ID ;ORDER_NR ;DESCRIPTION ;CREATE_DATE
1 ;A500236 ;CLEAN HOUSE ;02/20/2012
2 ;A623555 ;REPAIR CAR ;01/10/2012
3 ;A866944 ;MAINTAIN EQUIPMENT ;02/11/2012
Table 2:
ID;ORDER_NR;WO_STEP;STEP_DATE;EMPLOYEE
1;A500236 ;A;02/21/2012;W0010
2;A500239 ;F;02/21/2012;W0010
3;A500239 ;S;02/22/2012;W0027
[code]....
And the result of my Query should look like this:
ORDER_NR;DESCRIPTION ;CREATE_DATE;A_STAT_AGE;R_STAT_AGE;U_STAT_AGE
A500236;CLEAN HOUSE ;02/20/2012 ;5 ;3 ;1
A623555;REPAIR CAR ;01/10/2012 ;42 ;39 ;38
A866944;MAINTAIN EQUIPMENT ;02/11/2012 ;15 ;4 ;3
The age of my query result should be calculated from the Create date of the Order.I want to know 2 things, one is how old was the Order when they reached that status A, R and U.The second this ist, how long did the order remain on the stat A,R and U (and if possible all other status also)It could happend that not each order reaches each status, so it ca go directly from A to you in this case i want display a wildcard in this row/column
View 2 Replies
View Related
Apr 19, 2010
For each keeper, show the number of cages cleaned by the keeper, show the average number of animals in the cages cleaned by the keeper and the total cost of the cages cleaned by the keeper."
The data table is shown on the picture.i75.photobucket. com /albums/i297/lovebipbo/SIT103.jpg.. I can learn and do some similar task myself
View 4 Replies
View Related
Jan 26, 2007
How do I get my list of user session info? I thought there was a user_session_parameter view or something? Basically, I did an ALTER SESSION ... and want to verify it was set correctly.
View 8 Replies
View Related
Jul 24, 2013
I want TO find out FIRST two employee joined IN A particular department WITH department information.THE relation IS basically FROM THE scott SCHEMA.I tried LIKE AS follows. IS there ANY other way FOR best PERFORMANCE.
SELECT deptno,dname,loc,
Max(Decode(rn, 1, hiredate))hiredate1,
Max(Decode(rn, 1, ename)) employee1,
Max(Decode(rn, 2, hiredate))hiredate2,
Max(Decode(rn, 2, ename)) employee2
FROM (SELECT d.deptno,dname,loc,hiredate,ename,Row_number() over(PARTITION BY e.deptno ORDER BY hiredate) rn
FROM dept d, emp e
WHERE d.deptno = e.deptno(+))
GROUP BY deptno,dname,loc;
View 15 Replies
View Related
Oct 11, 2012
I need to export only the data from schemas or tables, how to do that with Oracle Data Pump? when we use schemas parameter this export all schema, not only the data right?
View 7 Replies
View Related
Mar 2, 2012
Im trying to generate a member procedure that allows the user to manual enter the required information via substitution variables. The manual INSERT INTO statements work but I cant seem to get it to work within a procedure.
Here is the code for the type, table andstandard insert:
CREATE TYPE toy_typ AS OBJECT
(toy_id NUMBER ( 5),
toy_name VARCHAR2 (20),
toy_cost NUMBER ( 4),
[code]...
the procedure complies but i get a warning error. When i also try to execute the code with either set values or substitutions i get either not enough or to many values error.
View 39 Replies
View Related
