| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| How Can I create Nested Select in ESQL |
« View previous topic :: View next topic » |
| Author |
Message
|
| se_zn2003 |
 Posted: Wed Sep 11, 2013 4:34 am Post subject: How Can I create Nested Select in ESQL |
 |
|
Apprentice
Joined: 07 May 2013
Posts: 30
|
i use this query for fetch data from Oracle Database and use it in Passthru Statement and successfully worked but I want to use this query in ESQL form by select statement
can you please help me??
| Code: |
SELECT * FROM (SELECT T.*,rownum AS rn FROM ATLANTICS2.TB_PM_STATUS T WHERE T.STATUS_NAME='Success')a WHERE a.rn>='1' and a.rn<='10';
|
|
|
| Back to top |
|
 |
| lancelotlinc |
| Posted: Wed Sep 11, 2013 4:39 am Post subject: Re: How Can I create Nested Select in ESQL |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
| se_zn2003 wrote: |
i use this query for fetch data from Oracle Database and use it in Passthru Statement and successfully worked but I want to use this query in ESQL form by select statement
can you please help me??
| Code: |
SELECT * FROM (SELECT T.*,rownum AS rn FROM ATLANTICS2.TB_PM_STATUS T WHERE T.STATUS_NAME='Success')a WHERE a.rn>='1' and a.rn<='10';
|
|
When you tried the working example sample. what happened ? You can substitute the database name for InputRoot spec.
http://publib.boulder.ibm.com/infocenter/wmbhelp/v8r0m0/index.jsp?topic=%2Fcom.ibm.etools.mft.doc%2Fak05750_.htm
| Code: |
SET OutputRoot.XMLNS.Data.Statement[] =
(SELECT I.Customer.Title AS Customer.Title,
I.Customer.FirstName || ' ' || I.Customer.LastName AS Customer.Name,
COALESCE(I.Customer.PhoneHome,'') AS Customer.Phone,
(SELECT II.Title AS Desc,
CAST(II.UnitPrice AS FLOAT) * 1.6 AS Cost,
II.Quantity AS Qty
FROM I.Purchases.Item[] AS II
WHERE II.UnitPrice> 0.0 ) AS Purchases.Article[],
(SELECT SUM( CAST(II.UnitPrice AS FLOAT) *
CAST(II.Quantity AS FLOAT) *
1.6 )
FROM I.Purchases.Item[] AS II ) AS Amount,
'Dollars' AS Amount.(XML.Attribute)Currency
FROM InputRoot.XMLNS.Invoice[] AS I
|
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| se_zn2003 |
| Posted: Wed Sep 11, 2013 4:58 am Post subject: Re: How Can I create Nested Select in ESQL |
|
|
Apprentice
Joined: 07 May 2013
Posts: 30
|
| lancelotlinc wrote: |
| se_zn2003 wrote: |
i use this query for fetch data from Oracle Database and use it in Passthru Statement and successfully worked but I want to use this query in ESQL form by select statement
can you please help me??
| Code: |
SELECT * FROM (SELECT T.*,rownum AS rn FROM ATLANTICS2.TB_PM_STATUS T WHERE T.STATUS_NAME='Success')a WHERE a.rn>='1' and a.rn<='10';
|
|
When you tried the working example sample. what happened ? You can substitute the database name for InputRoot spec.
http://publib.boulder.ibm.com/infocenter/wmbhelp/v8r0m0/index.jsp?topic=%2Fcom.ibm.etools.mft.doc%2Fak05750_.htm
| Code: |
SET OutputRoot.XMLNS.Data.Statement[] =
(SELECT I.Customer.Title AS Customer.Title,
I.Customer.FirstName || ' ' || I.Customer.LastName AS Customer.Name,
COALESCE(I.Customer.PhoneHome,'') AS Customer.Phone,
(SELECT II.Title AS Desc,
CAST(II.UnitPrice AS FLOAT) * 1.6 AS Cost,
II.Quantity AS Qty
FROM I.Purchases.Item[] AS II
WHERE II.UnitPrice> 0.0 ) AS Purchases.Article[],
(SELECT SUM( CAST(II.UnitPrice AS FLOAT) *
CAST(II.Quantity AS FLOAT) *
1.6 )
FROM I.Purchases.Item[] AS II ) AS Amount,
'Dollars' AS Amount.(XML.Attribute)Currency
FROM InputRoot.XMLNS.Invoice[] AS I
|
|
I checked this page
but by this query I can use for a table and Can't use this sample in dynamic select function.
because I have more than 30 search query in application and if use this sample I should have create a lot of functions!
what do you suggest? |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Wed Sep 11, 2013 5:01 am Post subject: Re: How Can I create Nested Select in ESQL |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
| se_zn2003 wrote: |
| lancelotlinc wrote: |
| se_zn2003 wrote: |
i use this query for fetch data from Oracle Database and use it in Passthru Statement and successfully worked but I want to use this query in ESQL form by select statement
can you please help me??
| Code: |
SELECT * FROM (SELECT T.*,rownum AS rn FROM ATLANTICS2.TB_PM_STATUS T WHERE T.STATUS_NAME='Success')a WHERE a.rn>='1' and a.rn<='10';
|
|
When you tried the working example sample. what happened ? You can substitute the database name for InputRoot spec.
http://publib.boulder.ibm.com/infocenter/wmbhelp/v8r0m0/index.jsp?topic=%2Fcom.ibm.etools.mft.doc%2Fak05750_.htm
| Code: |
SET OutputRoot.XMLNS.Data.Statement[] =
(SELECT I.Customer.Title AS Customer.Title,
I.Customer.FirstName || ' ' || I.Customer.LastName AS Customer.Name,
COALESCE(I.Customer.PhoneHome,'') AS Customer.Phone,
(SELECT II.Title AS Desc,
CAST(II.UnitPrice AS FLOAT) * 1.6 AS Cost,
II.Quantity AS Qty
FROM I.Purchases.Item[] AS II
WHERE II.UnitPrice> 0.0 ) AS Purchases.Article[],
(SELECT SUM( CAST(II.UnitPrice AS FLOAT) *
CAST(II.Quantity AS FLOAT) *
1.6 )
FROM I.Purchases.Item[] AS II ) AS Amount,
'Dollars' AS Amount.(XML.Attribute)Currency
FROM InputRoot.XMLNS.Invoice[] AS I
|
|
I checked this page
but by this query I can use for a table and Can't use this sample in dynamic select function.
because I have more than 30 search query in application and if use this sample I should have create a lot of functions!
what do you suggest? |
Your argument makes no sense. Are you suggesting that it is impossible to specify more than 30 expressions on the WHERE clause ?
Did you try it ?
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Sep 11, 2013 6:28 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
You have to understand the difference between an SQL Select statement and an ESQL Select statement.
Then you can figure out whether you can execute the same SQL Select statement as an ESQL Select statement or not. And it will show you how to transform an SQL Select into an ESQL Select, or how to use PASSTHRU.
All you have to do is read, think, try, and then repeat. |
|
| 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
|
|
|
|
