Discussions
Categories
- 382.6K All Categories
- 2.1K Data
- 213 Big Data Appliance
- 1.9K Data Science
- 448.4K Databases
- 221.1K General Database Discussions
- 3.7K Java and JavaScript in the Database
- 25 Multilingual Engine
- 541 MySQL Community Space
- 469 NoSQL Database
- 7.8K Oracle Database Express Edition (XE)
- 2.9K ORDS, SODA & JSON in the Database
- 499 SQLcl
- 3.9K SQL Developer Data Modeler
- 186.3K SQL & PL/SQL
- 21.1K SQL Developer
- 293.8K Development
- 9 Developer Projects
- 130 Programming Languages
- 290.5K Development Tools
- 96 DevOps
- 3K QA/Testing
- 645.6K Java
- 24 Java Learning Subscription
- 36.9K Database Connectivity
- 151 Java Community Process
- 104 Java 25
- 22.1K Java APIs
- 137.9K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 146 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 200 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 293 LiveLabs
- 36 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.8K Other Languages
- 2.3K Chinese
- 168 Deutsche Oracle Community
- 1.2K Español
- 1.9K Japanese
- 234 Portuguese
How to use NVL in where condition, manual input condition
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)
Answers
-
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> -
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.😂
-
-
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.
