Skip to main content
Question

Report Designer - SQL query statement - NVL and subselect possible?

  • September 27, 2024
  • 1 reply
  • 74 views

Forum|alt.badge.img+2
  • Do Gooder (Customer)
  • 1 reply

Hello,

I would like to output a logo that is retrieved from the database via a select query and 2 parameters (company and language).
Since it can happen that PNGs are not available for all combinations, I would like to output a standard logo for this case using the NVL function. The select statement works in PL/SQL-Developer.
Unfortunately, no PDF is generated in the Report Designer Preview. Only the rendering cursor is displayed briefly, but nothing happens, no PDF, no error message.

Is a nested select or the use of the NVL function not possible?
Is there anything else to consider that I have overlooked?
Or is there perhaps another way?

 

Here is my select statement:

SELECT NVL((SELECT image
            FROM   Report_Logos_Tab
            WHERE  SUBSTR(image_name,1,4)=:1
                   AND SUBSTR(image_name,-6,2)=:2),
           (SELECT image FROM Report_Logos_Tab WHERE image_name = '0000-Logo-en.png')) 
       FROM dual;

 

These are the parameters:

tns:PURCHASE_ORDER_PRINT_REP/tns:PO_HEADERS/tns:PO_HEADER/tns:C_SITE;tns:PROCESSING_INFO/tns:FORMATTING_OPTIONS/tns:LANG_CODE

1 reply

Forum|alt.badge.img+2
  • Author
  • Do Gooder (Customer)
  • 1 reply
  • October 15, 2024

I think I found the answer for myself:

The problem is that no parameter is used in the select statement:

   (SELECT image FROM Report_Logos_Tab WHERE image_name = '0000-Logo-en.png')

 

Solution:

use a third parameter:

select nvl((select image from report_logos_tab where substr(image_name,1,4)=:1 and substr(image_name,-6,2) =:2),(select image from report_logos_tab where image_name =:3)) image from dual

 

 

 

 


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