result = DB_SQL(dbms_id, "sql_command")
note | PV‑WAVE single-line SQL command support does not include the ability to execute Block SQL statements. Execution of stored procedures, however, is supported, so we recommend that users who wish to perform more complicated DBMS operations from PV‑WAVE enclose them in a DBMS stored procedure. For more info on creating stored procedures, contact your database administrator. |
; Connect to the Oracle database 'mydbserv', with username
; 'scott' and password 'tiger'
oracle_id = DB_CONNECT( ”ORACLE”, ”scott/tiger@mydbserv”)
table = DB_SQL( oracle_id, ”SELECT * FROM wave.wave_prop_trx”)
INFO, table
; TABLE STRUCT = -> TABLE_1855432390284244950984412
; Array(10000)
info, table, /Structure
;* Structure TABLE_1855432390284244950984412, 8 tags, 72 length:
; TRX_ID LONG 0
; PROP_TYPE STRING 'OTHER '
; PROP_ADDRESS STRING ''
; PROP_POST_CD STRING ''
; PROP_XGRID DOUBLE 0.0075200000
; PROP_YGRID DOUBLE 1.6357100
; TRX_AMT DOUBLE 116383.00
; TRX_DATE STRUCT -> !DT Array(1)
oracle_id = DB_CONNECT( ”ORACLE”, ”scott/tiger@mydbserv”)
; Create the SQL command as a PV-WAVE variable First, create the
; column list
sql_command = ”SELECT trx_id, prop_type, ” + $
”trx_amt, trx_date ” + ”FROM wave.wave_prop_trx ”
; Next, add a WHERE clause to limit the number of rows. This
; limits the subset to all dates between June 6, 1999 and June
; 6, 2001
sql_command = sql_command + $
”WHERE trx_date <= TO_DATE('2001/06/01', 'YYYY/MM/DD') ” + $
” AND trx_date > TO_DATE('1999/06/01', 'YYYY/MM/DD') ”
; Finally add an ORDER BY clause to sort the dates in order
sql_command = sql_command + ”ORDER BY trx_date”
sub_table = DB_SQL( oracle_id, sql_command)
INFO, sub_table
; SUB_TABLE STRUCT = -> TABLE_2080423439256551873139501
; Array(947)
INFO, sub_table, /Structure
;* Structure TABLE_2080423439256551873139501, 4 tags, 48 length:
; TRX_ID LONG 7514
; PROP_TYPE STRING 'OTHER '
; TRX_AMT DOUBLE 206871.00
; TRX_DATE STRUCT -> !DT Array(1)
DT_TO_STR, sub_table(0).trx_date, tmp_date, tmp_time, $
Date_Fmt=5, Time_Fmt=-1
PRINT, tmp_date + ” ” + tmp_time
; 1999/06/01 22:20:37.000
note | Very long SQL statements may not fit in a single PV‑WAVE command string. For very long SQL statements, we recommend that you “build” the command in a PV‑WAVE string variable, which can be any length. |
oracle_id = DB_CONNECT( 'ORACLE', 'scott/tiger@mydbserv')
; Select the average transaction amount for each property type,
; ordered by property type
amt_by_type = DB_SQL( oracle_id, 'SELECT prop_type, ' + $
'AVG(trx_amt) my_avg_amt, ' + 'SUM(trx_amt) my_total_amt ' + $
'FROM wave.wave_prop_trx ' + 'GROUP by prop_type ' + $
'ORDER by prop_type')
INFO, amt_by_type
; AMT_BY_TYPE STRUCT = -> TABLE_1990902712472184093171925
; Array(9)
INFO, amt_by_type, /Structure
;* Structure TABLE_1990902712472184093171925, 3 tags, 24 length:
; PROP_TYPE STRING '1BR_CONDO '
; MY_AVG_AMT DOUBLE 80501.404
; MY_TOTAL_AMT DOUBLE 87666029.
note | When using expressions or aggregate functions in an SQL SELECT column list, we recommend that you use a column alias. This will help ensure that the tag name is valid in the PV‑WAVE table variable. |
table = DB_SQL( oracle_id, 'SELECT * from wave.wave_prop_trx')
amt_by_type_2 = QUERY_TABLE( table, 'prop_type, ' + $
'AVG(trx_amt) my_avg_amt, ' + 'SUM(trx_amt) my_total_amt ' + $
'group by prop_type')
amt_by_type_2 = ORDER_BY( amt_by_type_2, 'prop_type')
INFO, amt_by_type_2
; AMT_BY_TYPE_2 STRUCT = -> TABLE_3150083162320518139151666
; Array(9)
INFO, amt_by_type_2, /Structure
;* Structure TABLE_3150083162320518139151666, 3 tags, 24 length:
; PROP_TYPE STRING '1BR_CONDO '
; MY_AVG_AMT DOUBLE 80501.404
; MY_TOTAL_AMT DOUBLE 87666029.
PV‑WAVE supports some searching, sorting, and aggregate functions internally (with the WHERE and QUERY_TABLE functions, for example). In many cases, PV‑WAVE searching and sorting algorithms may be faster than performing them on the DBMS server (with DB_SQL). We recommend that you try importing data into PV‑WAVE with a minimum of sorting, and use PV‑WAVE functions to sort, group, and search the data. |
note | We suggest that you use explicit SELECT lists (no wildcards) and column aliases when importing data through a multi-table join. |
oracle_id = DB_CONNECT( ”ORACLE”, ”scott/tiger@mydbserv”)
; Create the SQL command as a PV-WAVE variable. This query
; combines data from 3 normalized tables
sql_command = ”SELECT dpnt.air_temp air_temp, ” + $
”dpnt.humidity humidity, ” + $
”dpnt.atm_press atm_press, ” + $
”dpnt.o3_ppm o3_ppm, ” + $
”dpnt.co_ppm co_ppm, ” + $
”dpnt.no2_ppm no2_ppm, ” + $
”dpnt.pm10_ug_m3 pm10_ug_m3, ” + $
”dset.dataset_id dataset_id, ” + $
”dset.start_date ref_date, ” + $
”dloc.grid_x grid_x, ” + $
”dloc.grid_y grid_y ” + $
”FROM wave.wave_ts_datapoint dpnt, ” + $
”wave.wave_ts_dataset dset, ” + $
”wave.wave_ts_location dloc ”
; Join and data limits. Only plot data for grid ID = 1. And for
; datasets which started during 1997 through 2002
sql_command = sql_command + $
”WHERE dset.dataset_id = dpnt.dataset_id ” + $
”AND dset.start_date >= TO_DATE('19970101', 'YYYYMMDD') ” + $
”AND dset.start_date < TO_DATE('20030101', 'YYYYMMDD') ” + $
”AND dloc.loc_id = dpnt.loc_id ” + $
”AND dloc.start_date <= dset.start_date ” + $
”AND ( dloc.end_date > dset.start_date ” + $
” OR dloc.end_date IS NULL) ” + $
”AND dloc.grid_id = 1 ”
; Perform the query
table = DB_SQL( oracle_id, sql_command)
INFO, table
; TABLE STRUCT = -> TABLE_2808314677754116534184991
; Array(3400)
INFO, table, /Structure
;* Structure TABLE_2808314677754116534184991, 11 tags, 72
; length:
; AIR_TEMP FLOAT 29.2000
; HUMIDITY FLOAT 26.7000
; ATM_PRESS FLOAT 753.520
; O3_PPM FLOAT 0.0434300
; CO_PPM FLOAT 3.61000
; NO2_PPM FLOAT 0.0347400
; PM10_UG_M3 FLOAT 21.1800
; DATASET_ID LONG 6
; REF_DATE STRUCT -> !DT Array(1)
; GRID_X FLOAT -1.46000
; GRID_Y FLOAT 6.15000
note | PV‑WAVE only supports table JOINs during data import. JOINs are not allowed on PV‑WAVE table data after import. |
TEST_STRING TEST_DATE TEST_NUM
----------- --------- --------
<NULL> 04-JUL-1776 3.14
<NULL_STRING> <NULL> 0
Not null! 04-JUL-1776 <NULL>
oracle_id = DB_CONNECT( ”ORACLE”, ”scott/tiger@mydbserv”)
table = DB_SQL(oracle_id, ”SELECT * FROM $
wave.wave_conv_test_nulls”)
INFO, table
; TABLE STRUCT = -> TABLE_2251731550291596501887914
; Array(3)
INFO, table, /Structure
;* Structure TABLE_2251731550291596501887914, 3 tags, 48 length:
; TEST_STRING STRING ''
; TEST_DATE STRUCT -> !DT Array(1)
; TEST_NUM DOUBLE 3.1400000
INFO, table(1), /Structure
;* Structure TABLE_2251731550291596501887914, 3 tags, 48 length:
; TEST_STRING STRING ''
; TEST_DATE STRUCT -> !DT Array(1)
; TEST_NUM DOUBLE 0.0000000
INFO, table(2), /Structure
;* Structure TABLE_2251731550291596501887914, 3 tags, 48 length:
; TEST_STRING STRING 'Not null! '
; TEST_DATE STRUCT -> !DT Array(1)
; TEST_NUM DOUBLE 0.0000000
table_2 = DB_SQL(oracle_id, $
”SELECT NVL(test_string, '_NULL_') test_string, ” + $
”NVL(test_date, TO_DATE('29991231', 'YYYYMMDD')) $
test_date,” + ”NVL(test_num, -999999.98) test_num ” + $
”FROM wave.wave_conv_test_nulls”)
INFO, table_2, /Structure
;* Structure TABLE_3088719732127463461882630, 3 tags, 48 length:
; TEST_STRING STRING '_NULL_ '
; TEST_DATE STRUCT -> !DT Array(1)
; TEST_NUM DOUBLE 3.1400000
INFO, table_2(1), /Structure
;* Structure TABLE_3088719732127463461882630, 3 tags, 48 length:
; TEST_STRING STRING ''
; TEST_DATE STRUCT -> !DT Array(1)
; TEST_NUM DOUBLE 0.0000000
INFO, table_2(2), /Structure
;* Structure TABLE_3088719732127463461882630, 3 tags, 48 length:
; TEST_STRING STRING 'Not null! '
; TEST_DATE STRUCT -> !DT Array(1)
; TEST_NUM DOUBLE -999999.98
table_3 = DB_SQL(oracle_id, ”SELECT test_string, ” + $
”DECODE(test_string, NULL, -1, 0) test_string_i, ” + $
”test_date, ” + $
”DECODE(test_date, NULL, -1, 0) test_date_i, ” + $
”test_num, ” + $
”DECODE(test_num, NULL, -1, 0) test_num_i ” + $
”FROM wave.wave_conv_test_nulls”)
INFO, table_3, /Structure
;* Structure TABLE_2739531713696126301209217, 6 tags, 72 length:
; TEST_STRING STRING ''
; TEST_STRING_I DOUBLE -1.0000000
; TEST_DATE STRUCT -> !DT Array(1)
; TEST_DATE_I DOUBLE 0.0000000
; TEST_NUM DOUBLE 3.1400000
; TEST_NUM_I DOUBLE 0.0000000
INFO, table_3(1), /Structure
;* Structure TABLE_2739531713696126301209217, 6 tags, 72 length:
; TEST_STRING STRING ''
; TEST_STRING_I DOUBLE 0.0000000
; TEST_DATE STRUCT -> !DT Array(1)
; TEST_DATE_I DOUBLE -1.0000000
; TEST_NUM DOUBLE 0.0000000
; TEST_NUM_I DOUBLE 0.0000000
INFO, table_3(2), /Structure
;* Structure TABLE_2739531713696126301209217, 6 tags, 72 length:
; TEST_STRING STRING 'Not null! '
; TEST_STRING_I DOUBLE 0.0000000
; TEST_DATE STRUCT -> !DT Array(1)
; TEST_DATE_I DOUBLE 0.0000000
; TEST_NUM DOUBLE 0.0000000
; TEST_NUM_I DOUBLE -1.0000000
FUNCTION Read_Dept
; Read the employee name and department number from the
; database and return a new table.
ON_IOERROR, Bad
; Connect To DBMS
;===============
PRINT, 'DB_CONNECT:'
oracle_id=DB_CONNECT('ORACLE', 'scott/tiger')
PRINT, 'Ok'
; Import data from the database.
;=============================
PRINT, 'DB_SQL:'
table = DB_SQL(oracle_id, 'SELECT ename,' + 'deptno from emp')
PRINT, 'Ok'
; Disconnect from the DBMS.
;========================
PRINT, 'DB_DISCONNECT: '
DB_DISCONNECT, oracle_id
PRINT, 'Ok'
PRINT, 'End'
RETURN, table
Bad:
PRINT, 'Bad'
END