Author |
Message
|
umaneesh |
Posted: Wed May 06, 2009 1:02 am Post subject: ESQL function for word analyser |
|
|
Novice
Joined: 13 Apr 2009 Posts: 24
|
Hi,
I have a requirement something like this:
Input MSG:
'THIS IS MY FIRST LINE SECOND LINE STARTS HERE'
Rules for output:
First line should have length 12
Rest will be carried over to second line
If First line ends with a truncated word (like 'F' instead of 'FIRST'), move the whole word('FIRST') to second line.
I know, we can go with procedures to do this.
Is there any ESQL functions available to understand words using spaces or in some other way???
Thanks
Maneesh |
|
Back to top |
|
 |
WMBDEV1 |
Posted: Wed May 06, 2009 1:20 am Post subject: |
|
|
Sentinel
Joined: 05 Mar 2009 Posts: 888 Location: UK
|
Have a look at using the position and substring keywords.
It should be easy enough to do. |
|
Back to top |
|
 |
Vitor |
Posted: Wed May 06, 2009 1:23 am Post subject: Re: ESQL function for word analyser |
|
|
 Grand High Poobah
Joined: 11 Nov 2005 Posts: 26093 Location: Texas, USA
|
umaneesh wrote: |
Is there any ESQL functions available to understand words using spaces or in some other way???
|
Something like the C function strtok? Not that I'm aware of. _________________ Honesty is the best policy.
Insanity is the best defence. |
|
Back to top |
|
 |
umaneesh |
Posted: Wed May 06, 2009 1:33 am Post subject: Re: ESQL function for word analyser |
|
|
Novice
Joined: 13 Apr 2009 Posts: 24
|
Vitor wrote: |
umaneesh wrote: |
Is there any ESQL functions available to understand words using spaces or in some other way???
|
Something like the C function strtok? Not that I'm aware of. |
Similar to that. I am trying to avoid lengthy ESQL substring/position's as this got multiple lines involved.
Thanks
Maneesh |
|
Back to top |
|
 |
fjb_saper |
Posted: Wed May 06, 2009 2:24 am Post subject: |
|
|
 Grand High Poobah
Joined: 18 Nov 2003 Posts: 20756 Location: LI,NY
|
Have you thought about using a java procedure with a StringTokenizer for that?  _________________ MQ & Broker admin
Last edited by fjb_saper on Fri May 08, 2009 2:43 am; edited 1 time in total |
|
Back to top |
|
 |
zpat |
Posted: Wed May 06, 2009 2:30 am Post subject: |
|
|
 Jedi Council
Joined: 19 May 2001 Posts: 5866 Location: UK
|
ENDSWITH might help.
ESQL seems to lack a SUBWORD function such as found in REXX.
It would be possible to code your own SUBWORD function using the lower level functions. |
|
Back to top |
|
 |
Vitor |
Posted: Wed May 06, 2009 2:31 am Post subject: |
|
|
 Grand High Poobah
Joined: 11 Nov 2005 Posts: 26093 Location: Texas, USA
|
fjb_saper wrote: |
Have you thought about using a java procedure with a StringTokenized for that?  |
umaneesh wrote: |
Is there any ESQL functions available |
Maybe there's no Java resource available?
(Java. Bah humbug.) _________________ Honesty is the best policy.
Insanity is the best defence. |
|
Back to top |
|
 |
mqjeff |
Posted: Wed May 06, 2009 2:50 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008 Posts: 17447
|
If you're trying to avoid using "lengthy ESQL substring/position's " (which doesn't make a lot of sense) you can OF COURSE use a simple MRM model to give you a message tree that has every single word as a separated element. Use the CSV sample, change the comma to a space, and you should be most of the way there.
Then you'll get a logical structure of words in each line, and can figure out if you need to move a word from one line to the next. For that, you'll have to do some looping over LENGTH, but hey it's still not "lengthy ESQL substring/position's ". |
|
Back to top |
|
 |
Vitor |
Posted: Wed May 06, 2009 2:55 am Post subject: |
|
|
 Grand High Poobah
Joined: 11 Nov 2005 Posts: 26093 Location: Texas, USA
|
mqjeff wrote: |
If you're trying to avoid using "lengthy ESQL substring/position's " (which doesn't make a lot of sense) you can OF COURSE use a simple MRM model to give you a message tree that has every single word as a separated element. Use the CSV sample, change the comma to a space, and you should be most of the way there. |
Ingenious!  _________________ Honesty is the best policy.
Insanity is the best defence. |
|
Back to top |
|
 |
zpat |
Posted: Fri May 08, 2009 12:39 am Post subject: |
|
|
 Jedi Council
