converting SQL pass through code having with function to normal SQL

[maheshtalla]

Hi All,

We have sql pass-through code as below and get data using ODBC connection. can some help and suggest how to use with on pre-assigned libraries.

proc sql;
 connect to odbc as mydb
 (datasrc=dwprdsvc user=xxxxxxxxxxxx password=xxxxxxxxxxxxxxxxx);
create table Var_tmp as select * from connection to mydb (
 SET QUOTED_IDENTIFIER OFF
 execute executeoraclequery @query = 
%bquote(")
/* all the latest scenario forecasts */
with LATEST as (
select query...
),

DATA as (
select query...
),

now i have pre-assigned library and i'm not able to convert it to required format...

when i converted as below

proc sql;

     create table Oxford_Eco_Var_tmp as select * from  (

   with LATEST as (

select query...

 ),

DATA as (
select query...
),

i'm getting error as below.

proc sql;

27              create table Var_tmp as select * from  (

28                         with LATEST as (

                          __

                          22

                          201

ERROR 22-322: Syntax error, expecting one of the following: ',', ANSIMISS, CROSS, FULL, INNER, JOIN, LEFT, NATURAL, NOMISS, RIGHT. 

ERROR 201-322: The option is not recognized and will be ignored.

Please suggest and let me know.

Thanks in advance.

[ChrisNZ]

Your question makes no sense to me.

1. WITH creates a sort of a code shortcut, a view in a sense. It has nothing to do with libnames

2. WITH is not supported in SAS SQL

What are you trying to achieve?

[maheshtalla]

@ChrisNZ

the initial query work well with SQL-passthrough with odbc connection details with in that...but i have setup libraries to the database and pre-assigned them. now I modified the code accordingly and its not working.

[ChrisNZ]

You code should then look like this:

libname MYLIB odbc datasrc=dwprdsvc user=xxxxxxxx password=xxxxxxxxx;

proc sql;
  connect using MYLIB;
  create table Oxford_Eco_Var_tmp as select * from connection to MYLIB (
    with LATEST as ( ... )
    etc
  );
 quit;

[maheshtalla]

Hi all,

i'm able to resolve this issue, Basically the SQL-pass through i mentioned was using the execute query as below which submits the code to database which is having stored procedures and return back the results. All-though its nothing to do with pre-assigned libraries or SQL-pass thru it was probelm with stored procs, So we had same stored procs copied and it worked.

execute executeoraclequery @query = 

Thanks.