[deleted]

[stephanieaylamay]

[deleted]

[reportdev]

Since Oracle does not allow such kind of queries, you can use below work around

• Based on your AIRLINE parameter, we will write a java expression that will evaluate what is in AIRLINE and then Fill the hidden CONTRACT_CODE parameter
• This CONTRACT_CODE parameter will control the SQL

I 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]

 

[djohnson53]

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')  instead

it won't work. Is there a better way to do this? TIA :)