BIP parameters problems.

Misstram94

Summary

BIP parameters problems.

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

Thomas Dodds

'NULL' is not NULL ... and you really have a department where it's buyer is 'NULL'? 

Also ... your drop list is going to render as follows:

SELECT PARAM_DISPLAY, PARAM_VALUE

so this doesn't work:

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

try:

Dept

select 'NULL' dept_name, -999999 dept

from dual

UNION

select dept_name, dept dept

from deps

where

(LEAST(:PM_BUYER) IS NULL OR buyer IN (:PM_BUYER)) 

order by dept

^ - if a true null comes in (all choices) then you get all departments; else column buy is filtered on the list of selected PM_BUYER (even if it is just one)

OR ... OR ... OR doesn't work in your case

Misstram94

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

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

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