CODEWITHSUNDEEP
javasqldb2hsqldb
Vicky
Vicky

Reputation: 17375

Preparing a compare query between two database tables with similar columns

Given two tables each having n columns where 1 to n-1 columns are the columns which together form a unique key and the nth column is the value column, how can I create a generalized query in my java program which outputs 1 to n-1 columns, nth column of first table, nth column of second table and the difference of the nth column of the two tables.

For e.g. when n = 2, that is when given tables have two columns where first column is the key and the second column is the value, my query would be like:

SELECT 
  COALESCE(A.COL1, B.COL1) AS COL1, 
  CAST(A.VALCOL AS DECIMAL(18,2)) AS SRC_TAB_VALUE, 
  CAST(B.VALCOL AS DECIMAL(18,2)) AS TRGT_TAB_VALUE, 
  CAST(CAST(A.VALCOL AS DECIMAL(18,2)) - cast(B.VALCOL AS DECIMAL(18,2)) AS DECIMAL(18,2)) AS DIFF_OF_VAL 
FROM 
  SCHEMA1.TAB1 A 
FULL JOIN 
  SCHEMA2.TAB2 B 
ON A.COL1 = B.COL1 
  ORDER BY COL1

I am preparing above query in my Java program. The diff query above runs against two in memory HSQL tables.

But how can I generalize this query when the number of key columns are greater then 1.

e.g. given two tables with 5 columns each, the combination of first 4 columns will create a unique key while the fifth column would be the value of that key which is to be compared.

Thanks for reading!

Upvotes: 0

Views: 1495

Answers (2)

Turophile
Turophile

Reputation: 3405

Approaching your question differently, here is another answer.

If you want the SQL to be as generic as possible so that it is easy to generate in your Java, you could write it like this:

WITH 
  TABLEA (COL1,COL2,COLN) AS (SELECT * FROM SCHEMA1.TAB1),
  TABLEB (COL1,COL2,COLN) AS (SELECT * FROM SCHEMA2.TAB2)        
SELECT 
  COALESCE(TABLEA.COL1,TABLEB.COL1) AS COL1, 
  COALESCE(TABLEA.COL2,TABLEB.COL2) AS COL2, 
  TABLEA.COLN AS COLN_A, 
  TABLEB.COLN AS COLN_B, 
  (TABLEA.COLN-TABLEB.COLN) AS DIFF
FROM TABLEA
FULL OUTER JOIN TABLEB
  ON  TABLEA.COL1=TABLEB.COL1 
  AND TABLEA.COL2=TABLEB.COL2
;

The example is for 3 column tables. Anywhere where it has COL2 should be replaced with COL2, COL3, ... COLn-1 as suits the size of the tables. I see four places where this is necessary: the creation of TABLEA, the creation of TABLEB [note that these are identical, so the code only needs to generate the clause once and use it twice], the SELECT COALESCE(TABLEA.COLn,TABLEB.COLn) AS COLn and the ON clause AND TABLEA.COLn=TABLEB.COLn.

Obviously, you will also have to replace SCHEMA1.TAB1 and SCHEMA2.TAB2 as appropriate. The rest of the code should be generic and you don't need to use the actual column names, which should make the Java code much simpler.

Upvotes: 0

Turophile
Turophile

Reputation: 3405

While your question is clearly written, it doesn't give enough details, such as why would you do such a thing?

Seriously, SQL isn't suited to "generalised queries". So you need to have a good reason for doing this in such a difficult way.

Also, your query has SRC_TAB_VALUE and TRGT_TAB_VALUE being cast as DECIMAL(18,2) when selected and when subtracted - why is this necessary, aren't they decimal to begin with? Are the VALUE columns always numeric? What about the key columns? They are being cast also, so are presumably numeric? If they contain non-numeric characters, the CAST will fail. Why is there no WHERE clause to limit your results?

And before coming up with some bizarre attempt at a SQL solution, I have to ask: Why can't the java code just generate the appropriate SQL itself?

It seems like this is a purposefully obtuse problem, one which shouldn't come up unless you are approaching the problem in the wrong way.

Upvotes: 1

Related Questions