BusinessObjects Board

Select 'ALL' in LOV

building semantic layer
#1

Hello All,

I am trying to put ‘ALL’ in a LOV so that it picks up all the values in the list. I looked at other articles and tried to use a union of tables but it doesn’t fetch any data. Following is the sql generated when a user selects ‘ALL’:

SELECT
M_INVOICE_CURR_TEST.ACCT_CODE,
M_INVOICE_CURR_TEST.EVENT,
M_INVOICE_CURR_TEST.CLAIM_NO,

sum(M_INVOICE_CURR_TEST.AMOUNT)
FROM
M_INVOICE_CURR_TEST,
( select t.LESSEE_CODE,t.VEHICLE_NO,yr_mon from m_invoice_curr_test t
union
select t.LESSEE_CODE,t.VEHICLE_NO,yr_mon from b_invoice_curr_test t) Table__4
WHERE
( Table__4.LESSEE_CODE=M_INVOICE_CURR_TEST.LESSEE_CODE AND Table__4.VEHICLE_NO=M_INVOICE_CURR_TEST.VEHICLE_NO
AND M_INVOICE_CURR_TEST.YR_MON=Table__4.YR_MON )
AND (
Table__4.LESSEE_CODE = ‘0G36’
AND M_INVOICE_CURR_TEST.PAGE_NUMBER IN (‘ALL’)
)
GROUP BY
M_INVOICE_CURR_TEST.ACCT_CODE,
M_INVOICE_CURR_TEST.EVENT,
M_INVOICE_CURR_TEST.CLAIM_NO

What am I doing wrong. The select for the LOV object is:

SELECT DISTINCT
M_INVOICE_CURR_TEST.PAGE_NUMBER
FROM
M_INVOICE_CURR_TEST

UNION SELECT ‘ALL’ FROM DUAL

Your help will be greatly appreciated.

Thanks,
Purnima

prnmsharma :us: (BOB member since 2008-03-18)

#2

Try this…

SELECT DISTINCT M_INVOICE_CURR_TEST.PAGE_NUMBER as PAGE_NUMBER
FROM
M_INVOICE_CURR_TEST

UNION
SELECT nvl(null,’ ALL’) as PAGE_NUMBER FROM DUAL

GoodLuck!

TinkerBell (BOB member since 2008-12-18)

#3

It still doesn’t work. I will put it in a prompt

Thanks,
Purnima

prnmsharma :us: (BOB member since 2008-03-18)

#4

It worked for me…

the ides is same you might have to change the syntax based on you DB

TinkerBell (BOB member since 2008-12-18)

#5

We are using Oracle database. Is it the right synatx for Oracle database or not?

Thanks,
Purnima

prnmsharma :us: (BOB member since 2008-03-18)

#6

There are two parts to the issue. First part is getting ALL into the LOV query. Second part is properly handling the event when a user selects ALL in response to the prompt. The code you have does not handle ALL properly in the resulting SQL.

Handling ALL in a Prompt

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