Create a view in Oracle SQL from SAS

[Weibull]

I'm trying to create a view from SAS to SQL (i.e. I want to be able to query this view in SQL).

Let's say I have a bunch of tables called TABLE1, TABLE2, TABLE3....TABLE10.

I want a view that runs the following code:

select * from TABLE1
UNION
select * from TABLE2
UNION
select * from TABLE3
UNION......

So I want to create a view that runs the code to create a union of all tables. In SAS I created a macro variable that literally has all of that code. However when I run the following:

proc sql noprint;
create view SQL.TABLE_VIEW as &MACRO_VAR; quit;

I just get an error saying it cannot be processed directly by the database. Just curious if anyone knows how to solve this?

 

[bryangoodrich]

Do the tables reside in SAS or Oracle?

 

[bryangoodrich]

Also, what is the intention of doing union? Do you merely want your data to be all in one table to query?

1. Use UNION ALL in place of UNION. If you just do union, any duplicate rows will be discarded and the operation is much slower due to that constraint
2. If your tables hold distinct segments of some variable (say, monthly ranges of a date), then what you may be trying to do is create a partitioned view. It's essentially what partitioned tables take care of behind the scenes in a RDBMS. For this to work, you need each table to have defined on it check constraints to ensure when you query the table for, say, only a day in a given month, it doesn't check EVERY table in your massive union to find what resides only in one table. Essentially

SELECT * FROM TblJan UNION ALL
SELECT * FROM TblFeb UNION ALL
SELECT * FROM TblMar UNION ALL 
...
SELECT * FROM TblDec

https://sqlsunday.com/2014/08/31/partitioned-views/

As they show there, you would need to have defined on each table check constraints of the sort

ALTER TABLE TblJan
ADD CONSTRAINT CHK_TblJan
CHECK ([date]>={d '2014-01-01'} AND [date] < {d'2014-02-01'});

This sort of range bounding on the partitioning column (in my example "date"), you would have an optimized partitioned view, if that's what you're trying to do with this. I'm not an Oracle guy, but here they talk about check constraints for Oracle. Not terribly different https://www.techonthenet.com/oracle/check.php

 

[Weibull]

That still sounds like having to literally write the SELECT statement for every single table - I was trying to avoid that and have almost some loop or something. I'm not really sure. I will probably just handle this in SAS since I've done this there before.

 

[bryangoodrich]

Well it's not uncommon to use another language or even SAS (if you must) to build your SQL expression ("string builder" functions).

Def Months = [Jan, ..., Dec]
String sql_select = ""
String sql_alter = ""
For Month in Months:
    sql_select = "SELECT * FROM Tbl" + Month + "UNION ALL\n"
    sql_alter = ... a little more complex statement ...

s = "CREATE VIEW foobar AS " + sql_select

Print sql_select
Print sql_alter

This is just pseudo code, but with a couple lines of code and metadata (the list), you can build large SQL expressions. Run once, you're done (or execute the statement against the database directly instead of printing it). The whole point of creating a view is to have a defined SQL expression. Build the expression. Execute.