help with DATE placeholder for UNION

[rhythmz]

I have 2 queries (INVOICES and CHECKS) in which the CHECK query has a [CHECK_DATE] & [CHECK_CANCEL_DATE] that I created placeholders in the INVOICE query for a UNION between the 2 queries.

The CHECK query is composed of a UNION between 2 queries that gather issued checks and cancelled checks with dates for each of these scenarios. So the displayed results show a NULL values for those rows with a missing date value where applicable.

In the INVOICE query, my data items for these placeholders is to_date('01-JAN-2099').

Now I have gotten to my MAIN report query, the report runs, I have a format mask to display dates as 'MM/DD/YYYY', but I need to hide those dates displaying as '01/01/2099' in the report. I have tried numerous things but when I run tabular data, but the hourglass freezes regardless of my methods.

Should I have used a different placeholder value for these dates? What is the best method for creating date placeholders when building a UNION operation?

Thank You in advance for your help!!!

[Lynn]

A union is always going to insist that the data types for each column are consistent across all the queries in the union. I think your approach for the placeholder is fine, but did you try just using null (the keyword or '')? If that works it would be the easiest solution.

If you can't get a null value in there, you might look at conditional styling as a way to hide those dates.

If you click on the column in your layout and then select the conditional style property you can create an advanced conditional style and make it not visible for that value. Style variables are another way to achieve the same thing but I tend to prefer conditional styles over style variables.

As another option, you could convert the check dates on the CHECK query to properly formatted character strings and then just make a place holder as an empty string on the INVOICE query. You wouldn't need any conditional styling in that case, but you'd be executing a data type conversion on every date in the CHECK query which may not perform as well.

Good luck!

[rhythmz]

It would seem the most optimal solution was to go to the lowest base check queries (cancelled checks & re-issued checks) to introduces these date data items and convert them using to_char (since no date comparisons or date calculations exist in the report) and made the placeholder in the invoice query a ' '. I seemed to accomplished what I needed to do using this approach. Thank you very much for your guidance.