Why does this WHERE clause result in Script Error?

[#1]

Hello,

I'm trying to read users from the Active Directory. Since there are so many records in the AD, I'm trying to add a WHERE clause to limit the number returned.

I can run the following script in debug mode, set number of recs to bring back to 500 and it works fine.

However, if I add the following WHERE statement I get a Script Error

   WHERE length > 0

Can anyone tell me why that WHERE statement would cause a Script error?

Thanks in advance for any help.

[its_anandrjs · Accepted solution]

You have to write

Len(mail) > 0 in place of the length because it is not identified because length field is identified by Len(mail) if you read this in another table then it is identified. Also place this line in before SQL like

Load Distinct

Fields

..

Len(mail) as length

where Len(mail) > 0;

SQL select

Fields

From location

[tresesco]

Possibly, you have to write it like:

Where Len(mail)>0;    

Because the field Length is not yet created in your source data. Else use the same where condition in the qv load part.

[nizamsha]

Changes

Last Time i made some mistake now i corrected that one

where  Len(Trim(mail))>0;

[tresesco]

Nizam, if trim() has to be used, it would be Len(Trim(mail)) instead.

[its_anandrjs]

Yes it will be  Trim  and then Len function but not required here.

[nizamsha]

srry for that

thanks u very much  for reminding me,  i miss typed that  one

[#8]

Anand,

Thanks for your response. Your suggestions worked. The part about not using the new field name in the WHERE clause makes sense. However, the part about putting the WHERE clause before the SQL key word doesn't.

I mean, it works but I don't understand why. Every example I've seen always has the WHERE clause after the FROM clause inside the SQL keyword. Even the help system says to

Even the help system indicates that the WHERE comes after FROM. Strange.

[its_anandrjs]

See like from any SAP tables or may be SQL tables when we read the tables that has two parts one for SQL and another one for the QLIkview like a preceding load. So if we put the Where clause before the SQL will read by the qlikview table it depends on from which source we are reading is some thing like first we read the table and then filter the table with our requirements.

[#10]

Interesting. Good to know. Thanks!