Forum Stats

  • 3,768,297 Users
  • 2,252,772 Discussions
  • 7,874,520 Comments

Discussions

How to use NVL in where condition, manual input condition

YoungTL
YoungTL Member Posts: 3 Red Ribbon

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)


Tagged:

Answers

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,784 Bronze Crown

    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>
    
    YoungTL
  • YoungTL
    YoungTL Member Posts: 3 Red Ribbon


    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
    User_H3J7U Member Posts: 640 Silver Trophy

    Jaspersoft Studio User Guide/Parameters/IN & NOTIN

    YoungTL
  • YoungTL
    YoungTL Member Posts: 3 Red Ribbon

    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.