| Author |
Message
|
| EnOne |
 Posted: Thu Apr 10, 2008 12:49 pm Post subject: SELECT FROM INSERT with DB2 version 8 |
 |
|
Centurion
Joined: 09 Oct 2002
Posts: 101
Location: Kansas City
|
There is new Functionality within DB2 v8 that allows you to do a select with an insert. I am trying to get it to work using PASSTHRU with no luck.
| Code: |
| SELECT TABLE_ID FROM INSERT INTO Database.GBL.MYTABLE(ROW1, ROW2, ROW3) VALUES ( 456, 56, 6 ); |
where the table before the insert would be
MYTABLE
TABLE_ID ROW1 ROW2 ROW3
1 123 23 3
2 234 34 4
3 345 45 5
the value returned should be that TABLE_ID is 4 since the first row automatically increments.
i read about this functionality from the "What's New?"
http://publib.boulder.ibm.com/epubs/pdf/dsnwnj11.pdf
I do not get any warning or error from my SQL statement |
|
| Back to top |
|
 |
| EddieA |
| Posted: Thu Apr 10, 2008 4:14 pm Post subject: |
|
|
Jedi
Joined: 28 Jun 2001
Posts: 2453
Location: Los Angeles
|
Please post the actual ESQL statement used.
Cheers,
_________________
Eddie Atherton
IBM Certified Solution Developer - WebSphere Message Broker V6.1
IBM Certified Solution Developer - WebSphere Message Broker V7.0 |
|
| Back to top |
|
|
| EnOne |
| Posted: Fri Apr 11, 2008 4:58 am Post subject: |
|
|
Centurion
Joined: 09 Oct 2002
Posts: 101
Location: Kansas City
|
| the actual SQL statement has 20 or more columns in it so I simplified for clarity. |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Fri Apr 11, 2008 5:00 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
| EnOne wrote: |
| the actual SQL statement has 20 or more columns in it so I simplified for clarity. |
Please note that he said ESQL statement.
Also, please explain what you EXPECT to be happening, that isn't, that is causing you to say "it doesn't work".
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| EnOne |
| Posted: Fri Apr 11, 2008 6:03 am Post subject: |
|
|
Centurion
Joined: 09 Oct 2002
Posts: 101
Location: Kansas City
|
the ESQL would look like the following
| Code: |
DECLARE SQLStmt CHAR;
SET SQLStmt = 'SELECT TABLE_ID FROM INSERT INTO Database.GBL.MYTABLE(ROW1, ROW2, ROW3) VALUES ( 456, 56, 6 )';
SET Environment.XML.MYTABLE[] = PASSTHRU (SQLStmt);
|
what I would expect as a result would be
| Code: |
| Environment.XML.MYTABLE.TABLE_ID = 4 |
from
| Code: |
TABLE_ID ROW1 ROW2 ROW3
1 123 23 3
2 234 34 4
3 345 45 5
|
the response is
| Code: |
| Environment.XML.MYTABLE = |
|
|
| Back to top |
|
|
| jefflowrey |
| Posted: Fri Apr 11, 2008 6:05 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
What happens when you run the statement in plain DB2 - I mean outside of Broker?
What does the db2 log show when you execute your flow?
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
|
|
