BIP parameters problems.

Question

Summary

Content

Hi all,

I have 4 parameters:

1. Buyer:

select 'NULL' buyer_name, -999999 buyer

from dual

UNION

select buyer_name,buyer buyer

from buyer

order by buyer.

2. Dept

select 'NULL' dept_name, -999999 dept

from dual

UNION

select dept_name, dept dept

from deps

where

:PM_BUYER = 'NULL' OR :PM_BUYER = -999999  OR buyer = :PM_BUYER

order by dept

3. Status

Label                Value

NULL               NULL -- select all values

Worksheet          W

Approved           A

Submitted          S

4. Order no

select order_no order_no

from ordhead

WHERE

(:PM_BUYER = 'NULL' OR :PM_BUYER = -999999 OR ordhead.buyer = :PM_BUYER)

AND

(:PM_dept = 'NULL' OR :PM_dept = -999999 OR ordhead.dept = :PM_dept)

AND

(:PM_status = 'NULL' or ordhead.status = :PM_status)

=>"Order no" parameter is depended on other parameters.

it's working correctly for the first time with specific "Buyer" parameter, but when "Buyer" parameter is  re-selected with 'NULL' value then 'Dept' parameter's value field is disappear.

If anyone have any ideas or solution for this, please help me, I will very appreciate your help.

Thanks,

T

Misstram94 · Answer

I have resolved this issue, this is my code

select order_no order_no

from ordhead

WHERE

(ordhead.buyer =nvl(:PM_BUYER, ordhead.buyer))

AND

(ordhead.dept =nvl(:PM_dept,ordhead.dept))

AND

(ordhead.status in (:PM_status))

And set default value = *  for select all values passed.

https://docs.oracle.com/cd/E10415_01/doc/bi.1013/e12187/T518230T518233.htm#addlov

"Using * passes a null, so you must handle the null in your data source. A method to handle the null would be the standard Oracle NVL command"

Misstram94 · Answer

Optimizing my sql like this, it's still happening errors:

select order_no order_no

from ordhead

WHERE

(:PM_BUYER is null or ordhead.buyer =:PM_BUYER)

AND

(:PM_dept is null or ordhead.dept =:PM_dept)

AND

(ordhead.status in (:PM_status))

I removed OR from the codes, It's fine

select order_no order_no

from ordhead

WHERE

(ordhead.buyer =:PM_BUYER)

AND

(ordhead.dept =:PM_dept)

AND

(ordhead.status in (:PM_status))

But I want to select all order_no values when buyer,dept are nulls value.

Misstram94 · Answer

Hi Thomas,

I think the problems do not at ' Dept' prompt, it's Order_no prompt, since Order_No prompt must be depended on 3 parameters.

I tried like your suggestion, but it's not work.

And I try removing parameters from the codes of Order_no prompt then it's OK

select order_no order_no

from ordhead