Toad World® Forums

Testing insert: Compare 2 queries where some columns may be null


#1

Hi,

I am trying to test that a row is insert into a table with the correct contents.
So I compare an expected row using a query returning the desired constants from dual to the content of the row after the run of the program.

Data changed by program
SELECT SEQUENCE, errortypecode, oracleerrormsg,
TRUNC (errordate)
FROM rresv_reject_error
WHERE rresvrejectid =
(SELECT ID
FROM rresv_reject
WHERE resvcode = ‘A000081005’ AND lasthistorysequence = 2)

SELECT 1, ‘RGUEST500’, NULL , TRUNC (SYSDATE)
FROM DUAL

The queries are valid. But I get the following error:

Type Description
ERROR The column list specified by the Changed by Program data structure is incompatible with the column list specified by the Expected Results data structure. Make sure that the table and/or query on both “sides” of your outcome return a compatible set of column values.

It seems that when comparing the columns of the expected and actual row, content of an actual column cannot be compared to NULL so I have to do the following with NVL and it works.

SELECT SEQUENCE, errortypecode, NVL (oracleerrormsg, ‘null’),
TRUNC (errordate)
FROM rresv_reject_error
WHERE rresvrejectid =
(SELECT ID
FROM rresv_reject
WHERE resvcode = ‘A000081005’ AND lasthistorysequence = 2)

SELECT 1, ‘RGUEST500’, ‘null’ , TRUNC (SYSDATE)
FROM DUAL

This means that in each query comparison, we need to take care of null values ‘separately’? Is there an easier workaround to this? Or am I doing something wrong?

Rgds

Philippe


#2

You should DEFINITELY not have to jump thru the hoops you are jumping thru.

You should be able to compare:

SELECT SEQUENCE, errortypecode, oracleerrormsg,
TRUNC (errordate)
FROM rresv_reject_error
WHERE rresvrejectid =
(SELECT ID
FROM rresv_reject
WHERE resvcode = ‘A000081005’ AND lasthistorysequence = 2)

with

SELECT 1, ‘RGUEST500’, TO_CHAR(NULL), TRUNC (SYSDATE)
FROM DUAL

I will see if I can figure out why that is not working but that approach (that is, do not pass the literal NULL) works with NUMBER and other DTs. Hope to have this fixed in 1.6.1.


#3

Thanks Steven,

this change to the software will be very useful.

The easy solution until this is in place could be excluding these columns from the 2 queries but this undermines the value of the test.

There are many cases where we want to check that a column in the inserted row is null instead of an incorrect value

But may I say: well done on bringing the software so far, I remember using Qute in the early days, huge progress!

Thanks again.

Philippe


#4

I can confirm now that this will be fixed in 1.6.1. You will be able to specify TO_CHAR(NULL).

Thanks for your kind comments, Philippe. I agree that it has come a long way, and I think you will also be impressed with the changes in 1.6.1.

Regard, SF