Filter empty or null value in JSP file using SQL

Question

Filter empty or null value in JSP file using SQL

Good afternoon, I have a SQL query as code below where I have 3 filters, these filters use the clause where, and and and but if one of the filters is blank the returned value is also blank.

How can I do this processing, either in the database or directly in the JSP file?

SELECT DISTINCT DECODE (initcap(PP.OVERALL_HEALTH), '', '/itg/images/statusreport/NA.gif', '/itg/images/statusreport/'   || DECODE(UPPER(PP.OVERALL_HEALTH), 'GREEN', 'Verde', initcap(PP.OVERALL_HEALTH))   || '.gif' ) SAUDE_PROGRAMA_ICONE, PP.CONTAINER_NAME NOME_PROGRAMA, HD.VISIBLE_PARAMETER1 TIPO_CLIENTE, HD.VISIBLE_PARAMETER32 DIRETOR, P.NAME
FROM PGM_PROGRAMS PP
INNER JOIN KCRT_REQ_HEADER_DETAILS HD ON HD.REQUEST_ID = PP.PGM_REQUEST_ID
INNER JOIN KCRT_FG_PFM_PROGRAM FP ON FP.REQUEST_ID = PP.PGM_REQUEST_ID
INNER JOIN FM_FINANCIAL_SUMMARY FS ON PP.FINANCIAL_SUMMARY_ID = FS.FINANCIAL_SUMMARY_ID
INNER JOIN PFM_PORTFOLIO_CONTENTS PC ON FS.FINANCIAL_SUMMARY_ID = PC.FINANCIAL_SUMMARY_ID
INNER JOIN PFM_PORTFOLIOS P ON PC.PORTFOLIO_ID = P.PORTFOLIO_ID
WHERE HD.PARAMETER1 = 'CORPORATIVO'
AND HD.PARAMETER32 IN '100427' 
AND P.NAME IN (select p.name as portfolio from pfm_portfolios p
start with p.portfolio_id in '34006' 
connect by prior p.portfolio_id = p.parent_portfolio_id)

0

sql jsp

Author: João Martins, 2019-02-22

Source

1 answers

score 0 · Accepted Answer

You can use a OR in conjunction with the AND:

SELECT *
  FROM tabela t
 WHERE (:condicao1 = '' OR t.condicao1 = :condicao1)
   AND (:condicao2 = '' OR t.condicao2 = :condicao2)

Just don't forget to isolate the conditions with parentheses to have the behavior as explained