| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| SELECT with THE clause |
« View previous topic :: View next topic » |
| Author |
Message
|
| ediman |
 Posted: Tue Jun 30, 2015 12:29 am Post subject: SELECT with THE clause |
 |
|
Newbie
Joined: 05 Jun 2013
Posts: 9
|
Hi guys,
we use WMB 8 and in our ESQL scripts we have many SQL statement with THE clause, i.e.
| Code: |
SET variable = THE (SELECT ITEM field1 FROM table1 WHERE <where condition>)
|
The <where condition> returns more than one value and with the THE clause we can assign to variabile only the first value.
Well, what we are asking for is which is the criteria, if exists, to get the "first" value. I mean, sometimes we get one value, sometimes we get another value. The customer give us only the where clause and expects always the same value, but I think this is impossible. What can we tell to him to explian the behavior of the toolkit ?
Best regards. |
|
| Back to top |
|
 |
| maurito |
| Posted: Tue Jun 30, 2015 1:43 am Post subject: |
|
|
Partisan
Joined: 17 Apr 2014
Posts: 358
|
First of all, it is not a behaviour of the Toolkit but the way ESQL (and SQL ) have been implemented . The select statement will return an unordered array of elements, and the first one will be picked, so no guarantee that it will be always the same.
You can use a PASSTHRU to the database with the ORDER BY clause, that will probably work, but again, it is not guaranteed that if another element that meets the WHERE clause is added, the result will be the same, if the order by happens to place the new element first ( or last, depending on whether you sort ASC or DSC ) in the array.
You could also try combining the SELCT with the column functions MIN or MAX, to obtain a unique value. |
|
| Back to top |
|
|
| ediman |
| Posted: Tue Jun 30, 2015 2:05 am Post subject: |
|
|
Newbie
Joined: 05 Jun 2013
Posts: 9
|
Hi Maurito,
thank you for your answer an suggestions.
It's hard to explain to customer that the broker doesn't have an artificial intelligence to match its wishes 
Best regards. |
|
| Back to top |
|
|
| maurito |
| Posted: Tue Jun 30, 2015 2:45 am Post subject: |
|
|
Partisan
Joined: 17 Apr 2014
Posts: 358
|
| ediman wrote: |
Hi Maurito,
thank you for your answer an suggestions.
It's hard to explain to customer that the broker doesn't have an artificial intelligence to match its wishes
Best regards. |
well, that depends on how specific is the WHERE clause.
if you do a SELECT * from Database, then you get a result.
SELECT * from Database where surname='smith' you get another result.
SELECT * from Database where surname='smith' AND name = 'john' , another, etc.
so the customer needs to decide exactly what he/she wants. The broker cannot make that decision for them. |
|
| Back to top |
|
|
| inMo |
| Posted: Tue Jun 30, 2015 5:41 am Post subject: |
|
|
Master
Joined: 27 Jun 2009
Posts: 216
Location: NY
|
| The question is where is the logic that ensures only a desirable single result is captured from the DB? Using THE doesn't guarantee a single desirable result, just a single result. The code to return a single desirable result can reside in more complex SQL that is passed to the DB for execution, or that logic can reside within the broker after n results are returned from the simple DB call. |
|
| 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
|
|
|
|
