Results 1 to 8 of 8

Thread: cursor help

  1. #1
    Join Date
    Oct 2004
    Posts
    4

    Unanswered: cursor help

    I want to qualify date1 from a date field from of table1 and then use the date1 to qualify a second select statment on table2. I'm confused on how to handle the declare statment and then open the cursor in the second statement. any help would be appreicated. thanks.

    my example is

    @mydate1 = select date1 from table1
    select * from table2 where date2 = @mydate1

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    select * from table2 where date2 = (select date1 from table1)

    If there is more than value retrieved (even if the multiple values are equal) in the subselect then change the = to IN. Or use a select distinct if the multiple values are equal in the subselect.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Oct 2004
    Posts
    4
    yes I understand the subselect statment works the same way,
    but that's not what I'm looking for, I'm looking for the right way to declare and the then reference a cursor.
    thanks

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    That depends entirely on what programming language or procedure you are using. You want to store the output of a query in a host variale and then use the host variable in the next query.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Oct 2004
    Posts
    4
    I guess what I'm looking to do is come up with a type of session or temporary variable that can be altered based on what sepcific time period the users are looking for. for the end users who are writing sql against db2 on unix.
    by declaring or creating a variable/Alias upfront and then referencing the vairable thru out the code, I'm hoping to gain a better access paths against the database server instead of joining to multiple tables.

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Are you wanting to use the sql in a unix script, C program, stored procedure, etc? Please explain. It makes a difference.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Oct 2004
    Posts
    4
    the end users typicaly use business object to build and automate their reports.

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    So, it's either ODBC or JDBC... I guess you can't do what you want in plain SQL. You could create a stored proc that returns a resultset to the caller and pass the date to that stored proc; however I'm not sure BusinessObjects can invoke stored procedures...

Posting Permissions

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