Use "Set Type" variable in Where Clause

[#1]

Hi everyone,

I've a set of values from where I would like to filter the load of a table.So far, I wrote each of those, in the where clause.

GANANCIAS_PERDIDAS:

Directory;

LOAD @1 as COD_EMPRESA,

     @2 as MES_O,

     @3 as ANNO_O,

           MAKEDATE( @3, @2) AS FECHA,

           YEAR(MAKEDATE( @3, @2)) AS ANNO,

           MONTH(MAKEDATE( @3, @2)) AS MES,

     @4 as COD,

     mid(@4, 1,3) AS COD_CUENTA,

     IF (len(@4)>= 5, mid(@4, 1,5)) AS COD_SUBCUENTA,

     IF (len(@4)= 7, mid(@4, 1,7)) AS COD_SUBSUBCUENTA,    

     @5 as MONTO,

     @6 as DEPRECIACION_ACUM,

     @7 as MONTO_TOTAL

FROM

info62011.txt

(txt, codepage is 1252, no labels, delimiter is ',', msq)

WHERE

//          mid(@4, 1,3) EXISTS $(BG);

          (mid(@4, 1,3)='301' OR mid(@4, 1,3)='321' OR mid(@4, 1,3)='341' OR mid(@4, 1,3)='381' OR mid(@4, 1,3)='395' OR

          mid(@4, 1,3)='501' OR mid(@4, 1,3)='521' OR mid(@4, 1,3)='541' OR mid(@4, 1,3)='581' OR mid(@4, 1,3)='595'

           OR mid(@4, 1,3)='60000') AND @1 ='106';

So far, there arent too many values, but I will be. So, what I need, is to use a variable, set type, and use it in thw where clause.

Something like:

     SET BG='301;321;341;381;395;501;521;541;581;595;60000';

And in where use something like  mid(@4, 1,3) EXISTS $(BG); instead.

Is that posible??

Any ideas?

Thxs in Advance!

Leonor

[swuehl]

Hi Leonor,

what could work is something like

set vTest = (10,20,30);

Product:

LOAD ProductID,

  .....

  ProductName;

SQL SELECT *

FROM Product where mid(ProductNumber,5,2) in $(vTest);

I said "could work", because above example worked in real life, but the working of the where...in.. clause is depending on the underlying data source driver. For example, I think this will not work if you load in a plain text file (like in your example).

Regards,

Stefan