1 Reply Latest reply on Apr 30, 2012 5:19 AM by Neelam Nilje

    Using Global Variables in a SQL query

      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                              



      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?