| Author |
Message
|
| Inforz |
 Posted: Mon Oct 03, 2011 3:03 am Post subject: ESQL Question about SELECT query |
 |
|
Centurion
Joined: 15 Apr 2011
Posts: 139
Location: Chennai, India
|
Hi,
In ESQL,
How can I limit the result of SELECT query to fetch only one row that matches the where clause while fetching results.
Note: THE function discards all the other results except the first one which happens after the SELECT function selects all matched records. But I need the SELECT function to stop querying the remaining records while it has fetched the first matching record.
Thanks, |
|
| Back to top |
|
 |
| smdavies99 |
| Posted: Mon Oct 03, 2011 3:32 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
I'd do it in a stored procedure in the DB itself
The different db's have different syntaxes for this sort of stuff
for SQL-Server(from memory)
select top 5 * from mytable where ....;
Oracle
select name, price
from (
select name, price, row_number() over (order by price) r
from items
)
where r between 1 and 5;
DB2
select col1,col2,....
from tab1
where col1 = ?
fetch first 5 rows only
IMHO, it is a lot easier to code this in a stored proc. This sort of functionality in not found in the ESQL Select statement.
_________________
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 |
|
|
| Inforz |
| Posted: Mon Oct 03, 2011 4:09 am Post subject: |
|
|
Centurion
Joined: 15 Apr 2011
Posts: 139
Location: Chennai, India
|
Thanks for the suggestion.
I actually load the (property)table contents to the memory just once at the start of the flow into Environment variable(ROW datatype) and then onwards I would parse the variable.
So during this operation, I face this difficulty..  |
|
| Back to top |
|
|
| MrSmith |
| Posted: Wed Oct 05, 2011 5:49 am Post subject: |
|
|
Master
Joined: 20 Mar 2008
Posts: 215
|
If your loading this table into a ROW and then doing the SELECT on that ROW (if I have understood correctly) then you can only ever be certain of retrieving one record by either having a more refined WHERE clause so that only one record returns or as suggested the THE function but which will only return the first that matches the criteria........ or as smdavies99 suggested by doing this in a DB procedure, which while you have the overhead of the DB procedural call you have a simialr overhead as the ROW select will search all the ROWs anyway to match the WHERE clause.
The DB procedure does give you more flexibility in that you can at least make that procedure invoke an indexed and even viewed table, to retrieve selected columns and its managed by the DB rather than having to check for changes in your ROW load or restarting the flow for changes
You also can then have the option of a number of records fetch and thus regaining your performance advantage that you have from your ROW being in memory.
It also decouples Broker from a particular DB software as long as you keep the procedure name call the same etc. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Oct 05, 2011 5:53 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Can you add an index value to the property rows - that is an increasing count for each property value that has the same name?
then you could add this into your WHERE clause, such that you could match NAME='abc' and INDEX='3' to get the third abc value. |
|
| Back to top |
|
|
| marko.pitkanen |
| Posted: Wed Oct 05, 2011 10:18 pm Post subject: |
|
|
Chevalier
Joined: 23 Jul 2008
Posts: 440
Location: Jamsa, Finland
|
Hi,
If you like the idea of removing online dependency of database you perhaps like to fetch config data to SHARED ROW cache. Use your own loops with references to find correct data instead of SELECT clauses over internal data trees so you get more control how the procedure works.
--
Marko
Example pseudo code below:
| Code: |
declare srRT SHARED ROW;
.
.
.
SET OutputLocalEnvironment.Destination.MQ.DestinationData.queueName = getQN(cSP1, cSP2);
.
.
.
CREATE FUNCTION getQN(IN cSP1 CHAR, IN cSP2 CHAR) RETURNS CHAR
BEGIN
DECLARE cResult CHAR '';
DECLARE rTable REFERENCE TO srRT.Table;
DECLARE rRow REFERENCE TO rTable.Row;
X:WHILE LASTMOVE(rRow) DO
IF (FIELDVALUE(rRow.SP1) = cSP1)
AND (FIELDVALUE(rRow.SP2) = cSP2)
THEN
SET cResult = COALESCE(FIELDVALUE(rRow.Value),cResult);
LEAVE X;
END IF;
MOVE rRow NEXTSIBLING REPEAT TYPE NAME;
END WHILE;
IF cResult = '' THEN
THROW USER EXCEPTION MESSAGE 3001 VALUES('No QN Found');
END IF;
RETURN cResult;
END;
|
|
|
| Back to top |
|
|
|
|
