comparing the contents of 2 records

#1

I've been asked to create a backup of a record (which has 75 fields) which
can be restored if necessary. I'd hoped to be
able to tell if the current had been changed by comparing it to the backup.
I tried

with the backup by

CREATE PROCEDURE "transcend"."sp_compare_records"( IN @key1 INT )

BEGIN
IF EXISTS(
SELECT *
FROM table1 a
WHERE exists(SELECT *
FROM transcend.table1_backup b
WHERE a.key2 = b.key2
AND a.key1 = b.key1
AND a.field3 = b.field3
AND a.field4 = b.field4
AND a.field5 = b.field5
AND a.field6 = b.field6

etc,etc

AND b.key1 = @key1)
then return 1;
else return 0;
end if;
END;

I tested it using
BEGIN
DECLARE @return_code INTEGER;
@return_code = CALL sp_compare_records(55);
SELECT @return_code
END;

It sees the records as matching as long as none of the fields has a null
value. I cannot simply leave those fields out of the internal select
because different fields will be left with a null values depending upon the
circumstances (many of the fields are keys to other tables).

Does anyone have any suggestions? (I've essentially no experience with any
scripting within SQL.. will be forced to code a field by field comparison on
the client if I cannot handle in SQL)

Thanks in advance for any suggestions..

#2

Since the two tables are union-compatible, you can do this much more
simply with the EXCEPT operator, which requires a query expression, ie

select * from table1 a
except
select * from transcend.table1_backup b

EXCEPT treats NULL values as special values in each domain (like
DISTINCT and GROUP BY do).

Glenn

P Merritt wrote:

Quote:

I've been asked to create a backup of a record (which has 75 fields) which
can be restored if necessary. I'd hoped to be
able to tell if the current had been changed by comparing it to the backup.
I tried

with the backup by

CREATE PROCEDURE "transcend"."sp_compare_records"( IN @key1 INT )

BEGIN
IF EXISTS(
SELECT *
FROM table1 a
WHERE exists(SELECT *
FROM transcend.table1_backup b
WHERE a.key2 = b.key2
AND a.key1 = b.key1
AND a.field3 = b.field3
AND a.field4 = b.field4
AND a.field5 = b.field5
AND a.field6 = b.field6

etc,etc

AND b.key1 = @key1)
then return 1;
else return 0;
end if;
END;

I tested it using
BEGIN
DECLARE @return_code INTEGER;
@return_code = CALL sp_compare_records(55);
SELECT @return_code
END;

It sees the records as matching as long as none of the fields has a null
value. I cannot simply leave those fields out of the internal select
because different fields will be left with a null values depending upon the
circumstances (many of the fields are keys to other tables).

Does anyone have any suggestions? (I've essentially no experience with any
scripting within SQL.. will be forced to code a field by field comparison on
the client if I cannot handle in SQL)

Thanks in advance for any suggestions..

--
Glenn Paulley
Director, Engineering (Query Processing)
Sybase iAnywhere

Blog: http://iablog.sybase.com/paulley

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://case-express.sybase.com

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the
Sybase iAnywhere pages at
http://www.sybase.com/products/datab...chnicalsupport

#3

Besides Glenn's excellent proposal:

When comparing values and wanting null = null to be true (which it isn't
of course in three-valued logic), I sometimes use the isnull() function
with a replacement value that isn't usually a valid value for the
column, i.e. something like

WHERE ...
AND isnull(a.date1, '1000-01-01') = isnull(b.date1, 1000-01-01')
AND isnull(a.int1, -1) = isnull(b.int1, -1)

If both a.date1 and b.date1 are null, the same date value will be
compared and will return true obviously.
The problem is to find replacement values that won't be used as valid
contents.

HTH
Volker

P Merritt wrote:

Quote:

I've been asked to create a backup of a record (which has 75 fields) which
can be restored if necessary. I'd hoped to be
able to tell if the current had been changed by comparing it to the backup.
I tried

with the backup by

CREATE PROCEDURE "transcend"."sp_compare_records"( IN @key1 INT )

BEGIN
IF EXISTS(
SELECT *
FROM table1 a
WHERE exists(SELECT *
FROM transcend.table1_backup b
WHERE a.key2 = b.key2
AND a.key1 = b.key1
AND a.field3 = b.field3
AND a.field4 = b.field4
AND a.field5 = b.field5
AND a.field6 = b.field6

etc,etc

AND b.key1 = @key1)
then return 1;
else return 0;
end if;
END;

I tested it using
BEGIN
DECLARE @return_code INTEGER;
@return_code = CALL sp_compare_records(55);
SELECT @return_code
END;

It sees the records as matching as long as none of the fields has a null
value. I cannot simply leave those fields out of the internal select
because different fields will be left with a null values depending upon the
circumstances (many of the fields are keys to other tables).

Does anyone have any suggestions? (I've essentially no experience with any
scripting within SQL.. will be forced to code a field by field comparison on
the client if I cannot handle in SQL)

Thanks in advance for any suggestions..

#4

Glen - Thanks for the most Excellent suggestion!

is there any chance you've an equally elegant solution to 2 other related
problems.

1) I need to reproduce a specific set of rows (all share key1) giving them a
new key1 value.. right now I'm using a brute force INSERT listing all the
fields, a method which has the obvious disadvantage of becoming inadequate
when new fields are added to the record.

2) I need to restore the content of a specific set of rows (all share key1)
in table1 from table1_backup.. is there any chance I can do that without
listing all the fields ? (I guess that if push comes to shove, I could do a
delete and insert in place of an update)

Thanks again !

-----------------------
"Glenn Paulley [Sybase iAnywhere]" <paulley (AT) ianywhere (DOT) com> wrote

Quote:

Since the two tables are union-compatible, you can do this much more
simply with the EXCEPT operator, which requires a query expression, ie

select * from table1 a
except
select * from transcend.table1_backup b

EXCEPT treats NULL values as special values in each domain (like DISTINCT
and GROUP BY do).

Glenn

P Merritt wrote:
I've been asked to create a backup of a record (which has 75 fields)
which can be restored if necessary. I'd hoped to be
able to tell if the current had been changed by comparing it to the
backup. I tried

with the backup by

CREATE PROCEDURE "transcend"."sp_compare_records"( IN @key1 INT )

BEGIN
IF EXISTS(
SELECT *
FROM table1 a
WHERE exists(SELECT *
FROM transcend.table1_backup b
WHERE a.key2 = b.key2
AND a.key1 = b.key1
AND a.field3 = b.field3
AND a.field4 = b.field4
AND a.field5 = b.field5
AND a.field6 = b.field6

etc,etc

AND b.key1 = @key1)
then return 1;
else return 0;
end if;
END;

I tested it using
BEGIN
DECLARE @return_code INTEGER;
@return_code = CALL sp_compare_records(55);
SELECT @return_code
END;

It sees the records as matching as long as none of the fields has a null
value. I cannot simply leave those fields out of the internal select
because different fields will be left with a null values depending upon
the circumstances (many of the fields are keys to other tables).

Does anyone have any suggestions? (I've essentially no experience with
any scripting within SQL.. will be forced to code a field by field
comparison on the client if I cannot handle in SQL)

Thanks in advance for any suggestions..

--
Glenn Paulley
Director, Engineering (Query Processing)
Sybase iAnywhere

Blog: http://iablog.sybase.com/paulley

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://case-express.sybase.com

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the
Sybase iAnywhere pages at
http://www.sybase.com/products/datab...chnicalsupport

#5

While Volker's approach is semantically correct, the ISNULL construction
is *very* problematic because it is more computationally expensive.
Moreover, none of the ISNULL predicates will be sargable, and hence
indexes cannot be used in the access plan.

The Version 12 beta contains a new predicate, the "distinct predicate"
with syntax "IS [NOT] DISTINCT FROM", which is feature T151 of the
SQL:2008 standard. These predicates are sargable and provides precisely
the functionality desired.

Glenn

Volker Barth wrote:

Quote:

Besides Glenn's excellent proposal:

When comparing values and wanting null = null to be true (which it isn't
of course in three-valued logic), I sometimes use the isnull() function
with a replacement value that isn't usually a valid value for the
column, i.e. something like

WHERE ...
AND isnull(a.date1, '1000-01-01') = isnull(b.date1, 1000-01-01')
AND isnull(a.int1, -1) = isnull(b.int1, -1)

If both a.date1 and b.date1 are null, the same date value will be
compared and will return true obviously.
The problem is to find replacement values that won't be used as valid
contents.

HTH
Volker

P Merritt wrote:
I've been asked to create a backup of a record (which has 75 fields)
which
can be restored if necessary. I'd hoped to be
able to tell if the current had been changed by comparing it to the
backup.
I tried

with the backup by

CREATE PROCEDURE "transcend"."sp_compare_records"( IN @key1 INT )

BEGIN
IF EXISTS(
SELECT *
FROM table1 a
WHERE exists(SELECT *
FROM transcend.table1_backup b
WHERE a.key2 = b.key2
AND a.key1 = b.key1
AND a.field3 = b.field3
AND a.field4 = b.field4
AND a.field5 = b.field5
AND a.field6 = b.field6

etc,etc

AND b.key1 = @key1)
then return 1;
else return 0;
end if;
END;

I tested it using
BEGIN
DECLARE @return_code INTEGER;
@return_code = CALL sp_compare_records(55);
SELECT @return_code
END;

It sees the records as matching as long as none of the fields has a null
value. I cannot simply leave those fields out of the internal select
because different fields will be left with a null values depending
upon the
circumstances (many of the fields are keys to other tables).

Does anyone have any suggestions? (I've essentially no experience with
any
scripting within SQL.. will be forced to code a field by field
comparison on
the client if I cannot handle in SQL)

Thanks in advance for any suggestions..

--
Glenn Paulley
Director, Engineering (Query Processing)
Sybase iAnywhere

Blog: http://iablog.sybase.com/paulley

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://case-express.sybase.com

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the
Sybase iAnywhere pages at
http://www.sybase.com/products/datab...chnicalsupport

#6

For (2), I think the MERGE statement with the WITH AUTO NAME clause will
do what you want.

For (1) - is the "new key value" a specific value, or is this an
AUTOINCREMENT column?

Glenn

P Merritt wrote:

Quote:

Glen - Thanks for the most Excellent suggestion!

is there any chance you've an equally elegant solution to 2 other related
problems.

1) I need to reproduce a specific set of rows (all share key1) giving them a
new key1 value.. right now I'm using a brute force INSERT listing all the
fields, a method which has the obvious disadvantage of becoming inadequate
when new fields are added to the record.

2) I need to restore the content of a specific set of rows (all share key1)
in table1 from table1_backup.. is there any chance I can do that without
listing all the fields ? (I guess that if push comes to shove, I could do a
delete and insert in place of an update)

Thanks again !

-----------------------
"Glenn Paulley [Sybase iAnywhere]" <paulley (AT) ianywhere (DOT) com> wrote in message
news:4b5781eb$1 (AT) forums-1-dub (DOT) ..
Since the two tables are union-compatible, you can do this much more
simply with the EXCEPT operator, which requires a query expression, ie

select * from table1 a
except
select * from transcend.table1_backup b

EXCEPT treats NULL values as special values in each domain (like DISTINCT
and GROUP BY do).

Glenn

P Merritt wrote:
I've been asked to create a backup of a record (which has 75 fields)
which can be restored if necessary. I'd hoped to be
able to tell if the current had been changed by comparing it to the
backup. I tried

with the backup by

CREATE PROCEDURE "transcend"."sp_compare_records"( IN @key1 INT )

BEGIN
IF EXISTS(
SELECT *
FROM table1 a
WHERE exists(SELECT *
FROM transcend.table1_backup b
WHERE a.key2 = b.key2
AND a.key1 = b.key1
AND a.field3 = b.field3
AND a.field4 = b.field4
AND a.field5 = b.field5
AND a.field6 = b.field6

etc,etc

AND b.key1 = @key1)
then return 1;
else return 0;
end if;
END;

I tested it using
BEGIN
DECLARE @return_code INTEGER;
@return_code = CALL sp_compare_records(55);
SELECT @return_code
END;

It sees the records as matching as long as none of the fields has a null
value. I cannot simply leave those fields out of the internal select
because different fields will be left with a null values depending upon
the circumstances (many of the fields are keys to other tables).

Does anyone have any suggestions? (I've essentially no experience with
any scripting within SQL.. will be forced to code a field by field
comparison on the client if I cannot handle in SQL)

Thanks in advance for any suggestions..
--
Glenn Paulley
Director, Engineering (Query Processing)
Sybase iAnywhere

Blog: http://iablog.sybase.com/paulley

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://case-express.sybase.com

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the
Sybase iAnywhere pages at
http://www.sybase.com/products/datab...chnicalsupport

--
Glenn Paulley
Director, Engineering (Query Processing)
Sybase iAnywhere

Blog: http://iablog.sybase.com/paulley

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://case-express.sybase.com

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the
Sybase iAnywhere pages at
http://www.sybase.com/products/datab...chnicalsupport

#7

Glenn, thanks for the pointer to "IS DISTINCT FROM" - it's something
I've not yet checked in the beta.

W.r.t. ISNULL(), I agree that it's bad for performance reasons.

I have used that technique mainly in after update row-level triggers in
older versions (starting with V5.5) to check if the contents has
"really" changed. That way, the question of "sargability" or not was no
problem at all, as only two rows were compared at a time.
I could get rid of that use when the "after update of" trigger facility
started to work correctly

Regards
Volker

Glenn Paulley [Sybase iAnywhere] wrote:

Quote:

While Volker's approach is semantically correct, the ISNULL construction
is *very* problematic because it is more computationally expensive.
Moreover, none of the ISNULL predicates will be sargable, and hence
indexes cannot be used in the access plan.

The Version 12 beta contains a new predicate, the "distinct predicate"
with syntax "IS [NOT] DISTINCT FROM", which is feature T151 of the
SQL:2008 standard. These predicates are sargable and provides precisely
the functionality desired.

Glenn

Volker Barth wrote:
Besides Glenn's excellent proposal:

When comparing values and wanting null = null to be true (which it
isn't of course in three-valued logic), I sometimes use the isnull()
function with a replacement value that isn't usually a valid value for
the column, i.e. something like

WHERE ...
AND isnull(a.date1, '1000-01-01') = isnull(b.date1, 1000-01-01')
AND isnull(a.int1, -1) = isnull(b.int1, -1)

If both a.date1 and b.date1 are null, the same date value will be
compared and will return true obviously.
The problem is to find replacement values that won't be used as valid
contents.

HTH
Volker

P Merritt wrote:
I've been asked to create a backup of a record (which has 75 fields)
which
can be restored if necessary. I'd hoped to be
able to tell if the current had been changed by comparing it to the
backup.
I tried

with the backup by

CREATE PROCEDURE "transcend"."sp_compare_records"( IN @key1 INT )

BEGIN
IF EXISTS(
SELECT *
FROM table1 a
WHERE exists(SELECT *
FROM transcend.table1_backup b
WHERE a.key2 = b.key2
AND a.key1 = b.key1
AND a.field3 = b.field3
AND a.field4 = b.field4
AND a.field5 = b.field5
AND a.field6 = b.field6

etc,etc

AND b.key1 = @key1)
then return 1;
else return 0;
end if;
END;

I tested it using
BEGIN
DECLARE @return_code INTEGER;
@return_code = CALL sp_compare_records(55);
SELECT @return_code
END;

It sees the records as matching as long as none of the fields has a null
value. I cannot simply leave those fields out of the internal select
because different fields will be left with a null values depending
upon the
circumstances (many of the fields are keys to other tables).

Does anyone have any suggestions? (I've essentially no experience
with any
scripting within SQL.. will be forced to code a field by field
comparison on
the client if I cannot handle in SQL)

Thanks in advance for any suggestions..

#8

(2).. I'll give it a try.. THANKs
(1) The new key value is not autoincrement..

"Glenn Paulley [Sybase iAnywhere]" <paulley (AT) ianywhere (DOT) com> wrote

Quote:

For (2), I think the MERGE statement with the WITH AUTO NAME clause will
do what you want.

For (1) - is the "new key value" a specific value, or is this an
AUTOINCREMENT column?

Glenn

P Merritt wrote:
Glen - Thanks for the most Excellent suggestion!

is there any chance you've an equally elegant solution to 2 other related
problems.

1) I need to reproduce a specific set of rows (all share key1) giving
them a new key1 value.. right now I'm using a brute force INSERT listing
all the fields, a method which has the obvious disadvantage of becoming
inadequate when new fields are added to the record.

2) I need to restore the content of a specific set of rows (all share
key1) in table1 from table1_backup.. is there any chance I can do that
without listing all the fields ? (I guess that if push comes to shove, I
could do a delete and insert in place of an update)

Thanks again !

-----------------------
"Glenn Paulley [Sybase iAnywhere]" <paulley (AT) ianywhere (DOT) com> wrote in
message news:4b5781eb$1 (AT) forums-1-dub (DOT) ..
Since the two tables are union-compatible, you can do this much more
simply with the EXCEPT operator, which requires a query expression, ie

select * from table1 a
except
select * from transcend.table1_backup b

EXCEPT treats NULL values as special values in each domain (like
DISTINCT and GROUP BY do).

Glenn

P Merritt wrote:
I've been asked to create a backup of a record (which has 75 fields)
which can be restored if necessary. I'd hoped to be
able to tell if the current had been changed by comparing it to the
backup. I tried

with the backup by

CREATE PROCEDURE "transcend"."sp_compare_records"( IN @key1 INT )

BEGIN
IF EXISTS(
SELECT *
FROM table1 a
WHERE exists(SELECT *
FROM transcend.table1_backup b
WHERE a.key2 = b.key2
AND a.key1 = b.key1
AND a.field3 = b.field3
AND a.field4 = b.field4
AND a.field5 = b.field5
AND a.field6 = b.field6

etc,etc

AND b.key1 = @key1)
then return 1;
else return 0;
end if;
END;

I tested it using
BEGIN
DECLARE @return_code INTEGER;
@return_code = CALL sp_compare_records(55);
SELECT @return_code
END;

It sees the records as matching as long as none of the fields has a
null value. I cannot simply leave those fields out of the internal
select because different fields will be left with a null values
depending upon the circumstances (many of the fields are keys to other
tables).

Does anyone have any suggestions? (I've essentially no experience with
any scripting within SQL.. will be forced to code a field by field
comparison on the client if I cannot handle in SQL)

Thanks in advance for any suggestions..
--
Glenn Paulley
Director, Engineering (Query Processing)
Sybase iAnywhere

Blog: http://iablog.sybase.com/paulley

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://case-express.sybase.com

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the
Sybase iAnywhere pages at
http://www.sybase.com/products/datab...chnicalsupport

--
Glenn Paulley
Director, Engineering (Query Processing)
Sybase iAnywhere

Blog: http://iablog.sybase.com/paulley

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://case-express.sybase.com

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the
Sybase iAnywhere pages at
http://www.sybase.com/products/datab...chnicalsupport

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

comparing the contents of 2 records

sybase.public.sqlanywhere.general sybase.public.sqlanywhere.general

comparing the contents of 2 records - 01-20-2010 , 04:12 PM

Re: comparing the contents of 2 records - 01-20-2010 , 04:21 PM

Re: comparing the contents of 2 records - 01-21-2010 , 02:41 AM

Re: reproducing a set of records - 01-21-2010 , 09:36 AM

Re: comparing the contents of 2 records - 01-21-2010 , 09:38 AM

Re: reproducing a set of records - 01-21-2010 , 09:50 AM

Re: comparing the contents of 2 records - 01-21-2010 , 10:21 AM

Re: reproducing a set of records - 01-21-2010 , 11:20 AM