Joined: 19 May 2001 Posts: 5866 Location: UK
|
RTM - The ESQL manual has what you want in it. No need to resort to Java. (Keep it Simple!)
The following function example extracts a particular subfield of a message that is delimited by a specific character.
Code: |
CREATE FUNCTION SelectSubField
(SourceString CHAR, Delimiter CHAR, TargetStringPosition INT)
RETURNS CHAR
-- This function returns a substring at parameter position TargetStringPosition within the
-- passed parameter SourceString. An example of use might be:
-- SelectSubField(MySourceField,' ',2) which will select the second subfield from the
-- field MySourceField delimited by a blank. If MySourceField has the value
-- "First Second Third" the function will return the value "Second"
BEGIN
DECLARE DelimiterPosition INT;
DECLARE CurrentFieldPosition INT 1;
DECLARE StartNewString INT 1;
DECLARE WorkingSource CHAR SourceString;
SET DelimiterPosition = POSITION(Delimiter IN SourceString);
WHILE CurrentFieldPosition < TargetStringPosition
DO
IF DelimiterPosition = 0 THEN
-- DelimiterPosition will be 0 if the delimiter is not found
-- exit the loop
SET CurrentFieldPosition = TargetStringPosition;
ELSE
SET StartNewString = DelimiterPosition + 1;
SET WorkingSource = SUBSTRING(WorkingSource FROM StartNewString);
SET DelimiterPosition = POSITION(Delimiter IN WorkingSource);
SET CurrentFieldPosition = CurrentFieldPosition + 1;
END IF;
END WHILE;
IF DelimiterPosition> 0 THEN
-- Remove anything following the delimiter from the string
SET WorkingSource = SUBSTRING(WorkingSource FROM 1 FOR DelimiterPosition);
SET WorkingSource = TRIM(TRAILING Delimiter FROM WorkingSource);
END IF;
RETURN WorkingSource;
END; |
|
|
Back to top |
|
 |
umaneesh |
Posted: Fri May 08, 2009 1:19 am Post subject: |
|
|
Novice
Joined: 13 Apr 2009 Posts: 24
|
zpat wrote: |
RTM - The ESQL manual has what you want in it. No need to resort to Java. (Keep it Simple!)
|
Thanks for the suggestion.
I wrote a similar ESQL function to handle this, specific to my requirement. It is working...
mqjeff wrote: |
Use the CSV sample, change the comma to a space, and you should be most of the way there.
|
I cannot go with CSV as I am working in V5.1 broker.
Vitor wrote: |
Maybe there's no Java resource available? |
Instead of building a V5 custom node [in java], I found writing an ESQL function bit easier. I feel, custom node will impact the performance than an ESQL function. Not Sure! |
|
Back to top |
|
 |
WMBDEV1 |
Posted: Fri May 08, 2009 1:23 am Post subject: |
|
|
Sentinel
Joined: 05 Mar 2009 Posts: 888 Location: UK
|
umaneesh wrote: |
mqjeff wrote: |
Use the CSV sample, change the comma to a space, and you should be most of the way there.
|
I cannot go with CSV as I am working in V5.1 broker.
|
Jeff was not suggesting you change the format, but use the existing CSV sample and change the delimeter to be a space making it parse a SSV file which is what you have.
What ESQL solution did you create? Are you able to elude as to how you did it / post the solution in here to benefit others? |
|
Back to top |
|
 |
umaneesh |
Posted: Fri May 08, 2009 1:57 am Post subject: |
|
|
Novice
Joined: 13 Apr 2009 Posts: 24
|
[quote="WMBDEV1"]
umaneesh wrote: |
Jeff was not suggesting you change the format, but use the existing CSV sample and change the delimeter to be a space making it parse a SSV file which is what you have.
What ESQL solution did you create? Are you able to elude as to how you did it / post the solution in here to benefit others? |
Yeah Sure!
A sample:
This is how the output was,
//EAGATO A/VWEST1 WEST2 WEST3 WEST
4 WEST5 WEST6 WEST7 WEST8 WEST9WES
T10 WEST11 WEST12 WEST13 WEST14 WE
ST15 WEST16 WEST17
/VCD/EAIME0123457233123
Now, after writing the function,
//EAGATO A/VWEST1 WEST2 WEST3
WEST4 WEST5 WEST6 WEST7 WEST8
WEST9WEST10 WEST11 WEST12 WEST13
WEST14 WEST15 WEST16 WEST17
/VCD/EAI ME 0123457233123
My solution:
It is straight forward... ended up using substring/position only...
1. Position row's last space if present (using REPEAT -1)
2. Extract string from space+1
3. store reduced string count in a variable (I,J - for looping/array)
4. fix the extracted string in nxt row
5. change nxt rows startPos with the use of I,J (Row has fixed length)
6. Loop it for all the rows.
now, tuning it for a optimised function, which cd be used for generic case.
Expecting suggestions on this! |
|
Back to top |
|
 |
fjb_saper |
Posted: Fri May 08, 2009 2:47 am Post subject: |
|
|
 Grand High Poobah
Joined: 18 Nov 2003 Posts: 20756 Location: LI,NY
|
Quote: |
Instead of building a V5 custom node [in java], I found writing an ESQL function bit easier. I feel, custom node will impact the performance than an ESQL function. Not Sure! |
No need for a custom node... a procedure in Java would have done it...
But now that you have the ESQL way use it!
 _________________ MQ & Broker admin |
|
Back to top |
|
 |
|