Re: SQL - 2 Result sets, 1 Cursor

["Michael Naughton" <mnaughton@xxxxxxxxxxxx>]

My solution would be similar to Vernon's, but I think it avoids re-running
the first query:

WITH tempa AS (SELECT itmkey, itmnam, itmtyp FROM itm)
SELECT A.*, B.cmtkey, B.cmttext FROM tempa A INNER JOIN cmt B
ON B.cmtitm=A.itmkey
ORDER BY A.itmkey (if you like)

I think this should return a result set with colums:
itmkey, itmnam, itmtyp, cmtkey, cmttext

HTH


midrange-l@midrange.com writes:
>This should answer all the questions asked and I guess it's not important
>the result sets are returned in one Cursor...two cursers from one stored
>procedure would be fine (I think, I'm new at this).
>
>Version 5.1
>Result set 1 will contain selected items from an item master file.
>Result set 2 will contain comments for each item selected in result set
>one. (One to many relationship)
>
>I guess the big question is how you use that first result set as input to
>the second select without building what we typically think of as a work
>file in QTEMP.  What I'm learning, Vernon's example of running the first
>select within the second select (i.e. running it twice) is not a bad
>solution and is still very quick.
>
>I'm still not sure about returning two result sets from one stored
>procedure, but that's something I can run and test.



Mike Naughton
Senior Programmer/Analyst
Judd Wire, Inc.
124 Turnpike Road
Turners Falls, MA  01376
413-863-4357 x444
mnaughton@juddwire.com