Development Tools Forum

Re: migrate from oracle start with/ connect by

Posted By: Art Kagel
Date: Monday, 18 August 2008, at 9:28 p.m.

In Response To: migrate from oracle start with/ connect by (CHUAN LU)

See the Guide to SQL Tutorial, Guide to SQL Reference, Guide to SQL Syntax,
and DBaccess manuals.

In Informix you connect similarly:

You can run a script containing the commands as:

dbaccess - - < myscript.sql

or use a 'here' script, the commands are the same:

dbaccess - - <<EOF
connect to mydatabase@myserver user 'mylogin';
Password: mypassword

select * from emp;
EOF

Art

On Mon, Aug 18, 2008 at 8:14 PM, CHUAN LU <luchuan@cn.ibm.com> wrote:

> Do anybody know how to migrate from oracle start with /connec by to
> informix
> in SP?
> Oracle's sample like this.
> SQL> conn scott/tiger
> Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
> Connected as scott
>
> SQL> select * from emp;
>
> EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
> ----- ---------- --------- ----- ----------- --------- --------- ------
> 7369 SMITH CLERK 7902 1980-12-17 800.00 20
> 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
> 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
> 7566 JONES MANAGER 7839 1981-4-2 2975.00 20
> 7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
> 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
> 7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
> 7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
> 7839 KING PRESIDENT 1981-11-17 5000.00 10
> 7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
> 7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
> 7900 JAMES CLERK 7698 1981-12-3 950.00 30
> 7902 FORD ANALYST 7566 1981-12-3 3000.00 20
> 7934 MILLER CLERK 7782 1982-1-23 1300.00 10
>
> 14 rows selected
>
> SQL> insert into emp(empno, ename, mgr, deptno)
> values(9999,'Menglk_9',null,10);
>
> 1 row inserted
>
> SQL> insert into emp(empno, ename, mgr, deptno)
> values(8888,'Menglk_8',7788,10);
>
> 1 row inserted
>
> SQL> commit;
>
> Commit complete
>
> SQL> select * from emp;
>
> EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
> ----- ---------- --------- ----- ----------- --------- --------- ------
> 7369 SMITH CLERK 7902 1980-12-17 800.00 20
> 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
> 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
> 7566 JONES MANAGER 7839 1981-4-2 2975.00 20
> 7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
> 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
> 7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
> 7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
> 7839 KING PRESIDENT 1981-11-17 5000.00 10
> 7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
> 7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
> 7900 JAMES CLERK 7698 1981-12-3 950.00 30
> 7902 FORD ANALYST 7566 1981-12-3 3000.00 20
> 7934 MILLER CLERK 7782 1982-1-23 1300.00 10
> 9999 Menglk_9 10
> 8888 Menglk_8 7788 10
>
> 16 rows selected
> SQL> select level, lpad(' ',level*2,' ')||ename, t.* from emp t start with
> mgr
> is null connect by mgr=prior empno;
>
> LEVEL LPAD('',LEVEL*2,'')||ENAME EMPNO ENAME JOB MGR HIREDATE SAL COMM
> DEPTNO
> ---------- ---------------------------- ----- ---------- --------- -----
> ----------- --------- --------- ------
>
> 1 KING 7839 KING PRESIDENT 1981-11-17 5000.00 10
>
> 2 JONES 7566 JONES MANAGER 7839 1981-4-2 2975.00 20
>
> 3 SCOTT 7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
>
> 4 ADAMS 7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
>
> 4 Menglk_8 8888 Menglk_8 7788 10
>
> 3 FORD 7902 FORD ANALYST 7566 1981-12-3 3000.00 20
>
> 4 SMITH 7369 SMITH CLERK 7902 1980-12-17 800.00 20
>
> 2 BLAKE 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
>
> 3 ALLEN 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
>
> 3 WARD 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
>
> 3 MARTIN 7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
>
> 3 TURNER 7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
>
> 3 JAMES 7900 JAMES CLERK 7698 1981-12-3 950.00 30
>
> 2 CLARK 7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
>
> 3 MILLER 7934 MILLER CLERK 7782 1982-1-23 1300.00 10
>
> 1 Menglk_9 9999 Menglk_9 10
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)

Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do those
opinions reflect those of other individuals affiliated with any entity with
which I am affiliated nor those of the entities themselves.

Messages In This Thread

• migrate from oracle start with/ connect by
  CHUAN LU -- Monday, 18 August 2008, at 8:14 p.m.
  • Re: migrate from oracle start with/ connect by
    Art Kagel -- Monday, 18 August 2008, at 9:28 p.m.
    • Re: migrate from oracle start with/ connect by
      CHUAN LU -- Monday, 18 August 2008, at 11:49 p.m.
    • Re: migrate from oracle start with/ connect by
      Jonathan Leffler -- Monday, 18 August 2008, at 11:51 p.m.

Development Tools Forum is maintained by Administrator with WebBBS 5.12.