Stored procs, sub strings, upper case and conv string to int
I am trying to find solutions to three rather elementary problems.
The manuals do not seem to cover these topics.
1. Sub-strings
According to the manual Informix guide to sql page 3-5, I quote
You can also select any part of a BYTE column by using subscripts as
shown in the following example:
SELECT cat_picture [1,75] FROM catalog WHERE catalog_num = 100001
What I want to do is use a variable:
LET CheckSum = theOrgNo[VAT_len + 3,2];
2. Upper Case
I dont find any reference to such a function in the informix manuals.
What I want to do is convert a nation code however it is input so that
it is always upper case
3. Take a string variable containing numbers and convert it to type
interger:
LET CheckSum = theOrgNo[VAT_len + 3,2] to INT;
For completeness the kind of proceedure I am thinking of may look like:
create procedure VALIDATE_VAT_NO(theOrgNo char(16))
DEFINE NationCode char(2); DEFINE VATNumber INT;
DEFINE CheckSum INT;
DEFINE VAT_len INT;
DEFINE VAT_divider INT;
DEFINE rest INT;
DEFINE theMsg char(60);
begin
LET NationCode = theOrgNo[1,2];
IF (NationCode = 'BE') THEN
LET VAT_len = 7;
LET VAT_divider = 97;
ELIF (NationCode = 'NL') THEN
LET VAT_len = 7;
LET VAT_divider = 97;
ELSE
LET theMsg = 'No nation code is supplied with VAT
number!';
raise exception -20001 , '1101: ' || theMsg;
END IF;
LET CheckSum = theOrgNo[VAT_len + 3,2] to INT;
LET VATNumber = theOrgNo[3,VAT_len] to INT;
LET rest = MOD(VATnumber,VAT_divider);
IF (CheckSum <> (VAT_divider - rest))
THEN
LET theMsg = 'VAT number is not correct!';
raise exception -20001 , '1102: ' || theMsg;
END IF;
end procedure;
I would appreciate any replys !!!
--
##################################################
# John Kinsella #
# +46 (042) 25 39 39 #
# John.Kinsella@mactive.se #
# http://www.staff.mactive.se:1080/John_K/ #
##################################################
# UNIX is user friendly, #
# it's just picky about who it's friends are. #
##################################################