Empty values in Oracle result sets not returned as empty in Query templates

[Former Member]

To whoever can help,

I've searched the forum but haven't managed to find a solution to this issue although I would imagine it's fairly common.

I am executing Oracle Select statements using Query templates which I am activating from the Logic Editor. In the result sets returned I am expecting some empy values but they are coming back to me differently; numbers are returning as NA; characters as --- and timestamps as TimeUnavailable. What can I do to ensure these values appear exactly as they are in Oracle?

Thanks in advance,

John

[Former Member · Accepted solution]

John,

This is how the default stylesheet handles null values for Illuminator Columns of various data types.

I think the only recommended/supported way is to modify the results coming back from the query by copying the results to a new IllumDoc. I could be mistaken.

This leads to a little problem where if you are updating DB records with data returned from the DB, you could inadvertently insert "---" or "NA" into the DB.

[Former Member]

Thanks for the replies.

I am currently using REPLACE in the Oracle statement to get around this problem. I have tried changing the stylesheet also which works when I run Test in the Query Template but doesn't make any difference to the values returned to the Logic Editor. I was hoping there was some easy way such as modifying a style sheet but if not I'll probably just stick to the way I'm doing it.

John

[jcgood25]

John,

What you have experienced is the standard xMII behavior for dealing with NULL values in a data type associated manner. Handling this within your query is the right way to work with the data.

Regards,

Jeremy Good

[Former Member]

John - We had the exact same problem and posted on it just recently as well. We ended up putting a DECODE in our query and replacing the values with something else.

As well, you could also do some filtering or etc like Ryan suggested