Issue with CASE statement nested in WHERE statement containing PROMPT and IN-CodePudding

I am using SQL inside Oracle Cloud BI Publisher product, so I assume this is true SQL or PL/SQL. I am trying to satisfy the following condition: When a user selects accounting period 12 (December) I need the query to fetch all data for periods 12 and 13 (December and Adjustment, respectively). When I get a parenthesis, single quote combo, or nested Select statement which works; the selecting of the prompt (:p_month) of 12 results in an "invalid number" return. Changing the parenthesis, quote stuff other ways yields a direct error from the query when trying to save.

I have tried the following with the grouped result for the portion after THEN:

"invalid number" returns from prompt evaluation of statement passed: ('12''13') ('12,13') (SELECT '12,13' from dual) (SELECT "'12','13'" from dual)

"invalid identifier" from query direct: (SELECT "12,13" from dual) (SELECT "'12,13'" from dual)

"missing right parenthesis" from query direct: (''12','13'') ('12','13')

The offending code line is directly below with the full code below that:

AND gl_periods.period_num IN (CASE WHEN (:p_month) = '12' THEN ('12','13') ELSE (:p_month) END)

SELECT 
     LPAD(gl_periods.period_num, 2,'0') AS period_num
    ,gl_periods.period_year AS period_year
    ,gl_code_combinations.segment1 AS segment1
    ,gl_code_combinations.segment2 AS segment2
    ,gl_code_combinations.segment3 AS segment3
    ,gl_code_combinations.segment4 AS segment4
    ,gl_code_combinations.segment5 AS segment5
    ,gl_code_combinations.segment6 AS segment6
    ,gl_code_combinations.segment7 AS segment7
    ,gl_ledgers.name AS ledger_name
    ,gl_ledgers.currency_code AS currency_code
    ,gl_balances.begin_balance_dr_beq AS begin_balance_dr
    ,gl_balances.begin_balance_cr_beq AS begin_balance_cr
    ,gl_balances.period_net_dr_beq AS activity_dr
    ,gl_balances.period_net_cr_beq AS activity_cr
    ,(SELECT DISTINCT
        fnd_flex_values_vl.attribute1
      FROM
        fnd_flex_values_vl,
        fnd_flex_value_sets
      WHERE
        1=1
        AND fnd_flex_values_vl.flex_value = gl_code_combinations.segment2
        AND fnd_flex_values_vl.flex_value_set_id = fnd_flex_value_sets.flex_value_set_id 
        AND fnd_flex_value_sets.flex_value_set_name = 'CostCenter XXX Enterprise') AS reporting_unit_code
    ,(SELECT DISTINCT
        fnd_flex_values_vl.attribute7
      FROM
        fnd_flex_values_vl,
        fnd_flex_value_sets
      WHERE
        1=1
        AND fnd_flex_values_vl.flex_value = gl_code_combinations.segment2
        AND fnd_flex_values_vl.flex_value_set_id = fnd_flex_value_sets.flex_value_set_id
        AND fnd_flex_value_sets.flex_value_set_name = 'CostCenter XXX Enterprise') AS operating_country_code
    ,(SELECT DISTINCT
        fnd_flex_values_vl.attribute6
      FROM
        fnd_flex_values_vl,
        fnd_flex_value_sets
      WHERE
        1=1
        AND fnd_flex_values_vl.flex_value = gl_code_combinations.segment2
        AND fnd_flex_values_vl.flex_value_set_id = fnd_flex_value_sets.flex_value_set_id
        AND fnd_flex_value_sets.flex_value_set_name = 'CostCenter XXX Enterprise') AS legal_country_code 
FROM
    gl_balances
    INNER JOIN gl_code_combinations ON gl_balances.code_combination_id = gl_code_combinations.code_combination_id
    INNER JOIN gl_ledgers ON gl_ledgers.ledger_id = gl_balances.ledger_id
    INNER JOIN gl_periods ON gl_balances.period_name = gl_periods.period_name
 WHERE
    1=1
    AND gl_balances.currency_code <> 'STAT'
    AND gl_balances.actual_flag = 'A'
    AND gl_periods.period_set_name = 'XXX Enterprise'
    AND gl_code_combinations.account_type IN ('A','L','O')
    AND (gl_balances.translated_flag IN ('N','R') OR gl_balances.translated_flag IS NULL)
    AND (gl_ledgers.ledger_id IN (:p_ledger) OR LEAST (:p_ledger) IS NULL)
    AND gl_periods.period_num IN (CASE WHEN (:p_month) = '12' THEN ('12','13') ELSE (:p_month) END)
    AND (gl_periods.period_year IN (:p_year) OR LEAST (:p_year) IS NULL)    
    AND gl_ledgers.ledger_category_code = 'PRIMARY'
    AND gl_ledgers.name NOT LIKE ('%SL%')

CodePudding user response：

I was able to get the return I wanted with the following code snippet:

AND (
        (gl_periods.period_num IN (CASE WHEN (:p_month) = '12' THEN '12' ELSE (:p_month) END))
        OR
        (gl_periods.period_num IN (CASE WHEN (:p_month) = '12' THEN '13' ELSE (:p_month) END))
    )

If there is a more elegant answer, I would love to see it as I am always open to new ideas.