BusinessObjects Board

ALL in LOV when number field

building semantic layer
#1

I have the following LOV that is a number field. I am trying to customize this LOV so when I select ALL in the report It retuns ALL values for this column.

The SQL is a follow.

SELECT DISTINCT
PRODUCT_NUMBER
FROM
PRODUCT

It work when it is a character field customizing with the following syntax.

SELECT DISTINCT
PRODUCT_CODE
FROM
PRODUCT
union
select ‘ALL’ from DUAL

What should be the SQL for my number LOV ?

Cheers

gogo (BOB member since 2005-06-03)

#2

Hi,

Try this

SELECT DISTINCT
PRODUCT_CODE
FROM
PRODUCT
union
select 1 from DUAL

I have just given a number 1 so that if the user selects 1 it returns all product codes

ajitha :india: (BOB member since 2008-07-28)

#3

LOV values have to be a consistent type. If your LOV is numeric, your “all” constant has to be numeric as well. The trick is to pick something easy to remember and also something that does not actually exist in your database.

Dave Rathbun :us: (BOB member since 2002-06-06)

#4

Hi Dave,

Thanks for your reply but I am not sure what you exactly mean by consistent value. I understand that the following LOV will work in our environment using DUAL as the DUAL Dummy column is defined as a Varch2(1) field.

SELECT DISTINCT
PRODUCT_CODE
FROM
PRODUCT
union
select ‘ALL’ from DUAL

Now if I try SELECT DISTINCT
PRODUCT_NUMBER
FROM
PRODUCT
union
select 9999 from PRODUCT (Where 9999 is not an existing Product Number value I then get returned all my Product Number but this also include a column for 999 which I don’t want as I only need return all numeric values but not the dummy one (9999)

Cheers

gogo (BOB member since 2005-06-03)

#5

There are two things going on here. First is do you want the “dummy” value to show up in the list of values (LOV) or not. If not, you don’t need to do the union from dual stuff. Second is you need to handle the dummy value in the condition object. The code to do that is covered quite well in the Designer FAQ here. The FAQ is for a character object and uses the word “All”. In order to make this work with a numeric value, you have to use a numeric substitute for the word “ALL” which you have done with the part number 9999.

Dave Rathbun :us: (BOB member since 2002-06-06)