WHERE CLAUSE WITH A VARIABLE

[battistiandco]

Hi, who can help me.

I want to use a WHERE clause during a load of a file where I check a field with a variable assigned value before the script execution starts

Example: if I start in this way

qv.exe /r /vMyvar=123 abc.qvw

let Myvar = '';

load Field1,

        Field2

[from c:\tmp\Test.txt]

WHERE (Field1 = Myvar);

But an error occurs during the load. It don't recognise the variable Myvar

[rbecher · Accepted solution]

Hi,

try this:

1. don't set the variable in the script to an empy value (let Myvar = '';)

2. use right notation in Where Clause:

WHERE (Field1 = $(Myvar));  // on numeric value

WHERE (Field1 = '$(Myvar)');  // on string value

- Ralf

[whiteline]

The string "let Myvar = ''; " in your script erases the value. Remove It.

[battistiandco]

Thank, Great Ralf

Rainer

[sudeepkm]

Hi Ralf,

Is it possible to load field values of a table to a variable and then use the variable in the load script where clause like below.

filtertab:

load * Inline [

code

dd

mm

];

MainDatatab:

LOAD ID,

     Date,

     City,

     CityCode

FROM

<some table>

where not match(CityCode,'dd','mm');

In the above scenario I would like to update the where clause to exclude the values of CityCode with the values of the field code (from table filtertab) instead of hard coding the values in the where clause.

[rbecher]

Hi Sudeep,

this would be possible but why not simply using exists() here?

MainDatatab:

LOAD ...

WHERE not exists(code, CityCode)

- Ralf