stephanieaylamay Posted October 2, 2018 Share Posted October 2, 2018 [deleted] Link to comment Share on other sites More sharing options...
Solution reportdev Posted October 2, 2018 Solution Share Posted October 2, 2018 Since Oracle does not allow such kind of queries, you can use below work aroundBased on your AIRLINE parameter, we will write a java expression that will evaluate what is in AIRLINE and then Fill the hidden CONTRACT_CODE parameterThis CONTRACT_CODE parameter will control the SQLI know that this will work in similar way to case statement <parameter name="CONTRACT_CODE" class="java.lang.String" isForPrompting="false"> <parameterDescription><![CDATA[]]></parameterDescription> <defaultValueExpression><![CDATA[$P{AIRLINE}.equals( "A") ? ("'PBCA', 'PBCA1'") : ("'PBCP1', 'PBCP2'" )]]></defaultValueExpression> </parameter>[/code] Full JRXML<?xml version="1.0" encoding="UTF-8"?><!-- Created with Jaspersoft Studio version 6.4.2.final using JasperReports Library version 6.4.1 --><jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="conditionalLogic" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="060e2dee-2273-43e5-bba0-3ce989aa427a"> <property name="com.jaspersoft.studio.data.sql.tables" value=""/> <parameter name="AIRLINE" class="java.lang.String"> <defaultValueExpression><![CDATA["A"]]></defaultValueExpression> </parameter> <parameter name="CONTRACT_CODE" class="java.lang.String" isForPrompting="false"> <parameterDescription><![CDATA[]]></parameterDescription> <defaultValueExpression><![CDATA[$P{AIRLINE}.equals( "A") ? ("'PBCA', 'PBCA1'") : ("'PBCP1', 'PBCP2'" )]]></defaultValueExpression> </parameter> <queryString> <![CDATA[with q as (select 'PBCA' as CONTRACT_CODE, 20 as val from dual union all select 'PBCA' as CONTRACT_CODE, 30 as val from dual union all select 'PBCA' as CONTRACT_CODE, 40 as val from dual union all select 'PBCA1' as CONTRACT_CODE, 40 as val from dual union all select 'PBCA1' as CONTRACT_CODE, 40 as val from dual union all select 'PBCP1' as CONTRACT_CODE, 120 as val from dual union all select 'PBCP2' as CONTRACT_CODE, 150 as val from dual)select * from q where CONTRACT_CODE in ( $P!{CONTRACT_CODE} )]]> </queryString> <field name="CONTRACT_CODE" class="java.lang.String"/> <field name="VAL" class="java.math.BigDecimal"/> <background> <band splitType="Stretch"/> </background> <title> <band height="20"> <textField evaluationTime="Report"> <reportElement x="280" y="0" width="100" height="20" uuid="3bfd0cc8-950f-42c5-b81e-2213990cceae"/> <textFieldExpression><![CDATA[$P{CONTRACT_CODE}]]></textFieldExpression> </textField> <staticText> <reportElement x="180" y="0" width="100" height="20" uuid="9b4179cd-6d5f-401b-9533-731959f0a221"/> <text><![CDATA[selected Parameter Value]]></text> </staticText> </band> </title> <columnHeader> <band height="20" splitType="Stretch"> <staticText> <reportElement x="0" y="0" width="140" height="20" uuid="a487e214-1beb-4e96-8972-e19661a0064b"/> <text><![CDATA[CONTRACT_CODE]]></text> </staticText> <staticText> <reportElement x="140" y="0" width="100" height="20" uuid="8cd93577-512f-49b2-b0c6-2eaf02448641"/> <text><![CDATA[VAL]]></text> </staticText> </band> </columnHeader> <detail> <band height="20" splitType="Stretch"> <textField> <reportElement x="0" y="0" width="140" height="20" uuid="e96a559c-a82e-4c88-bf61-7734b7e3d110"/> <textFieldExpression><![CDATA[$F{CONTRACT_CODE}]]></textFieldExpression> </textField> <textField> <reportElement x="140" y="0" width="100" height="20" uuid="71eb5e60-9584-4393-a057-a95b474d2966"/> <textFieldExpression><![CDATA[$F{VAL}]]></textFieldExpression> </textField> </band> </detail></jasperReport>[/code] Link to comment Share on other sites More sharing options...
djohnson53 Posted December 11, 2018 Share Posted December 11, 2018 Not sure why you deleted the question:Hi all, I'm trying to work out how to make my IN statement use values based on user input i.e. user inputs 'A' the in statement uses 'X','Y', user inputs 'B', statement uses 'N','M'.CC.CONTRACT_CODE IN CASE WHEN $P{AIRLINE} = 'A' THEN ('PBCA') WHEN $P{AIRLINE} = 'B' THEN ('PBCP') END AND[/code]This works but if I try to add another value i.e. CASE WHEN $P{AIRLINE} = 'A' THEN ('PBCA','PBCP') insteadit won't work. Is there a better way to do this? TIA :) Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now