Cannot get a LOV to populate when used within a Derived Table.

[Former Member]

BOE 3.1 (updated to SP 2.1)

Oracle 10g

Universe Designer 12.1.0.882

The answer to this question may be a simple "This is not possible". I am unable to find a definitive answer as of now.

I am trying to create a derived table that prompts the user with a LOV before querying the database. The derived table actually returns a single value: a comma delimited id aggregation that is passed to other stored procedures. The query works excellent if I want to return all the values, but the failure is when I try to limit the values return to only those the user wants.

The derived table query is as follows:

Select substr(replace(replace(xmlagg(xmlelement("x", fld_offc_id) order by fld_offc_id), '</x>, <'x>',','),2) as offices
FROM fld_offc_lkp where fld_offc_name IN @Prompt('Select Offices:','A','FLDOFFC\Fld Offc Name',multi,constrained)

The problem occurs when using that derived table. The LOV is coming up empty and I am unable to select anything.

Any help or insight is appreciated.

Chad

[Former Member · Accepted solution]

Hi,

Have you checked your stored procedure returing all values and specifed values at data base level before appying in a universe. If so then

Try to cross check the prompt name(all capitials and small letters) and class name/object name used in prompt syntax.

Cheers,

Suresh Aluri.