Adding and Using 'All' into/from an LOV

[smooveb]

Hi!

I'm trying to add a value of 'All' to the LOV for a variable called site, and have All act to select all the values.

I've got a Site variable, a Site LOV variable (that successfully add's 'All' to the LOV using a union in the sql with sys.dual), and a Site Test variable I've been trying to work with in order to have it act like Site, but accept All as a valid choice.

right now, I have a select statement for Site Test with the followiing in it:

SITE in @Prompt('enter field','A','Site\Site LOV',MULTI,FREE) or 'ALL' in @Prompt('enter field','A','Site\Site LOV',MULTI,FREE)

i checked the sql for the SiteTest object and it looks like this:

SELECT DISTINCT
SITE.SITE
FROM
SITE


The error I'm getting is this:

ORA-00923: FROM keyword not found where expected :-923


Any ideas out there? Thanks everyone! Barrett

 

[Subhashab]

Hi the first thing u have to do is the make sure the SQL for the LOV is right
it should be
select distinct site from table
union
select 'All from dual

After this make an object in the universe with the prompt definition. eg name PROMPTSITE
@Prompt('select Site or All','A',,,,)

then make a predefined condition which you will use in the report condition

decode(PROMPTSITE,'All','All',site) = PROMPTSITE

 

[smooveb]

argh - you have given me hope, but BO is not playing nice. The error it's currently giving me is

ORA-00904: "PROMPTSITE": invalid identifier :-904

@Prompt('enter Site or All','A','Site\SiteLOV',MULTI,FREE)

is the select statement for promptsite right now - exactly, no equal signs or anything else. I have tested SiteLOV and that works fine. However, I should mention that when parsing the promptsite select statement, i get the following:

Parse Failed: ORA-00907: Missing Right Parenthesis

any ideas out there?
Thanks! Barrett