Help with script variable usage

[tschullo]

FOR EACH wNums in '76','1226,1250','1251,1255','1256,1268','1269,1273','1274,1280','1281,1290','1291,1300','1301,1311','1312,1321','1322,1335','1336,1345'
myFlag=index(chr(39) & $(wNums) & chr(39),',');

if $(myFlag) > 0 Then

LET wClause = 'BETWEEN ' & subfield(chr(39) & $(wNums) & chr(39),',',1) & ' AND ' & subfield(chr(39) & $(wNums) & chr(39),',',2);
else

LET wClause = '=' & $(wNums);
end if;

//wClause = if(index($(wNums),','),'BETWEEN ' & subfield( $(wNums) ,',',1) & ' AND ' & subfield( $(wNums) ,',',2),'=' & $(wNums));
//wClause = if(index(chr(39) & $(wNums) & chr(39),','),'BETWEEN ' & subfield(chr(39) & $(wNums) & chr(39),',',1) & ' AND ' & subfield(chr(39) & $(wNums)

next wNums;

I thought this would be simple and straightforward.

I just want to dynamically produce a series of WHERE clauses based on parameters passed in on a FOR EACH loop.

It's annoying that the single quotes are stripped, is their a way to not lose them?

As you can tell by commented out code I have tried this several ways now. Any suggestions would be greatly appreciated,

including reading material to explain the ins and outs of using variables in a script.

Also, in a FOR EACH command, is it possible to break the parameters out into more than one line?FOR EACH wNums in '76','1226,1250','1251,1255','1256,1268','1269,1273',

          '1274,1280','1281,1290','1291,1300','1301,1311','1312,1321','1322,1335','1336,1345'

Script editor not happy when I do that.

Thanks,

Tony

[pamaxeed]

Hi,

try to use something like that:

Let strWithoutQuote= ' where company in (#C1#,#C2#)';

Let strWithSingleQuote = replace(strSQL,'#',Chr(39));

and the result will be:

strWhereCluase= where company in ('C1','C2')

Cheers,

Patric

[maxgro · Accepted solution]

FOR EACH wNums in '76','1226,1250','1251,1255','1256,1268','1269,1273','1274,1280','1281,1290','1291,1300','1301,1311','1312,1321','1322,1335','1336,1345'

  //myFlag=index(chr(39) & $(wNums) & chr(39),',');

  let myFlag=index('$(wNums)', ',');

  //trace vNums=$(wNums)   myFlag=$(myFlag);

  if $(myFlag) > 0 Then

  //LET wClause = 'BETWEEN ' & subfield(chr(39) & $(wNums) & chr(39),',',1) & ' AND ' & subfield(chr(39) & $(wNums) & chr(39),',',2);

  LET wClause = 'BETWEEN ' & subfield('$(wNums)',',',1) & ' AND ' & subfield('$(wNums)',',',2);

  else

  //LET wClause = '=' & $(wNums);

  LET wClause = '=$(wNums)';

  end if;

  //wClause = if(index($(wNums),','),'BETWEEN ' & subfield( $(wNums) ,',',1) & ' AND ' & subfield( $(wNums) ,',',2),'=' & $(wNums));

  //wClause = if(index(chr(39) & $(wNums) & chr(39),','),'BETWEEN ' & subfield(chr(39) & $(wNums) & chr(39),',',1) & ' AND ' & subfield(chr(39) & $(wNums)

  TRACE wClause   $(wClause);

next wNums;

[pamaxeed]

In your example something like that:

FOR EACH wNums in '#1226,1250#' //,'#1251,1255#','#1256,1268#','#1269,1273#','#1274,1280#','#1281,1290#','#1291,1300#','#1301,1311#','#1312,1321#','#1322,1335#','#1336,1345#'

LET wClause = '=' & replace(wNums,'#',Chr(39));

next wNums;

Note to get the string value of each for entry do not use $(wNums) but just wNums.

Cheers,

Patric

[tschullo]

Dude, you rock!