Paul Vinkenoog wrote:

> Hi Todd,
>
> > My stored procedure seems to work. I am populating a double
> > precision column with data from the char column. I check each
> > character before adding it to the double precision column. Actually
> > I add them to another char column and then do a cast at the end of
> > the procedure.
>
> I liked your puzzle, so I wrote an SP that converts chars into doubles
> if possible, and (if necessary) ignores any trailing non-digits, so
> 12XB yields 12.
>
> Having read your latest postings, I'm not so sure anymore if this is
> exactly what you want (I now get the impression you just drop any
> non-digits so even A1Q2U returns 12), but I'll let it follow anyway.

No, I think you got it. 12XB returns 12, and 12.2A returns 12.2, but
A1Q2U would return NULL because the first character is not numeric.

> If you declare the standard UDF substrlen in your db, and then this:
>
> create procedure char7_to_double( charvar char( 7 ) )
> returns ( doublevar double precision )
> as
> declare variable len smallint;
> begin
> doublevar = null;
> len = 7; // could generalize this using strlen
> while ( len > 0 ) do
> begin
> doublevar = cast ( charvar as double precision );
> exit; // upon success; line is skipped if cast failed
> when gdscode convert_error /* or sqlcode -413 */ do
> begin
> len = len - 1;
> if ( len > 0 ) then charvar = substrlen( charvar, 1, len );
> end
> end
> end
>

I don't have the stored procedure here at home so I can't post it.
Yours is more elegant as I have a separate loop for each character in
the string. I grab each character, test it using the gdscode, then use
concatenate to add it to a char variable. At the end I convert the char
variable to a double. I had problems with codes with two decimal points
('1.2.1'), so I had to add some other code for that.

>
> Now, in your before insert/update triggers, you simply say
>
> execute procedure char7_to_double New.QuickKey
> returning_values New.QuickNum; // *not* select ... into

>
> ( In "after" triggers, you need a searched update because you can't
> assign to New. Well, you can, but it has no effect. I do it at
> least once a month and then wonder why things don't work ;-) )

I have an IF statement, something like:

IF(NEW.Quickkey <> OLD.Quickkey or NEW.Quickkey is null) THEN
Execute procedure

>
> Many people don't like EXIT. If you're one of those, you need to add
> an extra var and some lines to check for success or failure.

Thanks for your procedure code. I think I'll try it and maybe replace
mine with yours.

Todd

>
>
> Greetings,
> Paul
>
>
>
> Yahoo! Groups Sponsor
> ADVERTISEMENT
> Click Here!
> <http://rd.yahoo.com/M=244522.3313099.4604523.1261774/D=egroupweb/S=1705115386:HM/A=1595056/R=0/SIG=124fv1soh/*http://ashnin.com/clk/muryutaitakenattogyo?YH=3313099&yhad=1595056>
>
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
> <http://docs.yahoo.com/info/terms/>.

[Non-text portions of this message have been removed]