Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2014

    Unanswered: Need help with row count validation query.

    I am trying to write a query that will count and compare the number of rows of two tables on two different databases. If they are equal then a record will be inserted into another table as 'Pass', else it will result in a 'Fail'.
    I can get the portion that enters the pass/fail to work.
    But I would also like to insert data to a column called validation_type. What would I have to add to the following query to insert into another specified column?

      INTO db3.validation 
     (SELECT CASE WHEN (SELECT COUNT(1) FROM db1.transaction)
                   = (SELECT COUNT(1) FROM db2.transaction)
                THEN 'pass'
                ELSE 'fail' END);
    Last edited by lepom; 07-21-14 at 17:23.

  2. #2
    Join Date
    Jun 2014
    I just figured it out.

    This is what I had to do differently

    insert into db3.validation(validation_type, test_result)
    (select 'count_validation', (case when db2.cnt1 = db1.cnt2 then 'pass' else 'fail' end)
        from (select count(1) as cnt1 from lead.transaction) db2 cross join
             (select count(1) as cnt2 from stage.transaction) db1);

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts