Hello, All:
Could you help me to come up with a right sql query.
What i am trying to achieve is The data in source table A is available in target table B.
Here Product_Source and Product_Target_1 are the source and target tables.
What i need to get from the resulting SQL query is to output the rows
(1) which has any one of the column having a different value(meaning the source column value is different from target column value) has to be in the output
(2)If any column value is different then source and target values are to be displayed, but other column values which has same/equal values should display null or 0.
The idea is to capture the difference and remove the distraction of equal values from source and target
SELECT
A.Product_IDN,
B.Product_IDN,
A.Name,
B.Name,
A.Quantity,
B.Quantity,
A.Price,
B.Price
FROM Product_Source A
LEFT JOIN Product_Target_1 B ON A.Product_IDN = B.Product_IDN
WHERE A.Product_IDN NOT IN
(
SELECT DISTINCT B.Product_IDN
FROM Product_Target_1 B
INNER JOIN Product_Source A ON A.Product_IDN = B.Product_IDN
WHERE
B.Name = A.Name AND
B.Quantity = A.Quantity AND
B.Price = A.Price
)
CREATE TABLE Product_Source (
Product_IDN NUMBER(5) PRIMARY KEY,
Name VARCHAR2(15) NOT NULL,
Quantity NUMBER(9),
Price NUMBER(7,2),
BatchName VARCHAR2(15),
PowerProduct VARCHAR2(1),
CreationDate DATE DEFAULT (sysdate));
CREATE TABLE Product_Target_1 (
Product_IDN NUMBER(5) PRIMARY KEY,
Customer_IDN NUMBER(5),
Name VARCHAR2(15) NOT NULL,
Quantity NUMBER(9),
Price NUMBER(7,2),
Dummy11 VARCHAR2(15));
CREATE TABLE Product_Target_1 (
Customer_IDN NUMBER(5) PRIMARY KEY,
BatchName VARCHAR2(15),
PowerProduct VARCHAR2(1),
Dummy21 NUMBER(9),
Dummy22 NUMBER(7,2),
Dummy23 VARCHAR2(15));
DELETE FROM Product_Source;
DELETE FROM Product_Target_1;
DELETE FROM Product_Target_1;
INSERT INTO Product_Source (Product_IDN, Name, Quantity, Price, BatchName, PowerProduct)
VALUES (101, 'WATER', 150, 20.45, 'LalaWala', 'Y');
INSERT INTO Product_Source (Product_IDN, Name, Quantity, Price, BatchName, PowerProduct)
VALUES (102, 'MILK', 140, 6120.45, 'BalaWala', 'N');
INSERT INTO Product_Source (Product_IDN, Name, Quantity, Price, BatchName, PowerProduct)
VALUES (103, 'FRUITS', 130, 2220.45, 'MalaWala', 'Y');
INSERT INTO Product_Source (Product_IDN, Name, Quantity, Price, BatchName, PowerProduct)
VALUES (104, 'VEGETABLE', 3120, 20.45, 'KalaWala', 'N');
INSERT INTO Product_Source (Product_IDN, Name, Quantity, Price, BatchName, PowerProduct)
VALUES (105, 'MEAT', 110, 420.45, 'FalaWala', 'Y');
INSERT INTO Product_Target_1 (Product_IDN, Customer_IDN, Name, Quantity, Price, Dummy11)
VALUES (101,1101, 'WATER', 150, 20.45, 'LalaWalaValue');
INSERT INTO Product_Target_1 (Product_IDN, Customer_IDN, Name, Quantity, Price, Dummy11)
VALUES (102,1102, 'MILK1', 140, 6120.45, 'BalaWalaValue');
INSERT INTO Product_Target_1 (Product_IDN, Customer_IDN, Name, Quantity, Price, Dummy11)
VALUES (103,1103, 'FRUITS', 1301, 2220.45, 'MalaWalaValue');
INSERT INTO Product_Target_1 (Product_IDN, Customer_IDN, Name, Quantity, Price, Dummy11)
VALUES (104,1104, 'VEGETABLE', 3120, 201.45, 'KalaWalaValue');
INSERT INTO Product_Target_1 (Product_IDN, Customer_IDN, Name, Quantity, Price, Dummy11)
VALUES (106,1105, 'MEAT', 110, 420.45, 'FalaWalaValue');
INSERT INTO Product_Target_2 (Customer_IDN, BatchName, PowerProduct, Dummy21, Dummy22, Dummy23)
VALUES (1101, 'LalaWala','Y',150, 20.45, 'LalaWalaValue');
INSERT INTO Product_Target_2 (Customer_IDN, BatchName, PowerProduct, Dummy21, Dummy22, Dummy23)
VALUES (1102, 'BalaWala','N',140, 6120.45, 'BalaWalaValue');
INSERT INTO Product_Target_2 (Customer_IDN, BatchName, PowerProduct, Dummy21, Dummy22, Dummy23)
VALUES (1103, 'MalaWala','Y',130, 2220.45, 'MalaWalaValue');
INSERT INTO Product_Target_2 (Customer_IDN, BatchName, PowerProduct, Dummy21, Dummy22, Dummy23)
VALUES (1104, 'KalaWala','N',3120, 20.45, 'KalaWalaValue');
INSERT INTO Product_Target_2 (Customer_IDN, BatchName, PowerProduct, Dummy21, Dummy22, Dummy23)
VALUES (1105, 'FalaWala','Y',110, 420.45, 'FalaWalaValue');