Skip to main content
Question

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

  • September 27, 2024
  • 1 reply
  • 106 views

Forum|alt.badge.img+2
  • Do Gooder (Customer)
  • 3 replies

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)
  • 3 replies
  • 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