| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Using ESQL to select and insert multiple rows. |
« View previous topic :: View next topic » |
| Author |
Message
|
| Fresher_Newbie |
 Posted: Wed Jun 24, 2015 3:24 am Post subject: Using ESQL to select and insert multiple rows. |
 |
|
Novice
Joined: 24 Jun 2015
Posts: 13
|
Hello folks,
I need to select multiple rows from one table and insert the selected rows into another table in the same database. I have the Orcacle/SQL query for that, and is as follows:
| Code: |
INSERT INTO "ESB"."REQ_COUNT" (MESSAGE_FLOW, NO_OF_REQUESTS, START_TIME, END_TIME)
SELECT message_flow, count(*) as No_of_Requests, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP + interval '15' minute
FROM "ESB"."MESSAGE_LOG"
WHERE log_point = 'ClientRequest' AND process_timestamp BETWEEN CURRENT_TIMESTAMP AND CURRENT_TIMESTAMP + interval '15' minute
GROUP BY message_flow; |
"ESB" is the schema name.
"REQ_COUNT" and "MESSAGE_LOG" are the table names.
I'm really new to WMB/IIB and not adept with ESQL. Could any of please help me translate this SQL query into its equivalent ESQL code? Thanks!
P.S: I did try a lot, but didn't really come up with a syntactically right ESQL code. I'm finding this a bit complex at this stage.  |
|
| Back to top |
|
 |
| nelson |
| Posted: Wed Jun 24, 2015 4:11 am Post subject: |
|
|
Partisan
Joined: 02 Oct 2012
Posts: 313
|
|
| Back to top |
|
|
| Fresher_Newbie |
| Posted: Wed Jun 24, 2015 10:59 pm Post subject: |
|
|
Novice
Joined: 24 Jun 2015
Posts: 13
|
Thanks Nelson, but since the query involves the usage of the count(*), and GROUP BY functions, the normal select and insert isn't ideal.
I explored the PASSTHRU function, but I'm just not able to get it's syntax right. I'll look further into it. Sucks to be a beginner. |
|
| Back to top |
|
|
| mgk |
| Posted: Thu Jun 25, 2015 1:46 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Unfortunately, the ESQL INSERT does not support a multi-row insert so you will have to insert each one individually. An alternative would be to call a stored procedure to do this INSERT instead. A PASSTHRU could work, but a SP will probably be better. Also, I'm not sure why you need the GROUP BY clause given that the target is another table and not a report...
Kind regards,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Thu Jun 25, 2015 4:24 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Not sure what kind of result you'd expect....
Shouldn't the where clause say
| Code: |
| process_timestamp between CURRENT_TIMESTAMP - INTERVAL 15 MINUTES and CURRENT_TIMESTAMP |
??
Don't know what you'd have recorded in your DB between now and the future... 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| Fresher_Newbie |
| Posted: Thu Jun 25, 2015 10:44 pm Post subject: |
|
|
Novice
Joined: 24 Jun 2015
Posts: 13
|
@fjb_saper - Oh yes, my bad. It should be the way you've mentioned it.
@mgk - Thanks, I'll look into doing it the way you suggested. |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
