Help needed on SQL script

[shyam_uthaman]

Hi,

I am a bit new to data services and need some help in understanding how to write a script.

The scenario is explained as below:

There is a table having 2 columns as below:

Column A is of datatype Varchar.

COLUMN_A	COLUMN_ B
100	xxx
a900	x
abc200	xxxx
dasds47	xxxxx

Now, I need to fill a variable $Count with the maximum number of characters that any rows of column A has.

As in this case dasds47 has the most number of characters ('dasds') which is equal to 5.

The logic I am thinking is to loop on the table and again loop on the string to check if 1st letter is a number or not

(substr( TABLEA.COLUMN_A,1,1 )>='0') AND (substr( TABLEA.COLUMN_A,1,1)<='9')

Similarly check each character of each string until the length of string.

I know ABAP but am new to scripting in BODS.. can someone please help me in writing the logic.. I have no idea on how the syntax should be.

[former_member186894]

Hi, you can use this code.. assume this function name is F_MAX().. call this function from transform, pass that column name as a parameter for this function.

declare all the variable which are used.

$L_in = $P_in;

$L= length($L_in);

$I = 1;

$COUNT=0;

$L_out = 0;

while( $I <= $L)

begin

##LOGIC HERE

if( match_pattern(substr($L_in,$I,1),'[A-Z|a-z]') = 1)

begin

$L_out=$L_out+1;

if($COUNT<$L_out)

begin

$COUNT=$L_out;   

end

end

$I = $I +1;        

end

print('MAX : '|| $COUNT );

Return $L_out;

Imran

[Former Member]

Hi,

Add another column say COLUMN_C and generate row count using gen_row() function.

Use a while loop, create variables do some thing like below-

$Count_row = 1;

$Max_row = sql('datastorename','select max(ROW_ID) from tablename');

while ( $Count_row <= $Max_row)

begin

          $Count= sql('datastorename','select COLUMN_A from tablename where (substr( TABLEA.COLUMN_A,1,1 )>='0') AND (substr( TABLEA.COLUMN_A,1,1)<='9')');

          $Count_row = $Count_row + 1;

          end

Pull the variable $Count in the column to get the value.

Arun