SP Query with UNION: not possible?

Environment: IDS 7.31UC5 on Solaris 2.6 Hi all! I'm trying to write a Stored Procedure that returns the results from a query that has a UNION operator. Is this possible? All I'm getting are syntax errors. I tried putting the INTO clause just about anywhere but to no avail. CREATE PROCEDURE proc1(var1 CHAR(3)) RETURNING CHAR(10);DEFINE retvar CHAR(10); SELECT col2 from tab1 where col1 = var1 UNION SELECT col2 INTO retvar FROM tab2 where col1 = var1 RETURN retvar WITH RESUME END PROCEDURE; Thanks, Lyzander * Sent from RemarQ http://www.remarq.com The Internet's Discussion Network * The fastest and easiest way to search and participate in Usenet - Free!

You need a FOREACH loop to return more than one row from SPL. RTFineM. Art S. Kagel Zandy wrote: > > Environment: IDS 7.31UC5 on Solaris 2.6 > > Hi all! > > I'm trying to write a Stored Procedure that returns the results > from a query that has a UNION operator. Is this possible? All > I'm getting are syntax errors. I tried putting the INTO clause > just about anywhere but to no avail. > > CREATE PROCEDURE proc1(var1 CHAR(3)) > RETURNING CHAR(10);> DEFINE retvar CHAR(10); > > SELECT col2 from tab1 where col1 = var1> > UNION > > SELECT col2 INTO retvar FROM tab2 where col1 = var1 > > RETURN retvar WITH RESUME > END PROCEDURE; > > Thanks, > Lyzander > > * Sent from RemarQ http://www.remarq.com The Internet's Discussion Network * > The fastest and easiest way to search and participate in Usenet - Free!

Thanks Art. PS Whatever happened to the "Fuzzy Checkpoint" thread? I think William and Rudy has a point there. Cheers, Lyzander * Sent from RemarQ http://www.remarq.com The Internet's Discussion Network * The fastest and easiest way to search and participate in Usenet - Free!

Zandy wrote: [SNIP] > PS > Whatever happened to the "Fuzzy Checkpoint" thread? I think > William and Rudy has a point there. No question, the issue needed to be aired, and their concerns are real, I think we just exhausted the topic as far as we could take it here. The should become a thread on the engines SIG at IIUG. That is the proper forum since we can draw some Informix engine folk in and have a real discussion with them about how they feel that the data is safe. It's about time that that forum was actually used for its intended purpose. Art S. Kagel

You cannot use the statement: RETURN retvar WITH RESUME without using a FOREACH statement. Try this: CREATE PROCEDURE proc1(var1 CHAR(3)) RETURNING CHAR(10); DEFINE retvar CHAR(10); FOREACH cur_sor FOR SELECT col2 from tab1 where col1 = var1 UNION SELECT col2 INTO retvar FROM tab2 where col1 = var1 RETURN retvar WITH RESUME; END FOREACH; END PROCEDURE; Hope this helps. Don > Environment: IDS 7.31UC5 on Solaris 2.6 > > Hi all! > > I'm trying to write a Stored Procedure that returns the results > from a query that has a UNION operator. Is this possible? All > I'm getting are syntax errors. I tried putting the INTO clause > just about anywhere but to no avail. > > CREATE PROCEDURE proc1(var1 CHAR(3)) > RETURNING CHAR(10);> DEFINE retvar CHAR(10); > > SELECT col2 from tab1 where col1 = var1> > UNION > > SELECT col2 INTO retvar FROM tab2 where col1 = var1 > > RETURN retvar WITH RESUME > END PROCEDURE; > > Thanks, > Lyzander > > * Sent from RemarQ http://www.remarq.com The Internet's Discussion Network * > The fastest and easiest way to search and participate in Usenet - Free!

I've been gone for a few days. . . A few things should be clarified. 1. Physical consistency/the physical log has nothing to do with disk corruption. Presuming that the system(OS, hardware or power failure) has not corrupted the data "Physical Consistency" means that we have a stable point, with respect to TXN's, from which we can start logical recovery. In other words if the source of the failure is Informix's we can deal with it; If the source of failure is a third parties' and the failure is not of a nature which corrupts the data we can deal with it; If third parties' products(OS/Hardware/power) corrupt data there never has been any guarentee that we could handle all cases. For instance, mirroring has a statistically very low chance that the same page on two seperate disks will go bad. What if a disk manufacture's disks were so poor that it did happen? Would you consider this an Informix failure? 2. We do not physically log all pages and this is the way it has been for at least 10 years. This has nothing to do with fuzzy checkpoints. You may have just heard about the reduced physical logging that occurs with it. But that doesn't mean that prior to fuzzy checkpoints we logged every page. 3. Physical logging is not designed to correct hardware problems. It is designed to make logical recovery possible. If it occasionally does correct a partial page write then; a. it is a secondary effect b. it was only because we write some pages twice, to the physical log and chunk. This is similar to what mirroring does but with mirroring it was intended and designed that way. c. it never has been guaranteed and functionally has been that way for a long time. 4. Fuzzy checkpoints do not change the integrity of logical recovery. SUMMARY: Partial page writes rarely occured in the past and fuzzy checkpoints will not make the effect of this any worse and it does not introduce a new problem. With the increased use of disks with write caches, in off the shelf drives, systems are now even much less likely to run into the rare problem of partial writes. "Art S. Kagel" wrote: > Zandy wrote: > [SNIP] > > PS > > Whatever happened to the "Fuzzy Checkpoint" thread? I think > > William and Rudy has a point there. > > No question, the issue needed to be aired, and their concerns are real, > I think we just exhausted the topic as far as we could take it here. The > should become a thread on the engines SIG at IIUG. That is the proper > forum since we can draw some Informix engine folk in and have a real > discussion with them about how they feel that the data is safe. It's > about time that that forum was actually used for its intended purpose. > > Art S. Kagel -- If you want a fancy saying then go find yourself a poet. If you want a bug cracked then you've come to the right person. "The numbers speak to me" - 44 61 6E 20 57 6F 6F 64