Re: Puzzles on SQL/Oracle

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 21 Dec 2006 13:03:23 -0800
Message-ID: <1166735003.299911.70580@f1g2000cwa.googlegroups.com>

DA Morgan wrote:
> Charles Hooper wrote:
> > Extra credit:
> > SELECT DISTINCT
> > NVL(A.COL1,B.COL1) COL1,
> > NVL(A.COL2,B.COL2) COL2,
> > NVL(A.COL3,B.COL3) COL3,
> > NVL2(A.COL1,'TABLE A','TABLE B') FROM_TABLE
> > FROM
> > TABLE_A A
> > FULL OUTER JOIN
> > TABLE_B B ON A.COL1=B.COL1 AND A.COL2=B.COL2 AND A.COL3=B.COL3
> > WHERE
> > UTL_I18N.RAW_TO_CHAR(DBMS_CRYPTO.HASH(UTL_I18N.STRING_TO_RAW
> > (NVL(A.COL1,'1'),
> > 'AL32UTF8'),1)||DBMS_CRYPTO.HASH(UTL_I18N.STRING_TO_RAW
> > (NVL(A.COL2,'1'),
> > 'AL32UTF8'),1)||DBMS_CRYPTO.HASH(UTL_I18N.STRING_TO_RAW
> > (NVL(A.COL3,'1'), 'AL32UTF8'),1),'AL32UTF8')<>
> > UTL_I18N.RAW_TO_CHAR(DBMS_CRYPTO.HASH(UTL_I18N.STRING_TO_RAW
> > (NVL(B.COL1,'1'),
> > 'AL32UTF8'),1)||DBMS_CRYPTO.HASH(UTL_I18N.STRING_TO_RAW
> > (NVL(B.COL2,'1'),
> > 'AL32UTF8'),1)||DBMS_CRYPTO.HASH(UTL_I18N.STRING_TO_RAW
> > (NVL(B.COL3,'1'), 'AL32UTF8'),1),'AL32UTF8');
> >
> > COL1 COL2 COL3 FROM_TABLE
> > TEST2A TEST2B TEST2C TABLE A
> > TEST4A TEST4B TEST4C TABLE A
> > TEST2A TEST1B TEST1C TABLE B
> >
> > Is more than one SELECT acceptable?
> >
> > Charles Hooper
> > PC Support Specialist
> > K&M Machine-Fabricating, Inc.
>
> Different puzzle. <g>
> --
> Daniel A. Morgan
> University of Washington
> damorgan_at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Group
> www.psoug.org

What I was looking for when I started building the second example was a way to retrieve the binary values of all columns in a row (ragardless of the column names), pass the binary values through DBMS_CRYPTO.HASH to generate a hash key, and then use that to see if two rows resolved to exactly the same hash key. I didn't find the function that I was searching for to pull the binary values of all columns. Something like this, but without listing each of the columns: SELECT

  NVL(A.COL1,B.COL1) COL1,
  NVL(A.COL2,B.COL2) COL2,
  NVL(A.COL3,B.COL3) COL3,

  NVL2(A.COL1,'TABLE A','TABLE B') FROM_TABLE FROM
  TABLE_A A
FULL OUTER JOIN
  TABLE_B B ON A.COL1=B.COL1 AND A.COL2=B.COL2 AND A.COL3=B.COL3 WHERE DUMP(A.COL1||'^'||A.COL2||'^'||A.COL3)<>DUMP(B.COL1||'^'||B.COL2||'^'||B.COL3);

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Thu Dec 21 2006 - 15:03:23 CST