Use Imp interface to create a SQL union?

[jriz01]

Background:
The final report contains a report picklist prompt. Items that the user selects will be excluded from the final report. The goal here is to allow the user to select 'None' and thereby exlude nothing.

Failed Attempt #1: Using If Statement
My first thought was to find some dummy piece of data and use an if statement as follows:

if (record='Dummy') then ('None') else (record)

But unfortunately there is no piece of data that will serve this purpose.

Failed Attempt #2: Edit SQL
My second tactic was to write the following manually:

select *
 from Table1
union
select 'None'
 from Table1
order by 1 asc

That SQL produced the desired results: the word 'None' was added to the list of items returned by the query. Unfortunately, when I pointed the promt to the new .imr, Impromptu did not "see" any columns in the query. Apparently after you Edit SQL, not only do you lose the ability to edit a query using the interface, but you also lose the ability to use it as a picklist.

Plea for Help:
So... How do I achieve the same result in the picklist query that I achieved using Edit SQL, but staying within the Impromptu interface?

Thanks in advance for your help,
Josh

 

[griffindm]

Josh,

See faq401-2137 and look at the "Getting an ALL option" section. Use the same method to insert a "None" option.

Hope this helps,

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com