Using the Execute statement (Proc SQL) with Macro Variables

Reply
Occasional Contributor
Posts: 7

Using the Execute statement (Proc SQL) with Macro Variables

I have a program that queries multiple tables from Oracle (on a remote unix server).  The following statement works just fine:

EXECUTE(CREATE TABLE MY.TMP_FILE1 AS

  SELECT DISTINCT PRSN_IK

  FROM AD.VMBSH

  WHERE PURCH_IK IN(123, 456) BY REMOTE;

But my issue is that I need the values within the IN statement to be dynamic.  I have two macro variables already set at this point (reads data from a file and sets the macro variables accordingly).

So I tried this:

EXECUTE(CREATE TABLE MY.TMP_FILE1 AS

  SELECT DISTINCT PRSN_IK

  FROM AD.VMBSH

  WHERE PURCH_IK IN( &TE , &AC ) BY REMOTE;

and I get an "Oracle execute error".  It looks like it's not translating the macro variables into their values.

Any ideas on how I can get around this?

Super User
Posts: 10,532

Re: Using the Execute statement (Proc SQL) with Macro Variables

Did you try %put &te ∾ to examine if there might be unexpected leading or trailing blanks that may be getting passed to Oracle?

Or should they be passed as text explicitly using

IN ("&TE", "&AC")

?

Occasional Contributor
Posts: 7

Re: Using the Execute statement (Proc SQL) with Macro Variables

Sorry everyone, please close/delete this topic.  User error :smileycry:  I had set the session to be read only in the current job, but my test jobs were updated to be read/write.  Now it works fine.

Thank you anyway!

Super User
Posts: 10,532

Re: Using the Execute statement (Proc SQL) with Macro Variables

Glad you found the problem and shared the resolution.

And don't think that you're the first to write such code...

Super Contributor
Posts: 307

Re: Using the Execute statement (Proc SQL) with Macro Variables

What is the specific Oracle error you are getting? (For example, "ORA-01858: a non-numeric character was found where a numeric was expected".)

Ask a Question
Discussion stats
  • 4 replies
  • 259 views
  • 0 likes
  • 3 in conversation