1 Reply Latest reply: Apr 30, 2012 5:19 AM by Neelam N RSS

Using Global Variables in a SQL query

Todd Clayton

Currently using Robert Stinnetts wonderful script to parse variables from a flat file into a global variable in Control-M. They're getting there AOK.

 

ctmapn01% ctmvar -action list

<<< GLOBAL >>>

 

VAR                                       VAREXPR

----------------------------------------  ---------------------------------

%%POSTBILL_1                             51002103                              

%%POSTBILL_2                             20120604                              

%%POSTBILL_3                             51001779                              

ctmapn01%

 

However, when I try to pass them to an open query in a CTM databases job, I'm getting an error...

 

select * from customer_node_history c, account a

where c.schedule_id = %%POSTBILL_3

and c.effective_end_date > sysdate

and a.customer_node_id = c.customer_node_id

and a.account_balance <> 0

and c.customer_node_id not in (select i.customer_node_id from invoice i where i.bill_run_id = %%POSTBILL_1)

 

ORA-00904: "CTMERRPOSTBILL_3": invalid identifier

 

The problem seems to be that it doesn't like the variable. Am I using this properly?

  • 1. Using Global Variables in a SQL query
    Neelam N

    Hi Todd,

     

    Please check the value you are trying to assigen to %%POSTBILL_3 is getting assigened properly or not (or the value beeing set to this variable is might be blank)

     

    If it is not the case then try to close this variable in single cotation as below:

    where c.schedule_id = '%%POSTBILL_3'

     

    Regards,

    Neelam