Toad World® Forums

SingleValueQuery returning NULL value


#1

A single value query can return a single NULL value, it’s different from it returning no rows or such. If I select SingleValueQuery as outcome type, in the operator combobox there is no ‘evaluates to NULL’ and ‘does not evaluate to NULL’ entries. It would be nice if we could test for it.

It’d also be nice if it’d be possible to test for the two typical exceptions of a single value query (NO_DATA_FOUND and TOO_MANY_ROWS). This is not as important as the NULL value, as I can just do workaround by testing for SELECT count(*) FROM (subquery), but would be nice to have.

Yep I could do workaround for the NULL thing too like SELECT NVL(, ‘#NULL#’) … and test it against ‘#NULL#’, for example. But I’d like my test cases to be easily readable, and this functionality (test for NULL) allready exists for function return values, so I guess it’d be easy to add.


#2

Thanks, I will add this to our list of enhancement requests:

“Does a SVQ return value evaluate to NULL?”

However, you can right now check to see if a SVQ raised NDF or TOO_MANY_ROWS. Just define an outcome against an exception and specify the appropriate name or code.

SF


#3

I have noticed that I can’t test for a collection type out paramter or function return value being NULL either. I can test if it’s empty, but not if it’s NULL.


#4

Dang! You are right, another thing to add to the ER list!

Thanks, SF