Problem with condition in Where Clause when using a primary key (Field) subject to an alias

[#1]

Hi all,

I have to select only employee that have( EmployID >120) , data is loaded from Access database and this is my load :

Load

EmployID as BusEmployID,// alias

Name,

LastName

SQL SELECT *

FROM Employee;Where BusEmployID > 120;

My problem is :           1/ When I use the alias name (BusEmployID) the load didn't take the condition on considiration and return all the employee even those how have a BusEmployID > 120 ?

                                   2/ When I use the 'EmployID' , it didn't work because this fiels has already changed with the alias.

                                   3/when I delete the alias it work well.

This alias is mondatory for the concatination betwwen tables so I have to work with it .

Did you have explanation for that and how can I resolve it !

Thanks a lot

[oknotsen]

1: You do not use the ALIAS name.

2: You apparently did not test it or pulled the wrong conclusion as the name change takes place in the preceding load (in other words, after the data is pulled in memory). So you should use EmployID.

What mainly goes wrong in your piece of script is the 1 too many semi-colon:

FROM Employee;Where ...

[#3]

sorry it was a  wrong typing but my load is

Load

EmployID as BusEmployID,// alias

Name,

LastName

SQL SELECT *

FROM Employee

Where BusEmployID > 120 ;

I tested these three precedent cases , is worked well only when I tested without alias (case 3 ) .

[Kushal_Chawda]

try this

load :

Load

EmployID as BusEmployID,// alias

Name,

LastName

SQL SELECT *

FROM Employee;Where EmployID> 120;

As you are doing preceding load, first SQL statement executes, so BusEmployID in Where clause will not work and it will throw the error. So you should use actual Fieldname from SQL table which is EmployID. Then using preceding load you can give alias to EmployID

[#5]

I Tested it but it returned all the EmployID (it Didn't take in consideration the condition) ???

[karthickv]

You need to use EmployID in your where condition as like below.

Load

EmployID as BusEmployID,

Name,

LastName

SQL SELECT *

FROM Employee Where EmployID > 120;

Also like mentioned by Onno van, I suspect your issue is with multiple semicolon only (as you mentioned in #2 that u have tried using EmployID.).

[oknotsen]

Still the same comment as I did on your initial code:

You apparently did not test it or pulled the wrong conclusion as the name change takes place in the preceding load (in other words, after the data is pulled in memory). So you should use EmployID.

Or in different words:

BusEmployID does not exist until AFTER this complete piece of code is finished, so you can NOT user it in the where clause. You need to use the initial field name, in this case EmployID.

[#8]

this is the resalt with the 'EmployID' as you see the system return 1,2,3 ?

[#9]

Yes it was a wrong typing ,

and for this

Load

EmployID as BusEmployID,

Name,

LastName

SQL SELECT *

FROM Employee Where EmployID > 120;

it return this result and as you see 1,2,

3,4.... shouldn't be exist,

[oknotsen]

I suggest you do some research about that column. I think it contains spaces or other (not visible) symbols. The aligning of the values shows that they are not all considered "numbers".

See if you can load the field like this:

'|' & EmployID & '|' as BusEmployID

Analyze the field after that.