Dynamic bind variables not being recognized in inline view

Question

I have the following code written in SQR:

select
    business_unit        &g8_iee_business_unit
    ledger               g8_iee_ledger
    sum_posted_total_amt &yb_g8_iee_amount_selected
    ft_yb_iee_RPT_NY     &g8_iee_rpt_row_ny
    currency_cd          &g8_iee_currency_cd
 From ( 
    with a as ( 
        Select DISTINCT account
        From ps_gl_account_tbl
        Where account [$col_g8_account]
    )
    SELECT /*+LEADING(a g8row g8func g8ybdata) PX_JOIN_FILTER(g8ybdata) swap_join_inputs(g8row) use_hash(g8row) swap_join_inputs(g8func) use_hash(g8func) swap_join_inputs(angel) use_hash(angel)*/
        g8row.ft_yb_iee_rpt_ny, g8ybdata.business_unit, g8ybdata.ledger,
        sum(g8ybdata.posted_total_amt) sum_posted_total_amt,
        g8ybdata.currency_cd 
    from ps_ft_yba_rul8_row g8row
        , ps_ledger g8ybdata
        , a
    where ([$criteria2])
        and g8ybdata.ledger in ([$verledger])
        and g8ybdata.account = a.account 
        and (g8ybdata.currency_cd = 'USD' or g8ybdata.currency_cd = ' ' ) 
        and g8ybdata.fiscal_year = #year
        and [$account_period_select] 
        and g8ybdata.product = g8row.product 
        and ( g8row.ft_yb_iee_rpt_ny > '18' AND g8row.ft_yb_iee_rpt_ny < '22' ) 
    group by g8row.ft_yb_iee_rpt_ny, g8ybdata.business_unit, g8ybdata.ledger, g8ybdata.currency_cd
)
order by FT_YB_IEE_RPT_NY, business_unit, ledger, currency_cd;

It seems like the [$col_g8_account] in the inline view can't be read, because it is throwing me an error 'invalid relational operator':

(SQR 5528) ORACLE OCIStmtExecute error 920 in cursor 6:    ORA-00920: invalid relational operator SQL: SELECT business_unit, ledger, sum_posted_total_amt, FT_YB_IEE_RPT_NY,       ft_yb_rpt_col_char, CHARTFIELD2, currency_cd From ( with a as ( Select       DISTINCT account From ps_gl_account_tbl Where account group by       g8row.FT_YB_IEE_RPT_NY,g8func.ft_yb_rpt_col_char, g8func.CHARTFIELD2,       g8ybdata.business_unit, g8ybdata.ledger, g8ybdata.currency_cd ) 

Error on line 682:    (SQR 3716) Error in SQL statement.

(SQR 5528) ORACLE OCIStmtExecute error 920 in cursor 7:    ORA-00920: invalid relational operator SQL: SELECT business_unit, ledger, sum_posted_total_amt, ft_yb_iee_RPT_NY,       currency_cd From ( with a as ( Select DISTINCT account From       ps_gl_account_tbl Where account group by g8row.ft_yb_iee_rpt_ny,       g8ybdata.business_unit, g8ybdata.ledger, g8ybdata.currency_cd ) 

Error on line 803:    (SQR 3716) Error in SQL statement.

Errors were found in the program file.

SQR for PeopleSoft: Program Aborting.

Answer 1

The issue is embedded quotes in the dynamic variable.

I believe you're trying something like this(I don't know your conditional criteria so I created ShowSingle):

if #ShowSingle = 1
   Let $col_g8_account = '='100000''
else
   Let $col_g8_account = ' like '1000%''
End-If

The problem is that when you embed quotes, you have to double them up. This is hard to see, but what you need to do is something like this:

if #ShowSingle = 1
   Let $col_g8_account = '=''100000'''
else
   Let $col_g8_account = 'like ''1000%'''
End-If

I have tested this and it works (and fails accordingly too)