Was this helpful?
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: 02/16/2024