Something equivalent with contains command in script

[Levente]

Hi All,

I have created this For loop to simply drop most of the tables that I have got from a so called binary load:

no_of_tables = NoOfTables()-1
j =0
dropped = 1
For t = 0 to $(no_of_tables)


Exit For when j > 5;

tname = '['&TableName($(t))&']'

tname_check = substringcount('string_debug',TableName($(t)))

if substringcount('string1,$tname)=0 and substringcount('string2,$tname)=0 and substringcount('string3',$tname)=0 and substringcount('string4',$tname)=0 and substringcount('string5',$tname)=0 then


drop table $(tname);

tablesdropped:

Load
$(dropped) as recordnumber,
TableName($(t)) as Table
Autogenerate 1;

dropped = $(dropped)+1

else

j = $(j)+1

end if


t = $(t)-1

Next t

When I want to check if the actual table is one of those that I intend to keep the substringcount function value is always NULL...

I have checked it in a step-by-step run and this is why I created the tname_check variable which was always NULL...

What should I change to get the result I want?

Thanks and best regards,

Levente

[pradosh_thakur]

---

---

if substringcount('string1,$tname)=0 and substringcount('string2,$tname)=0 and 

in  string1 and string2 you have started the single quotes but didn't end it. $(tname) should be written like $(tname) and not $tname. Just check these in the entire code . if it doesn't work repost your code and we can again have a look at what's wrong.

[Levente]

Hi Pradosh,

Suggested modifications (single quotes and $(tname)) did not help...

Only change is that now value is always 0 and not NULL...

Levente

[pradosh_thakur]

Please check for the semicolon as well. You have missed it in a few places.

[Levente]

Hi Pradosh,

Below code worked when I used a limited set of tables (different binary load).

I had to change tname as in extended  binary load there are tables with "-" sign in their names which resulted error during report run... Since then I can not achieve to drop all tables that I do not need in my project.

no_of_tables = NoOfTables()-1

j =0

dropped = 1

For t = 0 to $(no_of_tables)

   Exit For when j >2

                tname = TableName($(t))

   if tname <> 'V_CF_TR_BASE_F' and tname <> 'V_CF_TR_EVT_F' then

               drop table $(tname);

                                tablesdropped:

       Load

       $(dropped) as recordnumber,

       TableName($(t)) as Table

       Autogenerate 1;

       dropped = $(dropped)+1

                else

                                j = $(j)+1

   end if

   t = $(t)-1

Next t

[pradosh_thakur]

Whu dont you use wild match in the line
" if tname <> 'V_CF_TR_BASE_F' and tname <> 'V_CF_TR_EVT_F' then"
to look for the table name and drop it.

[Levente]

I have tried and could not get back what I want...

tname_check = WildMatch($(tname),'*DEST*')

tname_check = WildMatch(tname,'*DEST*')

tname_check = Substringcount('DEST',$(tname))

tname_check = Substringcount('DEST',tname)

whichever I use I get back 0 value for tname_check although I now for sure that current table's name contains "DEST"...

[pradosh_thakur]

if you want to delete table that contain dest try the below

if (tname <> 'V_CF_TR_BASE_F' and tname <> 'V_CF_TR_EVT_F') or wildmatch(tname,'*DEST*') then

OR 

if (tname <> 'V_CF_TR_BASE_F' and tname <> 'V_CF_TR_EVT_F') or SUBSTRINGCOUNT(UPPER(tname),'DEST') then

[Levente · Accepted solution]

The simplest the best...

The last thing I have tried was simply making same modification in if statement then what I have done with tname variable...

    tname = '['&TableName($(t))&']'

   if tname <> '[V_CF_TR_BASE_F]' and tname <> '[V_CF_TR_EVT_F]' then

This version works just as fine with both binary loads...

[pradosh_thakur]

That's great , Please mark helpful and correct answers and close the thread.