| View previous topic :: View next topic |
| Author |
Message |
wishlister
Beginner
Joined: 08 Mar 2007
Posts: 16
Topics: 3
|
 Posted: Thu Mar 08, 2007 12:05 pm Post subject: union in stored procedure |
 |
|
| how will i perform UNION of SELECT statements in stored procedure? |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
|
| Back to top |
|
|
wishlister
Beginner
Joined: 08 Mar 2007
Posts: 16
Topics: 3
|
| Posted: Thu Mar 08, 2007 12:25 pm Post subject: |
|
|
| can u give me an example? union of 2 select statements where there is cursor also.. ty! the samples in the link did not help |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
| Posted: Thu Mar 08, 2007 12:33 pm Post subject: |
|
|
| wishlister wrote: | | can u give me an example? union of 2 select statements where there is cursor also.. ty! the samples in the link did not help |
what do you mean it did not help? Did you get an error? if so what is the error?
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
wishlister
Beginner
Joined: 08 Mar 2007
Posts: 16
Topics: 3
|
| Posted: Thu Mar 08, 2007 12:43 pm Post subject: |
|
|
db2 ESE
for
SELECT <statement>;
OPEN Cursor1;
FETCH <statement>;
WHILE SQLSTATE = '00000' Do
INSERT INTO <statement>;
FETCH <statement>;
END WHILE;
close Cursor1;
UNION
for
for
SELECT <statement>;
OPEN Cursor2;
FETCH <statement>;
WHILE SQLSTATE = '00000' Do
INSERT INTO <statement>;
FETCH <statement>;
END WHILE;
close Cursor2;
//An unexpected token "for" was found |
|
| Back to top |
|
|
wishlister
Beginner
Joined: 08 Mar 2007
Posts: 16
Topics: 3
|
| Posted: Thu Mar 08, 2007 12:48 pm Post subject: |
|
|
another question..
cursor1 is for the first table and cursor2 for the second table. is it right to use different cursors? or can i use only one cursor? |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
| Posted: Thu Mar 08, 2007 12:50 pm Post subject: |
|
|
wishlister,
you just need 1 cursor
try this
| Code: |
for
SELECT <statement 1>
UNION
SELECT <statement 2>;
OPEN Cursor1;
FETCH <statement>;
WHILE SQLSTATE = '00000' Do
INSERT INTO <statement>;
FETCH <statement>;
END WHILE;
close Cursor1;
|
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
wishlister
Beginner
Joined: 08 Mar 2007
Posts: 16
Topics: 3
|
| Posted: Thu Mar 08, 2007 12:58 pm Post subject: |
|
|
tnx! i know it will work but now, im having problem with my field names..
SELECT no, NAME, type, rate, hrs from HR
UNION
SELECT no, NAME, type, sal, bonus from MIS;
//error: The data types of corresponding columns are not compatible in a fullselect that includes a set operator or in the multiple rows of a VALUES clause of an INSERT or fullselect. |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
| Posted: Thu Mar 08, 2007 1:04 pm Post subject: |
|
|
wishlister,
If the attributes are different then convert them to a common data attribute.
ex:
| Code: |
SELECT no, NAME, type, char(rate), char(hrs) from HR
UNION
SELECT no, NAME, type, char(sal), char(bonus) from MIS;
|
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
wishlister
Beginner
Joined: 08 Mar 2007
Posts: 16
Topics: 3
|
| Posted: Thu Mar 08, 2007 1:08 pm Post subject: |
|
|
| oic.. now, it runs but when i call the stored proc it produces an error: A value cannot be assigned to a host variable in the SELECT, VALUES, or FETCH statement because the data types are not compatible. |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
| Posted: Thu Mar 08, 2007 1:14 pm Post subject: |
|
|
| wishlister wrote: | | oic.. now, it runs but when i call the stored proc it produces an error: A value cannot be assigned to a host variable in the SELECT, VALUES, or FETCH statement because the data types are not compatible. |
wishlister,
how is your target table defined? is it Integer or decimal? Based on the target table definitons use the function like CHAR or Int or DECIMAL
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
| Posted: Thu Mar 08, 2007 1:14 pm Post subject: |
|
|
did you insure that the data types for your host variables for rate,hrs,sal,and bonus are x type?
_________________
Dick Brenholtz
American living in Varel, Germany |
|
| Back to top |
|
|
wishlister
Beginner
Joined: 08 Mar 2007
Posts: 16
Topics: 3
|
| Posted: Thu Mar 08, 2007 1:24 pm Post subject: |
|
|
| what do u mean by x type? |
|
| Back to top |
|
|
wishlister
Beginner
Joined: 08 Mar 2007
Posts: 16
Topics: 3
|
| Posted: Thu Mar 08, 2007 1:40 pm Post subject: |
|
|
| kolusu wrote: | | wishlister wrote: | | oic.. now, it runs but when i call the stored proc it produces an error: A value cannot be assigned to a host variable in the SELECT, VALUES, or FETCH statement because the data types are not compatible. |
wishlister,
how is your target table defined? is it Integer or decimal? Based on the target table definitons use the function like CHAR or Int or DECIMAL
Kolusu |
just want to remind you that sal & bonus will not be placed under the column rate and hrs. they are another column, meaning if the employee is under MIS, his rate & hrs columns will be blank. |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
| Posted: Thu Mar 08, 2007 1:47 pm Post subject: |
|
|
wishlister,
post the column defintions of all the tables involved and i will post the sql
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
