| Author |
Message
|
| Vitor |
 Posted: Mon Jan 23, 2012 10:11 am Post subject: Using Handlers with CAST |
 |
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
I have a requirement to parse an XML element which can contain an integer value or "N/A". A sensible solution to the problem would seem be the addition of an XML attribute "applicable" that took a value of yes or no but it seems this would cost $6,000,000, take 5 months and require a UN resolution. The downstream app that's consuming the XML has already complained that the XSD clearly describes this field as an xsd:integer and they keep getting "N/A" in it. So here I am, scrubbing the XML so it matches it's schema and not feeling bitter at all.
Because this value can be 0, +ive or -ive I thought to use a handler to detect the cast failure. When a character value is passed the handler fires as expected but there's not SQLCODE to indicate the reason. A few moment with the search facility turned up this which contains this year old comment:
| mgk wrote: |
| Unfortunately only CASTS involving Char-> Decimal and the Base64Decode function currently use the S22018 SQL state code which is why you do not see it on a CAST of a char -> integer... |
Casting my value to a DECIMAL not an INTEGER yields a SQLCODE as this post indicates.
I have 2 questions resulting:
1) Is this documented anywhere? The InfoCenter entry for CAST has examples that use handlers (though not for CHAR -> INTEGER) and at least it's a bit misleading for the feeble minded.
2) What, in the opinion of the readership, is the best solution given this feature? I've come up with:
- Explicitly test for "N/A", only cast if it's not that and rely on the front end to put "N/A" in the field rather than "NA", "MISSING", "NOT SURE", etc
- Use a handler, cast the value to a decimal to trigger the handler then cast again to an integer
- Put a default in the cast of some mad value and test for that coming out
- A more ingenious option I've not thought of
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
 |
| mqjeff |
| Posted: Mon Jan 23, 2012 10:18 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| Code: |
| TRANSLATE(UPPER(inputInteger),'ABCDEFGHIJKLMNOPQRSTUVWXYZ/',); |
? |
|
| Back to top |
|
|
| Vitor |
| Posted: Mon Jan 23, 2012 10:28 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| mqjeff wrote: |
| Code: |
| TRANSLATE(UPPER(inputInteger),'ABCDEFGHIJKLMNOPQRSTUVWXYZ/',); |
? |
Valid. 
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| mgk |
| Posted: Mon Jan 23, 2012 10:50 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
You don't say what you want to do when the data is "N/A", but have you tried using the DEFAULT clause on the CAST statement:
DECLARE answer DECIMAL CAST( input AS DECIMAL DEFAULT DECIMAL 'NAN');
Kind 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 |
|
|
| Vitor |
| Posted: Mon Jan 23, 2012 10:55 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| mgk wrote: |
You don't say what you want to do when the data is "N/A", but have you tried using the DEFAULT clause on the CAST statement:
DECLARE answer DECIMAL CAST( input AS DECIMAL DEFAULT DECIMAL 'NAN'); |
Now that's ingenious! 
All I have to do with this is detect it and route the inbound message into a bucket for manual intervention. In theory this is a mandatory integer value, in practice the end user's don't always know it when they key the records so put in "N/A".
Don't laugh. 
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| mgk |
| Posted: Mon Jan 23, 2012 11:05 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
| Quote: |
| Now that's ingenious! |
Glad you like it 
Detecting NAN after the CAST is easy:
| Code: |
IF answer IS NAN THEN
--deal with invalid case
ELSE
--deal with valid case
END IF; |
Kind 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 |
|
|
| adubya |
| Posted: Mon Jan 23, 2012 11:40 am Post subject: |
|
|
Partisan
Joined: 25 Aug 2011
Posts: 377
Location: GU12, UK
|
| I use the DEFAULT clause for situations just like that, a handy tool to have in the kitbag |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Mon Jan 23, 2012 9:44 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| Vitor wrote: |
| mgk wrote: |
You don't say what you want to do when the data is "N/A", but have you tried using the DEFAULT clause on the CAST statement:
DECLARE answer DECIMAL CAST( input AS DECIMAL DEFAULT DECIMAL 'NAN'); |
Now that's ingenious!
All I have to do with this is detect it and route the inbound message into a bucket for manual intervention. In theory this is a mandatory integer value, in practice the end user's don't always know it when they key the records so put in "N/A".
Don't laugh. |
If the field is nillable, have you thought about using xsi:nil='true' for your non applicable case? 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| Vitor |
| Posted: Tue Jan 24, 2012 12:50 pm Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| fjb_saper wrote: |
| If the field is nillable, have you thought about using xsi:nil='true' for your non applicable case? |
This would be valid if the field really was nillable, but it's a mandatory integer. As I said above, the root problem is that the sending application isn't validating the XML against their own schema & allowing this field to be a character. The business rules insist this value must be provided by this message so why they allow the users to finish the transaction when they don't know the value to put in this field is beyond me.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
|
|
