Nested While...Do loops?

[mike777]

Does Interbase allow this?
If you REALLY want to help, can you tell me why this procedure isn't working?

create procedure prc_TestingSubStr(vcStrIn varchar(10),iBeg integer,iNumChar integer) returns (vcStrOut varchar(256)) as
     declare variable vcTempStr1 varchar(10); /*Wildcard place holder*/
     declare variable vcTempStr2 varchar(10); /*Singular read value for testing*/
     declare variable iCounter1 integer;      /*Used as a value to step through all potential characters.*/
     declare variable iCounter2 integer;      /*Counts the number of characters up to first character in return value*/
     declare variable iCounter3 integer;      /*Counts the number of characters in the return value*/
     declare variable iCounter4 integer;      /*Emergency exit counter*/
     begin /*main body*/
          vcStrOut=''; /*return value*/
          vcTempStr1='';
          vcTempStr2='';
          iCounter1=0;
          iCounter2=1;
          iCounter3=0;
          if (iBeg is null) then exit;
          if (iNumChar is null) then exit;
          while (1=1) do
               begin /*infinite loop*/
                    while (iCounter1 < 10) do
                         begin /*assign value to test with*/
                              vcTempStr2=cast(iCounter1 as char(1));
                              if (vcStrIn like vcTempStr1||vcTempStr2||'%') then
                                   begin
                                        iCounter1=10;
                                        if (iCounter2 >= iBeg-1) then
                                             begin
                                                  vcStrOut=vcStrOut||vcTempStr2;
                                                  iCounter3=iCounter3+1;
                                                  if (iCounter3=iNumChar) then
                                                       begin
                                                            suspend;
                                                            exit;
                                                       end
                                             end
                                        else
                                             vcTempStr1=vcTempStr1||'_';
                                   end
                              else
                                   iCounter1=iCounter1+1;
                                   if (iCounter1=10) then
                                        vcTempStr1=vcTempStr1||'_';
                         end /*assign value to test with*/
                    iCounter2=iCounter2+1;
                    iCounter1=0;
               end /*infinite loop*/
     end /*main body*/;

When I try:

execute procedure prc_TestingSubStr('a2345678',3,1)

, I get an answer of 2.
When I try:

execute procedure prc_TestingSubStr('a2345678',2,1)

, I get an answer of 2.
It's supposed to return a substring. In the first case, the answer should be 3, not 2. In the second case, the result is correct.
I tried some more:
When I try:

execute procedure prc_TestingSubStr('a2345678',4,1)

, I get an answer of 4 (correct).
When I try:

execute procedure prc_TestingSubStr('a2345678',5,1)

, I get an answer of 6 (incorrect).
Whew! I know I'm askin a lot here. Any help you can provide is very much appreciated.
Thank you.
-Mike Kemp

 

[mike777]

OK, I got it.
I must be the only person that answers their own questions. You know what they say, "It's OK to talk to yourself, as long as you don't start answering yourself...". Well...
I've highlighted the correction in red.

create procedure prc_TestingSubStr(vcStrIn varchar(10),iBeg integer,iNumChar integer) returns (vcStrOut varchar(256)) as
     declare variable vcTempStr1 varchar(10); /*Wildcard place holder*/
     declare variable vcTempStr2 varchar(10); /*Singular read value for testing*/
     declare variable iCounter1 integer;      /*Used as a value to step through all potential characters.*/
     declare variable iCounter2 integer;      /*Counts the number of characters up to first character in return value*/
     declare variable iCounter3 integer;      /*Counts the number of characters in the return value*/
     declare variable iCounter4 integer;      /*Emergency exit counter*/
     begin /*main body*/
          vcStrOut=''; /*return value*/
          vcTempStr1='';
          vcTempStr2='';
          iCounter1=0;
          iCounter2=1;
          iCounter3=0;
          if (iBeg is null) then exit;
          if (iNumChar is null) then exit;
          while (1=1) do
               begin /*infinite loop*/
                    while (iCounter1 < 10) do
                         begin /*assign value to test with*/
                              vcTempStr2=cast(iCounter1 as char(1));
                              if (vcStrIn like vcTempStr1||vcTempStr2||'%') then
                                   begin
                                        iCounter1=10;
                                        if (iCounter2 >= iBeg-1) then
                                             begin
                                                  vcStrOut=vcStrOut||vcTempStr2;
                                                  iCounter3=iCounter3+1;
                                                  if (iCounter3=iNumChar) then
                                                       begin
                                                            suspend;
                                                            exit;
                                                       end
                                             end
                                        else
                                             vcTempStr1=vcTempStr1||'_';
                                   end
                              else

begin

                                        iCounter1=iCounter1+1;
                                        if (iCounter1=10)  then

                                             vcTempStr1=vcTempStr1||'_';

                                   end

                         end /*assign value to test with*/
                    iCounter2=iCounter2+1;
                    iCounter1=0;
               end /*infinite loop*/
     end /*main body*/;

 

[goreXP]

what is the use of the "prc_TestingSubStr" since there is allready a "sbstring(s from i for k)" function in sql standard!

 

[mike777]

Thanks, GoreXP.
When I try

"select sbstring('mike' from 2 for 2) from tbl_CampaignStatsTableNames"

,
I get

Dynamic SQL Error, SQL error code=-104, Token unknown - line 1, char23, from (#-104)

.
Also, all the documentation I have on Interbase SQL says there is no "SBSTRING" (or substring) function.
Am I missing something?
Thanks again.
-Mike

 

[MartijnTonies]

InterBase doesn't have a native "SUBSTRING" function.

However, there are some available, if I'm correct, in free UDF libraries. Try searching for "FreeUDFLib".


Martijn Tonies
Database Workbench - the developer tool for InterBase, Firebird, MySQL & MS SQL Server

http://www.upscene.com