SQL testing query

Analytics
Teradata Employee

SQL testing query

Suppose i have

input(a,b are column name)

a b
1 N
N 1
1 1

2 3

here N means null

 

output (a, b, c are column name)

a b c=a*b
1 N 0
N 1 0
1 1 1

2 3  6

c is derived column which we need to calculate using column a and b as c=a*b

1)How to check if correct data has been taken from source system?(ie in output column a and b)

2)How to get output in single query? (validating a,b column and calculation column c)

Tags (3)
3 REPLIES
Teradata Employee

Re: SQL testing query

To get the results you show:

Select a, b, coalesce(a,0) * coalesce(b,0) as c

However, I am not sure what you mean by checking if correct data has been taken from source....  Data Quality is a complex problem (which in my opinion has been greatly overlooked in the past and today seems to be completely ignored).

Teradata Employee

Re: SQL testing query

Hi  GJColeman,

 

Thank you for your reply.

what i wanted to say is that i need to do check that data present in source and destination table is exactly similar and there are no differences in it what so ever.

eg. If source having 

1 N

N 1

2 3

then destination should also have same. so that there is no error while calculating derived column.

 

Teradata Employee

Re: SQL testing query

Go for except all :

select a, b from tableSrc
except all
select a, b from tableTgt;

select a, b from tableTgt
except all
select a, b from tableSrc;