Skip to main content
Solved

Quick Report not getting result with empty parameter value

  • September 4, 2020
  • 7 replies
  • 629 views

Forum|alt.badge.img+8

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.

 

Best answer by paul harland

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

View original
Did this topic help you find an answer to your question?

7 replies

NickPorter
Superhero (Customer)
Forum|alt.badge.img+18
  • Superhero (Customer)
  • 354 replies
  • September 4, 2020

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

 


Forum|alt.badge.img+8
  • Author
  • Sidekick
  • 27 replies
  • September 4, 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.


paul harland
Superhero (Employee)
Forum|alt.badge.img+24

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


william.klotz
Superhero (Customer)
Forum|alt.badge.img+21
  • Superhero (Customer)
  • 479 replies
  • September 4, 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

 

William


Deepak
Sidekick
Forum|alt.badge.img+10
  • Sidekick
  • 97 replies
  • September 7, 2020
paul harland wrote:

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

This will definitely work…!!! 


ADNAN
Sidekick
Forum|alt.badge.img+8
  • Sidekick
  • 53 replies
  • September 7, 2020

Hi @Narsi 

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


Forum|alt.badge.img+8
  • Author
  • Sidekick
  • 27 replies
  • September 7, 2020

thank you all


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings