Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2011
    Posts
    12

    Question Unanswered: Can cursor join with table ?

    Dear Sir,

    I want to know that cursor can join with table in the same sql ? Sample as below.

    declare c_1 cursor with hold for
    select case_num, amount from table1;

    declare c2 cursor with hold for
    select a.name, b.case_num, b.amount from table2 a, c_1 b where a.name = b.name (+);


    Thanks !

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    as long as the select is valid, it can also be defined for a cursor..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    amcc38,

    Why do you want to do such troublesome/useless way?
    It is not necessary to delare c_1.
    You can join table1 with table2 in c_2.

    where a.name = b.name (+)
    This syntax is valid only in Oracle or (may be valid) in DB2 setting compatibility vector to Oracle.
    Use of newer syntax using OUTER JOIN keyword is recommended.

  4. #4
    Join Date
    Oct 2011
    Posts
    12
    Quote Originally Posted by tonkuma View Post
    amcc38,

    Why do you want to do such troublesome/useless way?
    It is not necessary to delare c_1.
    You can join table1 with table2 in c_2.


    This syntax is valid only in Oracle or (may be valid) in DB2 setting compatibility vector to Oracle.
    Use of newer syntax using OUTER JOIN keyword is recommended.
    Actually, the c_1 will be used as master table and it will be joined with other tables and form several cursor for further use. If c_1 extraction logic has been changes, needn't to modify each cursor. Logically, I think it works, right ?

    Anyway, thanks !

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Actually, the c_1 will be used as master table and it will be joined with other tables and form several cursor for further use. If c_1 extraction logic has been changes, needn't to modify each cursor. Logically, I think it works, right ?
    Usually, using multiple cursor is a sign of bad design of DB access(with SQL).
    I think if you showed whole procedure using these cursors,
    I could show you the simplified design with reducing number of cursors(usually to one cursor).

    Anyway, you can go your way.

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by amcc38 View Post
    declare c2 cursor with hold for
    select a.name, b.case_num, b.amount from table2 a, c_1 b where a.name = b.name (+);
    No, this won't work in DB2. The "(+)" notation is (legacy) Oracle notation, not standard SQL. Do rather something like:
    Code:
    declare c2 cursor with hold for
    select b.case_num, b.amount, a.name
    from table2 a LEFT JOIN table1 b ON a.name = b.name
    Moreover, this cursor makes cursor c1 unnecessary: it returns exactly the same results in the first two columns as c1 (be it possibly repeatedly, and/or both NULL).
    Last edited by Peter.Vanroose; 10-27-11 at 17:02.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •