Skip to main content

Hi,

It is my where clause in Quick Report(SQL statement type)

spr.MANAGER = '&MANAGER'

I need result when parameter(&MANAGER) is empty 

getting no data found message

 

When I use where clause like

spr.MANAGER is null

I’m getting result as expected 

 

Any ideas to get result with where parameter is empty?

 

Thanks,

Narsi.

 

Can you change the WHERE clause to be WHERE spr.MANAGER = '&MANAGER' OR spr.MANAGER is NULL?

 

Hi @NickPorter,

It works where '&MANAGER' is empty but doesn’t work where  '&MANAGER' is not empty 

getting result as empty records + MANAGER2 records when '&MANAGER' is MANAGER2

Thanks,

Narsi.

where spr.MANAGER like nvl('&MANAGER','%') 

Hi @Narsi ,

 

You can write a query for a quick report like the one below and you’ll be able to leave fields blank or enter something to filter the data.

 

SELECT part_no, 
       description, 
       part_status,
       part_product_code,
       part_product_family,
       prime_commodity,
       second_commodity
FROM IFSAPP.INVENTORY_PART ip
WHERE UPPER(contract) LIKE NVL(UPPER('&SITE'),'OM1SH')
  AND part_status IN ('A', 'D')
  AND type_code_db IN ('1','2')
  AND part_product_code LIKE NVL(UPPER('&PRODUCT_CODE'),'%')
  AND part_product_family LIKE NVL(UPPER('&PRODUCT_FAMILY'),'%')
  AND prime_commodity LIKE NVL(UPPER('&PRIME_COMMODITY'),'%')
  AND second_commodity LIKE NVL(UPPER('&SECOND_COMMODITY'),'%')
ORDER BY part_no

 

William

where spr.MANAGER like nvl('&MANAGER','%') 

This will definitely work…!!! 

Hi @Narsi 

where spr.MANAGER like nvl('&MANAGER','%') 

thank you all

Reply


Terms & ConditionsCookie settings