Re: Stored procs, sub strings, upper case and conv string to int
John Kinsella wrote:
>
> 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];
As you have found you can't use variables. If you can't get the data already
split then you have to split it manually into CHAR variables, then use
a number of IF statements to 'build' a new string.
Remember also, that the syntax is [start, end], NOT [start, length].
> 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
There is no UPSHIFT function in SQL. Again you would have to do this
manually.
> 3. Take a string variable containing numbers and convert it to type
> interger:
> LET CheckSum = theOrgNo[VAT_len + 3,2] to INT;
You don't need to explicitly convert, Informix does this for you.
> 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;
This folowing code compiles, but I assume your example is not a finished
procedure, because VAT_len never changes value. You can use the LENGTH
function to determine the length of a CHAR variable.
If you need to use VAT_len then you will need a number of IF statements
to allocate the sub-strings to VATNumber based on the value of VAT_len.
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 CheckSum = theOrgNo[10,11];
-- LET VATNumber = theOrgNo[3,VAT_len] to INT;
LET VATNumber = theOrgNo[3,9];
-- IF VAT_len = 7
-- THEN
-- LET VATNumber = theOrgNo[3,9];
-- END IF;
-- IF VAT_len = 8
-- THEN
-- LET VATNumber = theOrgNo[3,10];
-- END IF;
-- :
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;
END PROCEDURE;
Hope that helps,
--
Mark.
+-------------------------------------------------------------------------+
|Mark D. Stock - The West Solutions Group http://www.west.co.za |
| The Informix FAQ is at http://www.iiug.org |
|mailto:marks@west.co.za +------------------------------------------------+
|Tel: +27 11 803 2151 |If it doesn't work... force it! |
|Fax: +27 11 803 2189 |If it breaks... it needed replacing anyway! |
|Cell: +27 83 250 2325 |Well, that's how I code anyway! |
+------------------------+------------------------------------------------+