where Exists()

[Ranjanac]

I want the out put : Employee name & salary where (Salary) >20000

my code:

Employee: //* Loading table

LOAD * inline [

Ename,ID,Salary

Bill,001,20000

Ranjana,002,30000

Lakshya,003,35000
];

EmployeeAddresses:

Load

Ename as Name,

Salary

Resident Employee where Exists (Salary) >20000;

but this code will show all the existing values.. 

Did this load script work ? How to get the correct output.? 

[marcus_sommer · Accepted solution]

Try it with:

LOAD * inline [

Ename,ID,Salary

Bill,001,20000

Ranjana,002,30000

Lakshya,003,35000
] where Salary > 20000;

[Tanish]

HI,

EmployeeAddresses:

Load

Ename as Name,

Salary

Resident Employee where Salary >20000;

Don't use Exists function...

and I can see two tables in your  DataModel, if u need it then fine, if not then write Drop Statement.

For Example,

Drop table Employee;

[Ranjanac]

it works with "where Salary > 20000" Thanks.

But, where exactly we're using where Exists ? how can we optimized this query ?

[marcus_sommer]

Exists() evaluates if this field-value exists within the loaded data and is not directly related against the current load else also all previous loaded field-values. For this it looked within the system-table if the value is there or not and returning a TRUE or FALSE. Any other evaluation like >= <= or whatever isn't possible with this function.

Nevertheless this feature could be often applied for tasks like yours - by preparing appropriate data in beforehand. In your case maybe with something like:

temp: load 20000 + (recno() * 1000) as Salary autogenerate 100;

LOAD * inline [

Ename,ID,Salary

Bill,001,20000

Ranjana,002,30000

Lakshya,003,35000
] where exists(Salary);

Depending on the possible variety of values the data-generation part in beforehand may need some more logic and efforts. By rather small tables and/or other sources as qvd- or resident-loads (without any other processing) the performance impact would be not really significant.