Solved

Quick Report not getting result with empty parameter value

3 years ago
4 September 2020
7 replies
518 views

Userlevel 2

Narsi
Sidekick
27 replies

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.

icon

Best answer by paul harland 4 September 2020, 14:45

View original

7 replies

Userlevel 6

+18

NickPorter
Superhero (Customer)
317 replies
3 years ago
4 September 2020

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

Userlevel 2

Narsi
Author
Sidekick
27 replies
3 years ago
4 September 2020

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.

Userlevel 7

+24

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

Userlevel 7

+21

william.klotz
Superhero (Customer)
476 replies
3 years ago
4 September 2020

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