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.

What is $col_g8_account set to PRIOR to the BEGIN-SELECT statement? — cardmagik, Jan 14 '18 at 18:08
I don't know SQL, but shouldn't `where account [$col_g8_account]` be `where account = [$col_g8_account]`? — William Robertson, Jan 14 '18 at 19:45
@William: possibly, but - there's also 'where ([$criteria2])' which suggests that those variables might contain more than just a single value, but something more, such as 'id = 25 and sal in (12, 23, 56)'. Who knows? Tom, if possible, display values of all variables in order to see what's passed to this SQL code. Use their exact value and rewrite query, then run it again - Oracle might point you to the culprit. — Littlefoot, Jan 14 '18 at 20:39
This isn't a SQL issue, it's an SQR issue. SQR "protects" SQL and puts its own variables in it. The SQR variable $col_g8_account could have = xxx in it or just xxx. A "Show $col_g8_account" prior to the Begin-SQL statement should show the problem but until @Tom_micua answers us, we won't know — cardmagik, Jan 14 '18 at 20:50
Hello everyone, the value of [$col_g8_account] comes from the result of the IF statement: if account = '1000000' then [$col_g8_account] value is 'like '1000%' else [$col_g8_account] value is '='1000000'' — Tom Micua, Jan 15 '18 at 06:19
@tom_micua I'm a little confused with the SQL statement - being in SQR, it should start with Begin-Select and end with End-Select. All variables after the Begin-Select should start in column 1, 1 per line like above (but in column 1). If you're doing that, then replace [$col_g8_account] with like '1000%' just to see what happens. In other words, form a good SQL statement within the bonds of SQR's syntax WITHOUT bind variables first and see what you get. Contact me at cardmagik at gmail.com if you want a quicker response. — cardmagik, Jan 16 '18 at 03:40
@cardmagik I already tried putting literals in the bind variables, it did work. So im a little confused why [$col_g8_account] is not working. — Tom Micua, Jan 18 '18 at 02:54

score 0 · Answer 1 · answered Jan 18 '18 at 21:36

0

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)

answered Jan 18 '18 at 21:36

cardmagik

1,698
19
17

Hi cardmagik, below is what I did and the SQR errored out again saying that I have an invalid relational operator: /* if substr($col_g4_account,6,5) = '00000' let $col_g4_account = 'like '''||substr($col_g4_account,1,5)||'%''' else let $col_g4_account = '= '''||$col_g4_account||'''' end-if */ – Tom Micua Jan 19 '18 at 07:54
3 questions: 1. Why did it change from $col_g8_account to $col_g4_account? Are you using $col_g4_account in your SQL now? 2. Can you do a show statement of $col_g4_account please and put it in comments? – cardmagik Jan 20 '18 at 04:13

Dynamic bind variables not being recognized in inline view

1 Answers1