4

So I am validating count between source and target tables, if count is not the same it can go in positive or negative number, however when is negative it would show NULL. I used to know this but seems to me I have short memory. My code is below.

with cte as (
            select 'source' [object],count(contract_id) as total_count
            FROM [account].[dbo].[account] act
            left join [account].[dbo].[contract] cont
            on act.account_id = cont.account_id)
,cte1 as(
          select 'target' [object], count(contract_id) as total_count
          from [account].[dbo].[action]
          union
          select * from cte)
          select * from cte1 
          union
          select 'diff' [object],
                 (select total_count from cte where [object] = 'source')
                -
                 (select total_count from cte where [object] = 'target')  

Results is as follow object total_count target 28402 source 28401 diff NULL -- here it should be negative 1 (-1)

6

The problem is here:

(select total_count from cte where [object] = 'target')

There is not object = 'target' in the "cte" cte.
As the select returned null, the calculation also have to return null.

Try this: (changing cte for cte1)

with cte as (
            select 'source' [object],count(contract_id) as total_count
            FROM [account].[dbo].[account] act
            left join [account].[dbo].[contract] cont
            on act.account_id = cont.account_id)
,cte1 as(
          select 'target' [object], count(contract_id) as total_count
          from [account].[dbo].[action]
          union
          select * from cte)
          select * from cte1 
          union
          select 'diff' [object],
                 (select total_count from cte where [object] = 'source')
                -
                 (select total_count from cte1 where [object] = 'target')  
1
  • Thank you, I see where I made mistake, 2nd set of eyes are always great. Thank you so much @Dominique Boucher – DKCroat Jul 29 '20 at 15:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.