| Author |
Message
|
| aravind703 |
 Posted: Mon Sep 26, 2011 8:22 am Post subject: Select records from Database and List them in ASC or DESC |
 |
|
Apprentice
Joined: 24 Sep 2011
Posts: 29
|
Hi
I am retrieving a column from a database table. My select statement may result in more than one record. I want to pass SINGLE ESQL query and find the lowest of the all the matching records from the database.
I know that ESQL does not SUPPORT ORDER BY. Currently I am storing the matching records of the DB in ENVIRONMENTAL Variable Array. Using trace node , i found that the the Records are stored in Ascending order. So i am picking up the first element of the ENVIRONMENTAL Variable Array Blindly. I would like to know if the easiest way for this
Here is the Query which results in Multiple records
DECLARE InputData REFERENCE TO InputRoot.XMLNSC.----------;
DECLARE num INTEGER;
SET OutputRoot.XML.Message.Lowest = THE( SELECT ITEM T.item_no from Database.schema.table AS T where T.seq_no =InputData.seq AND T.amount=InputData.Amount)
The above select statement will result in multiple. I just phrased it. It may not be working. But this is how i want to find the lowest number of the matching records  |
|
| Back to top |
|
 |
| mqjeff |
| Posted: Mon Sep 26, 2011 8:30 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Other than a certain neatness of code, what is the value you derive from achieving this in a single ESQL SELECT statement?
As you have noted, ESQL Select does not support ORDER BY.
You can expend time (not much) finding the posts that have a QUICKSORT in ESQL that you could use, such that you would know you have found the lowest one when you look at the first one.
You can write code to walk through a reference to each element and compare it to the lowest currently found value, and save a reference if so.
You can ask your database to handle this for you - if you are doing SELECTs against a real database - using PASSTHRU. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Mon Sep 26, 2011 8:37 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
Or
Let the DB do it all for you inside a stored procedure. Then you can control things like page and table locking plus transactionality.
Little things like that (locking) can really affect flow performance especially if you have multiple instances of the flow running.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| aravind703 |
| Posted: Mon Sep 26, 2011 8:41 am Post subject: Hi mqjeff |
|
|
Apprentice
Joined: 24 Sep 2011
Posts: 29
|
I did try the PASSTHRU
SET OutputRoot.XML.Message.Number= PASSTHRU ('SELECT item_no from schema.table where amount='||amount );
I was not sure how to include the second column and its matching value along with order by. I came across the QUICK SORT but was thinking to make this short. Usually the number of records can be counted on finger tips. So dont want to use the QUICK SORT. Was not sure how to include the ORDER BY in the above statement. i tried to do the MIN of the COLUMN.
If I wanna try this in a SINGLE ESQL how can i do it |
|
| Back to top |
|
|
| mqjeff |
| Posted: Mon Sep 26, 2011 8:43 am Post subject: Re: Hi mqjeff |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| aravind703 wrote: |
| If I wanna try this in a SINGLE ESQL how can i do it |
I did not say that it could be done.
I asked what value you derive from doing so?
It is likely somewhat possible to craft a set of nested ESQL selects, perhaps making use of some of the clauses that ESQL Select does support. |
|
| Back to top |
|
|
| aravind703 |
| Posted: Mon Sep 26, 2011 8:50 am Post subject: Hi mqjeff |
|
|
Apprentice
Joined: 24 Sep 2011
Posts: 29
|
|
| Back to top |
|
|
| mqjeff |
| Posted: Mon Sep 26, 2011 9:40 am Post subject: Re: Hi mqjeff |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| aravind703 wrote: |
| It is an integer value |
Yes, okay. I do not mean "value" in that sense.
Allow me to be more direct.
Why does it help you to use a single ESQL statement? If ten statements can say the same thing as one, and the ten statements are clearer in purpose than the one, which is better?
Why do you *want* to use *one* esql statement, rather than several? |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Mon Sep 26, 2011 9:55 am Post subject: Re: Hi mqjeff |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
| mqjeff wrote: |
Why do you *want* to use *one* esql statement, rather than several? |
Less is more?
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Mon Sep 26, 2011 9:59 am Post subject: Re: Hi mqjeff |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| smdavies99 wrote: |
| mqjeff wrote: |
Why do you *want* to use *one* esql statement, rather than several? |
Less is more? |
This does not apply to paychecks. Should it apply to source code? |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Mon Sep 26, 2011 10:16 am Post subject: Re: Hi mqjeff |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
| mqjeff wrote: |
| smdavies99 wrote: |
| mqjeff wrote: |
Why do you *want* to use *one* esql statement, rather than several? |
Less is more? |
This does not apply to paychecks. Should it apply to source code? |
At one time, in the 1980s, Microsoft awarded raises based on KLOCs, or thousand lines of code. The more KLOCs you turned in, the bigger bonus you got.
More is more, in source code terms.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| mqjeff |
| Posted: Mon Sep 26, 2011 10:26 am Post subject: Re: Hi mqjeff |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| lancelotlinc wrote: |
| mqjeff wrote: |
| smdavies99 wrote: |
| mqjeff wrote: |
Why do you *want* to use *one* esql statement, rather than several? |
Less is more? |
This does not apply to paychecks. Should it apply to source code? |
At one time, in the 1980s, Microsoft awarded raises based on KLOCs, or thousand lines of code. The more KLOCs you turned in, the bigger bonus you got.
More is more, in source code terms. |
Is more better or worse than less, in source code terms?
I would argue that clearer is better. |
|
| Back to top |
|
|
|
|
