Examples--WITH NATIVE SELECT
1. SELECT in Oracle:
SELECT name, dept, count(*) OVER (PARTITION BY dept) dept_count
FROM emp WITH NATIVE_SELECT;
SELECT col1 FROM table1 for update WITH NATIVE_SELECT;
SELECT /*+ no_parallel(iidbconstants) */ count(*)
FROM iidbconstants WITH NATIVE SELECT';
2. SELECT using Dynamic SQL:
mysql = 'SELECT /*+ no_parallel(iidbconstants) */ count(*)
FROM iidbconstants WITH NATIVE SELECT';
EXECUTE IMMEDIATE :mysql into col1;
3. SELECT in Microsoft SQL:
SELECT convert(varchar(30), user_name) FROM iidbconstants
WITH NATIVE_SELECT;
SELECT cast(sum(case when salary < 30000 then 1 else 0 end) as int)
as final FROM emp WITH NATIVE_SELECT;
4. SELECT in an OpenROAD application using Dynamic SQL:
Declare
mysql = varchar(256) not null,
col1 = varchar(256) not null,
col2 = varchar(256) not null,
col3 = varchar(256) not null,
mysql = 'select * from iidbconstants';
mysql = mysql + ' WITH NATIVE_SELECT';
EXECUTE IMMEDIATE :mysql into col1, col2, col3;
5. SELECT using embedded SQL:
char user[30], dba[30];
EXEC SQL select user_name, dba_name into :user, :dba
FROM iidbconstants WITH NATIVE_SELECT;
6. SELECT with an existing WITH clause in Microsoft SQL:
SELECT * FROM ingres.iigwuser WITH (XLOCK) WITH NATIVE_SELECT;
SELECT * FROM ingres.iigwuser WITH (XLOCK, ROWLOCK)
WITH NATIVE_SELECT;
7. SELECT with an existing WITH clause using Dynamic SQL:
mysql = ‘SELECT username FROM ingres.iigwuser WITH (XLOCK)
WITH NATIVE_SELECT’;
EXECUTE IMMEDIATE :mysql into col1;
Last modified date: 08/22/2022