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