| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Select from where like |
« View previous topic :: View next topic » |
| Author |
Message
|
| ACheeseman |
 Posted: Wed Sep 08, 2004 8:49 am Post subject: Select from where like |
 |
|
Novice
Joined: 25 Feb 2004
Posts: 19
Location: London UK
|
I am trying to do a select from a database table, but I want to filter the results I get back. I'd hoped to do it using a LIKE command, but have discovered there is no such thing in ESQL. Instead I tried to use passthru, with limited success.
I have an XML message such as:
<where>
<value></value>
<value>foo</value>
<value></value>
<value></value>
</where>
The idea is the filter should return only messages where the second column in the database contains the string 'foo'. My EQSL code is as follows:
SET lr_data.row[] = PASSTHRU
('SELECT'
code AS "value",
text AS "value",
severity AS "value",
long_text AS "value"
FROM error_code
WHERE code LIKE ?
AND text LIKE ?
AND severity LIKE ?
AND long_text LIKE?',
'%' || lr_request.where.value[1] || '%',
'%' || lr_request.where.value[2] || '%',
'%' || lr_request.where.value[3] || '%',
'%' || lr_request.where.value[4] || '%'
);
I hoped this would return a message something like:
<row>
<value>1004</value>
<value>football</value>
<value>1</value>
<value>Something else</value>
</row>
<row>
<value>1018</value>
<value>tofoo</value>
<value>3</value>
<value></value>
</row>
The problem is that it only returns the row where the final value (error_text here) is not null.
Is there anyway I can modify my method to return the rows with null values as well? Or is there another, much more elegant method I could employ instead?
Cheers
Alex |
|
| Back to top |
|
 |
| jefflowrey |
| Posted: Wed Sep 08, 2004 9:31 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
| WBIMB Info Center - Reference, ESQL, Operators wrote: |
Operator LIKE
The operator LIKE searches for strings that match a certain pattern.
Syntax diagram format: Railroad diagram Dotted decimal
LIKE operator
>>-operand_1--+-----+--LIKE--pattern---------------------------->
'-NOT-'
>--+---------------------------+-------------------------------><
'-ESCAPE ------operand_2 -'
The result is TRUE if none of the operands is NULL and the source operand (operand_1) matches the pattern operand. The result is FALSE if none of the operands is NULL and the source operand does not match the pattern operand. Otherwise the result is UNKNOWN.
The pattern is specified by a string in which the percent (%) and underscore (_) characters have a special meaning:
* The underscore character _ matches any single character.
For example, the following finds matches for IBM and for IGI, but not for International Business Machines or IBM Corp:
Body.Trade.Company LIKE 'I__'
* The percent character % matches a string of zero or more characters.
For example, the following finds matches for IBM, IGI, International Business Machines, and IBM Corp:
Body.Trade.Company LIKE 'I%'
To use the percent and underscore characters within the expressions that are to be matched, precede the characters with an ESCAPE character, which defaults to the backslash (\) character.
For example, the following predicate finds a match for IBM_Corp.
Body.Trade.Company LIKE 'IBM\_Corp'
You can specify a different escape character by using the ESCAPE clause. For example, you could also specify the previous example like this:
Body.Trade.Company LIKE 'IBM$_Corp' ESCAPE '$'
|
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| ACheeseman |
| Posted: Thu Sep 09, 2004 1:16 am Post subject: |
|
|
Novice
Joined: 25 Feb 2004
Posts: 19
Location: London UK
|
Thanks - I'll have to refine my help searching skills.
In the meantime the following code works:
-------------------------------------------------------------
SET lr_data.row[] = (
SELECT
COALESCE(DB.code,'') AS value[1],
COALESCE(DB.text,'') AS value[2],
COALESCE(DB.severity,'') AS value [3],
COALESCE(DB.long_text,'') AS value [4]
FROM
Database.error_code AS DB
WHERE
CAST (COALESCE(DB.code,'') AS CHAR) LIKE '%' || lr_request.where.value[1] || '%'
AND COALESCE(DB.text,'') LIKE '%' || lr_request.where.value[2] || '%'
AND COALESCE(DB.severity,'') LIKE '%' || lr_request.where.value[3] || '%'
AND COALESCE(DB.long_text,'') LIKE '%' || lr_request.where.value[4] || '%'
);
-------------------------------------------------------------
The reason for the CAST is because the code column in the database is an integer. |
|
| Back to top |
|
|
| jms_newbie |
| Posted: Fri Sep 10, 2004 7:14 am Post subject: |
|
|
Newbie
Joined: 09 Sep 2004
Posts: 3
|
so what actually is the difference between using PASSTHRU and SELECT when SELECTING something from the database  ?
when do we have to use PASSTHRU with SELECT and ONLY SELECT ? 
enlighten me ............ :  |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Fri Sep 10, 2004 7:16 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
| jms_newbie wrote: |
so what actually is the difference between using PASSTHRU and SELECT when SELECTING something from the database ?
when do we have to use PASSTHRU with SELECT and ONLY SELECT ?
enlighten me ............ : |
Use select whenever you possibly can.
Use PASSTHRU when you need to issue an SQL Select that uses options that ESQL Select doesn't support.
_________________
I am *not* the model of the modern major general. |
|
| 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
|
|
|
|
