Writing Tests : Data Diff and Data Diff count

[Anu Nambiar]

Hi,

I am trying to compare the source and target table but today, it says "Testing was interrupted and results are incomplete. Test Pages: 0 right, 0 wrong, 0 ignored, 0 exceptions     Assertions: 0 right, 0 wrong, 0 ignored, 0 exceptions " though it gave some incorrect list of data yesterday .

Below is my Page data :

------------------------------------
!define q1 { Select   "asset.name",
                 'q1' as source
             from RF_EXT_PARTNER
             }
            
!define q2 {Select  LABID AS "asset.name",
                 'q2' as source
           from SRC_EXTPARTNERS
  }
 
|query|(${q1} MINUS ${q2}) UNION (${q2} MINUS ${q1})|
|"asset.name"                                       |
  source     |

---------------------

But today it gives: Could not complete testing: java.net.SocketException error as below:

Exception in thread "main" java.lang.StackOverflowError
at sun.nio.cs.UTF_8.updatePositions(Unknown Source)
at sun.nio.cs.UTF_8.access$200(Unknown Source)
at sun.nio.cs.UTF_8$Encoder.encodeArrayLoop(Unknown Source)
at sun.nio.cs.UTF_8$Encoder.encodeLoop(Unknown Source)
at java.nio.charset.CharsetEncoder.encode(Unknown Source)
at sun.nio.cs.StreamEncoder.implWrite(Unknown Source)
at sun.nio.cs.StreamEncoder.write(Unknown Source)
at sun.nio.cs.StreamEncoder.write(Unknown Source)
at java.io.OutputStreamWriter.write(Unknown Source)
at java.io.PrintWriter.write(Unknown Source)
at java.io.PrintWriter.write(Unknown Source)
at java.io.PrintWriter.print(Unknown Source)
at fit.Parse.print(Parse.java:185)
at fit.Parse.print(Parse.java:188)
at fit.Parse.print(Parse.java:194)
at fit.Parse.print(Parse.java:194)
at fit.Parse.print(Parse.java:194)
at fit.Parse.print(Parse.java:194)

'

'

'

When I had tried the same yesterday, it listed all the q1's asset.name data which was incorrect as there is no difference in both the tables. Can anyone help me resolve this? And also suggest how can I write the test to just check the count of the difference of these table?

Thanks,

Anu

[Yavor Nikolov]

Hi Anu,

* The formatting below seems suspicious:

|query|(${q1} MINUS ${q2}) UNION (${q2} MINUS ${q1})|
|"asset.name"                                       |
  source     |

the second row should rather be on a single line:

|Query|(${q1} MINUS ${q2}) UNION (${q2} MINUS ${q1})|
|"asset.name"                         |  source     |

* Your current query doesn't compare the two tables: it would return all distinct rows from both. (The source column is always different - 'q1' in 1st query, 'q2' - in 2nd).

* If you want to count the differences, you can e.g. use a query like:

select count(*) as cnt

from (${q1}) q1 full outer join (${q2}) q2 on (q1."asset.name" = q2."asset.name")

where q1.source is null or q2.source is null

Above is assuming asset name is not nullable. If can be null - the join expression needs to be modified accordingly.

Regards,

Yavor

--
You received this message because you are subscribed to the Google Groups "dbfit" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dbfit+un...@googlegroups.com.
To post to this group, send email to db...@googlegroups.com.
Visit this group at https://groups.google.com/group/dbfit.
For more options, visit https://groups.google.com/d/optout.

[Anu Nambiar]

Hi Yavor,

Thanks for your suggestion.

I used the below to see the count of the difference in data :

!|Store Query|Select count(*) AS my_count from ((${q1} MINUS ${q2}) UNION (${q2} MINUS ${q1}))|diff_res|

!|Query|<<diff_res|
|my_count         |

And it gave me the below output :

Since I did not have any data difference, I was expecting '0' as count...So, let me know if this is correct and also, is there a way where I can match the expected result and the query output result. Secondly, though there were no distinct records, I was getting all the "asset.name" entries previously.

Thanks,

Anu

[Yavor Nikolov]

Hi Anu,

So, let me know if this is correct.

As I mentioned - this doesn't seem correct to me (if you're using the same queries for q1, q2 as before).

Let's say query q1 returns (7 ,'q1') and q2 returns (7, 'q2'). This means:

(q1 minus q2) would be (7, 'q1')

(q2 minus q1) would be (7, 'q2')

the union is 2 rows:

(7, 'q1')

(7, 'q2')

The count on top of that union should be 2. (Nothing DbFit specific here).

If you're getting result 0 - this perhaps means that both q1 and q2 are returning empty sets; or q1 and q2 are maybe defined differently than what you posted here previously.

 

is there a way where I can match the expected result and the query output result

Well, this is what dbfit Query and CompareStoredQueries do. If you're expecting 0 as a result - you need to specify that:

!|Query|<<diff_res|
|my_count         |

|0                |

Best Regards,

Yavor

[Anu N]

Correct Yavor,

I had removed      'q1' as source and q2 as source from q1 nd q2 definition ,as even i could see that as mentioned by you below. Doing so had given me the output as '0 ' surplus as shown in my previous post. 

But when i was trying to find the different data amongst the two source and target table , it gave me Stackoverflow error as shown in my 1st post.

Sent from my iPhone

Hi Anu,

<mime-attachment.png>

--
You received this message because you are subscribed to a topic in the Google Groups "dbfit" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/dbfit/lOlBQPv3NXY/unsubscribe.
To unsubscribe from this group and all its topics, send an email to dbfit+un...@googlegroups.com.