stored procedure and a UNION join
I've got a query that requires a union join. It's rather simplistic.
It does work, and returns 9085 rows. I wanted to write a procedure to
return all those rows. And while writing it, it occurred to me that I
couldn't have the INTO clause on each of the two selects, so I figured I
would need to do the union select first, put the results into a TEMP
table and then cursor throug a select on the temp table. So this is
what I had :
foreach crs cursor
select a,b into x,y from cat,dog where bla bla bla
union
select a,b into x,y from cat where bla bla bla
returning x,y with resume ;end foreach
so that didn't work, so I changed it into this :
select a,b from cat,dog where bla bla bla
union
select a,b from cat where bla bla bla
into temp tmp_table ;
foreach crs cursor
select a,b into x,y from tmp_table
returning x,y with resume ;end foreach
When I run this, instead of getting the 9085 rows that I expect I should
get, I get nothing, nada, zilch. No rows returned. I ran the union
select and put the results into the temp table in dbaccess, and it
created a temp table with 9085 rows in it. At the end of the procedure I
have a drop table statement. Since if I try running it again it tells
me that I have a table already out there with that name. I removed this
line, and after the procedure ran, I tried selecting on the table, and
it tells me it's not there, but if I run the procedure again it tells me
that it is there.
I suspect I'm overlooking some very obvious technical thing here that
I don't know about. Or maybe I'm just losing my mind. Also, is there a
better way to do this? Using a temp table in a procedure seems like a
nasty support headache down the road.
--
Curtis Bennett
CIBER, INC
Overland Park, KS
Sent via Deja.com http://www.deja.com/
Before you buy.
Curtis Bennett wrote:
> I've got a query that requires a union join. It's rather simplistic.
> It does work, and returns 9085 rows. I wanted to write a procedure to
> return all those rows. And while writing it, it occurred to me that I
> couldn't have the INTO clause on each of the two selects, so I figured I
> would need to do the union select first, put the results into a TEMP
> table and then cursor throug a select on the temp table. So this is
> what I had :
>
> foreach crs cursor
> select a,b into x,y from cat,dog where bla bla bla
> union
> select a,b into x,y from cat where bla bla bla
> returning x,y with resume ;> end foreach
>
> so that didn't work, so I changed it into this :
>
> select a,b from cat,dog where bla bla bla
> union
> select a,b from cat where bla bla bla
> into temp tmp_table ;>
> foreach crs cursor
> select a,b into x,y from tmp_table
> returning x,y with resume ;> end foreach
>
> When I run this, instead of getting the 9085 rows that I expect I should
> get, I get nothing, nada, zilch. No rows returned. I ran the union
> select and put the results into the temp table in dbaccess, and it
> created a temp table with 9085 rows in it. At the end of the procedure I
> have a drop table statement. Since if I try running it again it tells
> me that I have a table already out there with that name. I removed this
> line, and after the procedure ran, I tried selecting on the table, and
> it tells me it's not there, but if I run the procedure again it tells me
> that it is there.
>
> I suspect I'm overlooking some very obvious technical thing here that
> I don't know about. Or maybe I'm just losing my mind. Also, is there a
> better way to do this? Using a temp table in a procedure seems like a
> nasty support headache down the road.
If you use a UNION, you only need an INTO clause after the first
select list, not after the second or subsequent ones. So, droppingthe second INTO x, y should make your first option work.
I'm not clear why the SELECT ... UNION SELECT ... INTO TEMP followed
by a cursor on the temp table does not work. I would expect it to.
Are you doing anything odd with ON EXCEPTION?
--
Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net)
Guardian of DBD::Informix v0.95 -- see http://www.perl.com/CPAN
#include <disclaimer.h>
In article <38C0936C.E0856092@earthlink.net>,
Jonathan Leffler <jleffler@earthlink.net> wrote:
>
> If you use a UNION, you only need an INTO clause after the first
> select list, not after the second or subsequent ones. So, dropping> the second INTO x, y should make your first option work.
>
> I'm not clear why the SELECT ... UNION SELECT ... INTO TEMP followed
> by a cursor on the temp table does not work. I would expect it to.
> Are you doing anything odd with ON EXCEPTION?
Ok, you're right. I did :
select a,b
into x,y from bla where bla
union
select a,b from bla bla where bla bla
and I put that into a cursor and added the return with resume.When I ran it, I got no rows returned!
I removed the query from the procedure, minus the INTO and return clause
and ran it in dbaccess, and got rows. I'm thoroughly confused now. At
this point, I think it's time to hand it over to my DBA so he can work
with Informix to see if it's some kind of bug, which I'm inclined to
believe that it is.
I have no "On Exception" statements in this procedure.
> --
> Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net)
> Guardian of DBD::Informix v0.95 -- see http://www.perl.com/CPAN
> #include <disclaimer.h>
Sent via Deja.com http://www.deja.com/
Before you buy.
We have been using SPs with temp tables extensively without problems in multple 7.3x versions under HP and Solaris. Maybe you could post your procedure code, if its possible. Rudy Curtis Bennett wrote: > ... > > I suspect I'm overlooking some very obvious technical thing here that > I don't know about. Or maybe I'm just losing my mind. Also, is there a > better way to do this? Using a temp table in a procedure seems like a > nasty support headache down the road. > > -- > Curtis Bennett > CIBER, INC > Overland Park, KS