Hybrid Queries (from Fraktal SAS Programming): Unterschied zwischen den Versionen

Aus phenixxenia.org
Zur Navigation springen Zur Suche springen
K
K
 
(13 dazwischenliegende Versionen desselben Benutzers werden nicht angezeigt)
Zeile 1: Zeile 1:
−
[[Kategorie:Zazy]]
+
[[Kategorie:zazy]]
 +
{{SeitenNavigation1
 +
|links=xx_left.png
 +
|zurück=Libname Engine (from Fraktal SAS Programming)
 +
|rechts=xx_right.png
 +
|vorwärts=Passthru SQL (from Fraktal SAS Programming)
 +
|hoch=Duck_zazy_com.png
 +
|übersicht=DBMS Interaction (from Fraktal SAS Programming)
 +
}}
  
−
;1st Make SAS your SQL interpreter
+
== What is this? ==
  
−
proc sql;
+
The qualifying term ''"hybrid"'' denotes to the fact that a single piece of SQL code is processed by a number of systems that are integrated by appropriate middleware in a way that system borders are not visible when looking at the code.
  
−
;2nd Connect to the DBMS using your credentials stored in [[Macro Variables]]
+
We will use a maximum simplified task to be coded in SQL:
 +
* A '''SAS dataset''' shall be created
 +
* Data for which are kept in a '''DBMS table'''
 +
* The DBMS talks '''ANSI SQL'''
 +
* SAS can connect to the DBMS using '''SAS/Connect software'''
 +
* The solution uses '''Proc SQL''' from Base SAS software
  
−
connect to oracle (user="&DB_USR." password="&DB_PWD." path="&DB_PTH.");
 
  
−
;3rd Start your query from inside SAS with a special from clause
+
== Documented Code Example ==
  
 +
{| class="wikitable"
 +
|-
 +
! Code executed
 +
! Function performed
 +
|-
 +
|
 +
proc sql;
 +
| '''Start the SQL interpreter inside SAS'''
 +
|-
 +
|
 +
connect to oracle
 +
(user="&DB_USR."
 +
  password="&DB_PWD."
 +
  path="&DB_PTH."
 +
);
 +
| '''Open connection to the DBMS using your credentials stored in [[Symbol_Tables_(from_Fraktal_SAS_Programming)|Macro Variables]]'''
 +
|-
 +
| 
 +
create table my_sas_tab as
 +
| '''Initiate creating your SAS table using ANSI SQL code'''
 +
|-
 +
| 
 
  select *  
 
  select *  
 
   from connection to oracle
 
   from connection to oracle
−
 
+
| '''Start your query from inside SAS with a special from clause'''
−
;4th Inside brackets use DBMS specific SQL
+
|-
−
 
+
|
 
  (
 
  (
 
  select *  
 
  select *  
−
   from  
+
   from ops$&DB_USR..&MYTABLE.
 
  )
 
  )
−
 
+
| '''Inside brackets use DBMS specific SQL; [[Symbol_Tables_(from_Fraktal_SAS_Programming)|Macro Variables]] are resolved before code is passed to any processing system'''
−
;5th Execute the query by issuing the SAS statement terminator ';'
+
|-
−
 
+
|
 
  ;
 
  ;
 +
| '''Execute the query by issuing the SAS statement terminator ';''''
 +
|-
 +
|
 +
disconnect from oracle;
 +
| '''Close connection to DBMS'''
 +
|-
 +
|
 +
quit;
 +
| '''Terminate SAS SQL interpreter status'''
 +
|}
  
−
;6th Terminate connection to DBMS
+
{{SeitenNavigation1
−
 
+
|links=xx_left.png
−
disconnect to oracle;
+
|zurück=Libname Engine (from Fraktal SAS Programming)
−
 
+
|rechts=xx_right.png
−
;7th Terminate SAS SQL processor status
+
|vorwärts=Passthru SQL (from Fraktal SAS Programming)
−
 
+
|hoch=Duck_zazy_com.png
−
quit;
+
|übersicht=DBMS Interaction (from Fraktal SAS Programming)
 +
}}

Aktuelle Version vom 2. Juli 2014, 14:32 Uhr

Zurück

Übersicht

Vorwärts

What is this?

The qualifying term "hybrid" denotes to the fact that a single piece of SQL code is processed by a number of systems that are integrated by appropriate middleware in a way that system borders are not visible when looking at the code.

We will use a maximum simplified task to be coded in SQL:

  • A SAS dataset shall be created
  • Data for which are kept in a DBMS table
  • The DBMS talks ANSI SQL
  • SAS can connect to the DBMS using SAS/Connect software
  • The solution uses Proc SQL from Base SAS software


Documented Code Example

Code executed Function performed 
proc sql;
Start the SQL interpreter inside SAS 
connect to oracle 
(user="&DB_USR." 
 password="&DB_PWD." 
 path="&DB_PTH."
);
Open connection to the DBMS using your credentials stored in Macro Variables 
create table my_sas_tab as
Initiate creating your SAS table using ANSI SQL code 
select * 
  from connection to oracle
Start your query from inside SAS with a special from clause 
(
select * 
  from ops$&DB_USR..&MYTABLE.
)
Inside brackets use DBMS specific SQL; Macro Variables are resolved before code is passed to any processing system 
;
Execute the query by issuing the SAS statement terminator ';' 
disconnect from oracle;
Close connection to DBMS 
quit;
Terminate SAS SQL interpreter status

Zurück

Übersicht

Vorwärts

Abgerufen von „https://phenixxenia.org/w/index.php?title=Hybrid_Queries_(from_Fraktal_SAS_Programming)&oldid=3747“