Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Adding and Using 'All' into/from an LOV

Status
Not open for further replies.

smooveb

IS-IT--Management
Joined
Jul 20, 2001
Messages
161
Location
US
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
 
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

 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top