BusinessObjects Board

Adding ALL to the list of values

building semantic layer
#1

Hello Sandi,

I tried the following SQL to add “ALL” in the LOV for ProjectType object (on the universe side).

Select ‘All’ from Dual
union all
SELECT DISTINCT
VW_RPT_CUS_SEP_RenamedStandardFields.ProjectType
FROM
VW_RPT_CUS_SEP_RenamedStandardFields

I am getting the following error message when I validate the SQL

Exception: DBD, [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name ‘Dual’.State: 42S02

Do I need to do any additional steps to include ALL to the LOVs

Hema

hemaghsr (BOB member since 2008-10-31)

IDT- LOV with ALL in asc order
URGENT HELP NEEDED - PROMPT PASSING VALUES!
#2

The ‘dual’ table concept is available in Oracle and not in SQL server.
For SQL server no need to mention any dual table.

So,

Oracle

select ‘ALL’ from Dual

SQL Server

Select ‘All’

kurt (BOB member since 2006-11-06)

#3

If you want the ‘All’ value to appear first in the list then prefix it with something like an underscore, otherwise it will sort alphabetically with the rest of the values.

jwhite9 :us: (BOB member since 2006-07-28)

#4

A cleaner way of doing it is:


SELECT
  x.LOV
FROM
(SELECT 'ALL' AS LOV
UNION ALL
SELECT DISTINCT
VW_RPT_CUS_SEP_RenamedStandardFields.ProjectType
FROM
VW_RPT_CUS_SEP_RenamedStandardFields 
) x
ORDER BY
CASE WHEN LOV = 'ALL' THEN '__' ELSE LOV END

The case statement in the ORDER BY section will force ALL to the top (unless you’ve got something alphabetically preceding ‘__’ in which case sort your data out!) and also sort the rest after that.

If you want other well used items to come to the top of the LOV because they are selected most often then adjust your case statement accordingly. For example if you were working predominantly in the UK and US, you could have


CASE 
WHEN LOV = 'ALL' THEN '__1' 
WHEN LOV = 'United States' THEN '__2' 
WHEN LOV = 'United Kingdom' THEN '__3' 
ELSE LOV END

Hope that helps.

Regards,
Mark

#5

thank you mark.

Hema

hemaghsr (BOB member since 2008-10-31)