I'm trying to find all the fields from tbl1 that have different values

[hoggle]

I'm simply trying to find all the fields from tbl1 (MAST) where a row is different from tbl2 (TRANS)

Here's my query which seems like it *should* work, but it isn't returning anything at all.
SELECT MAST.*
FROM QueryTrx..SO_90_UDF_Sales_Order MAST, QueryMaster..SO_90_UDF_Sales_Order TRANS
WHERE
--UNIQUE KEY
TRANS.SalesOrderNumber=MAST.SalesOrderNumber
--UPDATEABLE FIELDS
AND (TRANS.Delivery_Date<>MAST.Delivery_Date
OR TRANS.Revision<>MAST.Revision)

so basically I'm joining on the pkey, and then seeing if any of the other fields are different. Here's some sample data.

for MAST
SalesOrderNumber Delivery_Date Revision
====================================================
701 2000-05-31 00:00:00.000 99
702 2000-05-31 00:00:00.000 NULL
703 2000-05-31 00:00:00.000 1

for TRANS
SalesOrderNumber Delivery_Date Revision
====================================================
701 2000-05-31 00:00:00.000 NULL
702 2000-05-31 00:00:00.000 NULL
703 2000-05-31 00:00:00.000 1


So 701 should come out because it is NULL in the TRANS, but 99 in MAST, but isn't catching this.
However my query will catch if the MAST table's value isn't NULL and is just different...this makes no sense to me.

thanks for any insight

 

[swampBoogie]

select * from mast m
where not exists (select * from trans t
where t.salesordernumber = m.salesordernumber
and t.deliverydate = m.deliverydate
and (t.revision = m.revision
or (t.revision is null and m.revision is null)))

 

[hoggle]

swampBoogie, thanks for the quick reply.
That fixes the problem I posted, but the I'm still having difficulty because I only gave a subset of the cols. Some of the tables I need to check have upwards of around 120 cols, which would require to check 120 cols for different values as well as if both are NULL.
Is this the only way to find out if a NULL value has been changed? Seems to me like there should be a simpler way to do it.

thanks again

 

[along73]

HI,
Null value is being ignored and you need to convert that field value with a blank before you make a join, if not, since you only have 3 columns to deal with, you can use this code (below)

SELECT MAST.*
FROM QueryTrx..SO_90_UDF_Sales_Order MAST, QueryMaster..SO_90_UDF_Sales_Order TRANS
WHERE
TRANS.SalesOrderNumber=MAST.SalesOrderNumber
AND
(
TRANS.Delivery_Date<>MAST.Delivery_Date
OR TRANS.Revision<>MAST.Revision
OR (TRANS.Delivery_Date IS NULL
AND (MAST.Delivery_Date IS NOT NULL
OR MAST.Delivery_Date <> ''))
OR (MAST.Delivery_Date IS NULL
AND (TRANS.Delivery_Date IS NOT NULL
OR TRANS.Delivery_Date <> ''))
)

 

[hoggle]

I got something that works...it still seems to me that there should be an easier way but...for those that are following this post, this will work.

SELECT MAST.* FROM QueryTrx..SO_90_UDF_Sales_Order MAST, QueryMaster..SO_90_UDF_Sales_Order TRANS
WHERE
--UNIQUE KEY
TRANS.SalesOrderNumber=MAST.SalesOrderNumber
--UPDATEABLE FIELDS
AND (CASE WHEN TRANS.Delivery_Date IS NULL THEN '' ELSE TRANS.Delivery_Date END <>CASE WHEN MAST.Delivery_Date IS NULL THEN '' ELSE MAST.Delivery_Date END
OR CASE WHEN TRANS.Revision IS NULL THEN '' ELSE TRANS.Revision END<>CASE WHEN MAST.Revision IS NULL THEN '' ELSE MAST.Revision END)