Syntax for MINUS

[ria_arora]

Dear All,

I have two tables with 200 columns without any primary key. I want to findout the numbers does not match in both.
SELECT C1, C2, C3, C4.... C200 from T1
MINUS
SELECT C1, C2, C3, C4.... C200 from T2

Please let me know what is the workaround for this.

Thanks
Ria

 

[John Big Booty]

You could use an Unmatched Query to do what you want.

However a table with 200 fields sounds horribly de-normalised, this tutorial should help you get your head around the concept.

 

[gemma-the-husky]

if you do not have a key - then how do you compare rows, in order to find the differences? is there just one row per table?

note that also that the concept of MINUS is the wrong operation for this. (there is no minus operator, but it's hard to see what you are doing with the concept)

 

[ria_arora]

Thanks for the reply. Pls let me explain the issue again:

Table 1 Name: T1 (Revenue File)
Columns: C1, C2, C3, C4...C200

Table 1 Name: T2 (Revenue File)
Columns: C1, C2, C3, C4...C200

These C1 to C2 contains revenue by each product. At this stage it's one table cause this need to be loaded in one of the system and can't be splitted (used for analysis purpose). Once it is moved to DWH then it's normaise.

Now issue is I want to find if there is any mismatched in any of the columns that needs to be reported e.g.
T1
C1 C2 C3 C4 C5 C6
01 02 04 02 03 05
11 13 05 03 04 06
21 14 06 04 05 07
31 15 07 05 06 08
41 16 08 06 07 09
51 17 09 07 08 10

T2
C1 C2 C3 C4 C5 C6
01 02 04 02 03 06
11 13 05 03 04 06
21 14 06 04 05 07
31 15 07 05 06 08
41 16 08 06 07 09
51 17 09 07 08 10

T1 is
01 02 04 02 03 05
and T2 value for C6 is changed so need to be reported
01 02 04 02 03 06

T1 is
21 14 06 04 05 07
and T2 value for C1 is changed so need to be reported
22 14 06 04 05 07

T1 is
41 16 08 06 07 09
and T2 value for C3 is changed so need to be reported
41 16 09 06 07 09

Unique is C2 but if C2 is mismatched then that also need to be reported.

Please let me know how to achieve this.

Regards
Ria

 

[ria_arora]

Basically I want to compare each cell of the record.

Any idea how to do that?

Ria