Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to use NVL in where condition, manual input condition

YoungTLOct 13 2021

I want to make a jasper report used oracle db, and the condition is manual input,
if not input condition , display all the data,
if input condition ,and the condition maybe multi,
like '33,998',not include the ',
the problem is the col 'abab8' is number type, maybe the NVL return '33,998',include the ',
so i can't get the correct result,
if i use to_char(aban8), then i have no result..
any one can help me ,PLS.
select aban8,abalph from proddta.f0101
where ABAT1='V'
and NVL2(:P_aban,(aban8),1) in NVL(:P_aban,1)
o1.jpgo2.jpgo3.jpg

Comments

Gaz in Oz

One convoluted way would be to use REGEXP_LIKE()
For example, using WITH() to mimic dummy table and dummy data:

SQL> ed
Wrote file afiedt.buf

 1 with f0101 (aban8, abalph, abat1) as (
 2    select   33, 'Text', 'V' from dual union all
 3    select  998, 'Some', 'V' from dual union all
 4    select 1023, 'More', 'V' from dual union all
 5    select 1024, 'text', 'V' from dual
 6 )
 7 select aban8, abalph
 8 from  f0101
 9 where abat1 = 'V'
 10 and   regexp_like(aban8, '^(' || replace(replace(:p_aban, ',', '|'), ' ') || ')$')
 11* or    :p_aban IS NULL
SQL> exec :p_aban := '33,998';

PL/SQL procedure successfully completed.

SQL> /

    ABAN8 ABAL
---------- ----
       33 Text
      998 Some

2 rows selected.

SQL> exec :p_aban := NULL;

PL/SQL procedure successfully completed.

SQL> /

    ABAN8 ABAL
---------- ----
       33 Text
      998 Some
     1023 More
     1024 text

4 rows selected.

SQL> exec :p_aban := '1023, 1024';

PL/SQL procedure successfully completed.

SQL> /

    ABAN8 ABAL
---------- ----
     1023 More
     1024 text

2 rows selected.

SQL>
1 person found this helpful
YoungTL

Thanks a lot.
If nothing else, I'll try use the procedure.
You know in the Jaspersoft Studio I can use java.util.Collection ,it work really good,
but i can't find this type in JasperReport Server's input control.
I think maybe can add this type ,but i don't know how to add it.

User_H3J7U

Jaspersoft Studio User Guide/Parameters/IN & NOTIN

1 person found this helpful
YoungTL

As you see I had try use $X {in},but what input control type i should use,
there not any Collection  item to select.
the condition should be manual input,there more then thousands result,
I can't use multi_select Query ,let user to select ,maybe they only want to see 3 or 5 result, from thousands result to select 3 or 5 result isn't good.

o4.jpgo5.jpg

1 - 4

Post Details

Added on Oct 13 2021
#pl-sql
4 comments
1,019 views