| Author |
Message
|
| wmb61_user |
 Posted: Wed Jul 09, 2008 11:21 am Post subject: Help with ESQL in compute node. |
 |
|
Novice
Joined: 08 May 2008
Posts: 18
|
I have two SQLs going after a the same set of tables using the PASSTHRU funciton. One to get the header level information and one to get the line level information. I did it this way as I only need one instance of header information in my entire document.
I am sure there is a better way of doing this instead of using two SQLs which is affecting the performance. How do I do this?.
Ex:
I have
SELECT ORD_NBR FROM ORD_HDR;
SELECT ITM_NBR FROM ORD_LINE;
My output looks like this.
<ORD_NBR ABC </ORD_NBR>
< ITEM_NBR 123 </ITM_NBR>
< ITEM_NBR 456 </ITM_NBR>
Instead I would like use
SELECT ORD_NBR ,ITM_NBR FROM ORD_HDR A,ORD_LINE B
WHERE A.ORD_NBR = B.ORD_NBR;
How do I make my output look the same as above. |
|
| Back to top |
|
 |
| sridhsri |
| Posted: Wed Jul 09, 2008 12:46 pm Post subject: |
|
|
Master
Joined: 19 Jun 2008
Posts: 297
|
| Can you please tell me why you decided to use the PASSTHRU function ? You could write those two SQL statements in ESQL. that does give better performance than PASSTHRU. |
|
| Back to top |
|
|
| wmb61_user |
| Posted: Wed Jul 09, 2008 1:28 pm Post subject: |
|
|
Novice
Joined: 08 May 2008
Posts: 18
|
| The bottom line is that it is not efficient to do to SQL calls when you can get away with one. |
|
| Back to top |
|
|
| AkankshA |
| Posted: Wed Jul 09, 2008 8:49 pm Post subject: |
|
|
Grand Master
Joined: 12 Jan 2006
Posts: 1494
Location: Singapore
|
| Quote: |
SELECT ORD_NBR ,ITM_NBR FROM ORD_HDR A,ORD_LINE B
WHERE A.ORD_NBR = B.ORD_NBR; |
when you use this query with passthru.... how does your output look like ??
_________________
Cheers |
|
| Back to top |
|
|
| Maximreality |
| Posted: Thu Jul 10, 2008 12:56 am Post subject: Re: Help with ESQL in compute node. |
|
|
Acolyte
Joined: 04 Jun 2004
Posts: 65
Location: Copenhagen
|
If you are worried about performance you should at least make your PASSTHRU SQL statements as prepared statements....
If you use ESQL the broker automatically generates prepared SQL statements.
In ESQL your SQL statement would look like this:
| Code: |
| SET OutputRoot.XML.Orders.Result[] = (SELECT A.ORD_NBR, B.ITEM_NBR FROM Database.ORD_HDR AS A, Database.ORD_LINE AS B WHERE A.ORD_NBR = B.ORD_NBR); |
If you want your output presented nicely you need to make a more advanced statement...., take a look in the ESQL reference quide from IBM there should be some examples of joining data form two tables and formatting it in XML. |
|
| Back to top |
|
|
| wmb61_user |
| Posted: Thu Jul 10, 2008 7:24 am Post subject: |
|
|
Novice
Joined: 08 May 2008
Posts: 18
|
Akankhsa,
The output looks like this.
<ORD_NBR ABC </ORD_NBR>
< ITEM_NBR 123 </ITM_NBR>
<ORD_NBR ABC </ORD_NBR>
< ITEM_NBR 456 </ITM_NBR> |
|
| Back to top |
|
|
| sridhsri |
| Posted: Thu Jul 10, 2008 8:27 pm Post subject: |
|
|
Master
Joined: 19 Jun 2008
Posts: 297
|
| If you could write a stored procedure perhaps you could construct a result set (or even two result sets) to give you the desired output. You could then invoke the stored procedure from esql. |
|
| Back to top |
|
|
| AkankshA |
| Posted: Thu Jul 10, 2008 9:42 pm Post subject: |
|
|
Grand Master
Joined: 12 Jan 2006
Posts: 1494
Location: Singapore
|
One way to acheve this could be .. Do not store the result in output tree directly.... get the result from DB in environment tree and then build output tree as per your requirement....
_________________
Cheers |
|
| Back to top |
|
|
|
|
