| Author |
Message
|
| MQGuy2000 |
 Posted: Tue Nov 30, 2004 3:00 pm Post subject: Validation using ESQL |
 |
|
Centurion
Joined: 20 Jul 2003
Posts: 131
|
Hi Gurus
Cheers to all. I need to validate the following.
The incoming message has a field called SSN. It is supposed to be only numbers. and should not contain any alphabets.
I am trying to CAST it to INTEGER and if it throws an runtime exception then I know that it is not a valid #.
mySSN = cast(InputRoot.MRM.SSN as INTEGER)
But I do not know how to catch the runtime exception and continue with the program.
This is how we do in Java. I do not know if this is the correct approach in eqsl. Can anybody throw some light.
regards
[/b] |
|
| Back to top |
|
 |
| chanduy9 |
| Posted: Tue Nov 30, 2004 3:29 pm Post subject: |
|
|
Disciple
Joined: 28 Nov 2001
Posts: 177
Location: USA
|
Hi,
How you defined the mySSN variable..is it char or interger??...can you post your error.
Thanks,
Chandra. |
|
| Back to top |
|
|
| kirani |
| Posted: Tue Nov 30, 2004 5:05 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
What version of WMQI are you using? If using V5 then try making use of IS ESQL function with NAN constant.
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
| javaforvivek |
| Posted: Tue Nov 30, 2004 11:29 pm Post subject: |
|
|
Master
Joined: 14 Jun 2002
Posts: 282
Location: Pune,India
|
Kiran,
I have a message set where ExchangeRate is defined as a string. I need to convert this ExchangeRate to decimal
and then inverse it.
When I write this code:
| Code: |
DECLARE rateString CHARACTER;
DECLARE rate DECIMAL 0.0;
SET rateString = inputcursor.ExchangeRate;
IF (rateString IS NOT NAN)THEN
SET rate = CAST(rateString AS DECIMAL);
END IF;
|
But it gives me error when I save this ESQL file.
In the task list it shows
Syntax error. Valid options include: FALSE NULL TRUE UNKNOWN
with the red cross.
Whats wrong here??
I am on WBIMB5+CSD4
_________________
Vivek
------------------------------------------------------
...when you have eliminated the impossible, whatever remains, however improbable, must be the truth. |
|
| Back to top |
|
|
| kirani |
| Posted: Tue Nov 30, 2004 11:46 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
Vivek,
I have not used this personally, so I don't know the correct syntax. I read in the manual that this can be done.
| Quote: |
Operator IS
The operator IS allows you to test whether a value is NULL.
This includes testing values INF, +INF, -INF, NAN (not a number), and NUM in any mixture of case. The alternative forms +INFINITY, -INFINITY, and NUMBER are also accepted.
If applied to non-numeric types, the result is FALSE.
The comparison operator (=) does not allow this because the result of comparing with NULL is NULL. It also allows you to use a more natural English syntax when testing boolean values.
|
I don't have access to WBIMB right now, but I will try to test this later.
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
| kimbert |
| Posted: Thu Dec 02, 2004 1:12 am Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
| In your message set, you could define SSN as DECIMAL or INTEGER instead of STRING - then the MRM parser will throw a data conversion exception if the input is not a valid number, and the message will go to the failure queue. |
|
| Back to top |
|
|
| jlalbor |
| Posted: Mon Jun 27, 2005 4:23 pm Post subject: Use of IS with NAN |
|
|
Apprentice
Joined: 18 Feb 2003
Posts: 38
|
| Quote: |
Operator IS
The operator IS allows you to test whether a value is NULL. This includes testing values INF, +INF, -INF, NAN (not a number), and NUM in any mixture of case. The alternative forms +INFINITY, -INFINITY, and NUMBER are also accepted. If applied to non-numeric types, the result is FALSE. The comparison operator (=) does not allow this because the result of comparing with NULL is NULL. It also allows you to use a more natural English syntax when testing boolean values.
IS operator Operand IS NOT TRUE FALSE UNKNOWN NULL
The result is TRUE if the value of the left operand is equal (or not equal if the NOT clause is present) to the specified value (TRUE, FALSE, UNKNOWN, or NULL). Otherwise the result is FALSE. |
Did you find out who tu use the NAN or INF with the IS operator? |
|
| Back to top |
|
|
| mgk |
| Posted: Mon Jun 27, 2005 7:14 pm Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Hi,
1: NAN, INFINITY, +INFINITY, -INFINITY, NUM and NUMBER are values that only variables of type DECIMAL can have, so you cannot use then to test a STRING to see if it is a valid value before making a CAST to DECIMAL. If you need to do this you will have to write your own ESQL / Java function / method to walk the string testing each character.
2: Their seems to be a problem in that the special values above (INF etc)are currently not recognised by the tooling and so cannot be deployed to the runtime. I will arrrange for this to be fixed, and if anyone needs that fix before the next CSD then please raise a PMR
Regards,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| javaforvivek |
| Posted: Wed Jun 29, 2005 12:40 am Post subject: |
|
|
Master
Joined: 14 Jun 2002
Posts: 282
Location: Pune,India
|
| Quote: |
1: NAN, INFINITY, +INFINITY, -INFINITY, NUM and NUMBER are values that only variables of type DECIMAL can have, so you cannot use then to test a STRING to see if it is a valid value before making a CAST to DECIMAL. If you need to do this you will have to write your own ESQL / Java function / method to walk the string testing each character. |
If I already know that my value is of type Decimal then what's the use of checking it with NAN..
My expectation from NAN is that it should check whether a character string can be converted to numeric datatype or not..
Does it make any sense to check a decimal value with NAN?
_________________
Vivek
------------------------------------------------------
...when you have eliminated the impossible, whatever remains, however improbable, must be the truth. |
|
| Back to top |
|
|
| jlalbor |
| Posted: Wed Jun 29, 2005 8:07 am Post subject: Testing whethere a String is a numeric value or not |
|
|
Apprentice
Joined: 18 Feb 2003
Posts: 38
|
| mgk wrote: |
Hi,
1: NAN, INFINITY, +INFINITY, -INFINITY, NUM and NUMBER are values that only variables of type DECIMAL can have, so you cannot use then to test a STRING to see if it is a valid value before making a CAST to DECIMAL. If you need to do this you will have to write your own ESQL / Java function / method to walk the string testing each character.
2: Their seems to be a problem in that the special values above (INF etc)are currently not recognised by the tooling and so cannot be deployed to the runtime. I will arrrange for this to be fixed, and if anyone needs that fix before the next CSD then please raise a PMR
Regards, |
I really think that the broker should have a function to test whethere a String is a numeric value or not. I have found the need of doing this in several cases without raising an Exception. I can not believe that a basic function like this is not available, its a shame. |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Wed Jun 29, 2005 9:58 am Post subject: Re: Testing whethere a String is a numeric value or not |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
| jlalbor wrote: |
| I really think that the broker should have a function to test whethere a String is a numeric value or not. |
I think if CAST fails, then you know it's not a valid numeric value.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| javaforvivek |
| Posted: Wed Jun 29, 2005 8:36 pm Post subject: |
|
|
Master
Joined: 14 Jun 2002
Posts: 282
Location: Pune,India
|
jefflowry wrote:
| Quote: |
| I think if CAST fails, then you know it's not a valid numeric value |
Jeff,
You are right here.. but when CAST fails it raises an exception and we have to have some exception handling mechanism. Again when the exception is raised, it rollbacks..
jlalbor wrote:
| Quote: |
| I really think that the broker should have a function to test whethere a String is a numeric value or not. I have found the need of doing this in several cases without raising an Exception. I can not believe that a basic function like this is not available, its a shame. |
I agree with jlalbor.
What we really want is just to check whether a string can be CAST to numeric or not, before actually calling CAST Function.. something like this:
| Code: |
DECLARE numericString CHARACTER '1234.56';
DECLARE numericDecimal DECIMAL;
IF (numericString can be CAST to number) THEN
SET numericDecimal = CAST (numericString AS DECIMAL);
ELSE
SET numericDecimal = 0.00;
END IF;
|
What we want is some function to replace 'numericString can be CAST to number'
_________________
Vivek
------------------------------------------------------
...when you have eliminated the impossible, whatever remains, however improbable, must be the truth. |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Thu Jun 30, 2005 3:17 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
| javaforvivek wrote: |
| You are right here.. but when CAST fails it raises an exception and we have to have some exception handling mechanism. |
Like the Try/Catch node, you mean?
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| javaforvivek |
| Posted: Thu Jun 30, 2005 3:42 am Post subject: |
|
|
Master
Joined: 14 Jun 2002
Posts: 282
Location: Pune,India
|
Yes.. but exception handling mechanism is not the issue here.. we have discussed a lot on it in this forum.
what I wanted to say is that, I won't like that entire message is rolled back just because of the CAST is failed.
Can't we have such function as isNAN(numericString) which returns boolean.
So,
| Code: |
IF (isNAN('1234.56')) THEN
SET amount = CAST('1234.56' AS DECIMAL);
ELSE
-- log to database
END IF; |
And continue with further processing...
_________________
Vivek
------------------------------------------------------
...when you have eliminated the impossible, whatever remains, however improbable, must be the truth. |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Thu Jun 30, 2005 4:15 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
I agree this might be a useful thing.
I would rather see a more useful thing, of a try/catch block IN ESQL. So you could trap this better.
But, you know, with v6, the Java integration will probably solve both problems. Or at least replace them with Java problems... 
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
|
|
