Why does this basic select query throw exception in foxpro?

Question

My simplified query is as follows.

select CustomerName as custname from customers where custname='Hey'

It says Column custname is not found. How can I handle this?

In sql it does not throw any exception and accepts custname as a column.

(In fact my query is more like select IIF(...,...,...) as name from customers where custname='Hey' than the above one.)

`name` is a `pseudo column`. A pseudo column cannot be used in inserts or other operations — Bala, Nov 21 '14 at 13:25
@Bala Interestingly I can use custname in `group by` clause. — serdar, Nov 21 '14 at 13:31
Please do not edit the original query, How is the original field named (the one on the database)? — ericpap, Nov 21 '14 at 13:32
@ericpap This is my original query. `select IIF(FPOLDOVTiP=0,99,FPOLDOVTiP) AS doviz from pol where doviz =99` — serdar, Nov 21 '14 at 13:35
@sendar you are showing a different query now! also a different table! please be more clear in what do you need help with! — ericpap, Nov 21 '14 at 13:37
@ericpap no, all the queries throws exception. When my original wury throwed exception I simplified it and still throwed exception. — serdar, Nov 21 '14 at 13:38
Can one of the downvotes tell the reason? Off-topic? Or my problem does not exist in foxpro? — serdar, Nov 21 '14 at 13:43
The WHERE clause operates on the original tables. GROUP BY and HAVING operate on intermediate results. — Tamar E. Granor, Nov 21 '14 at 21:25

ericpap · Accepted Answer · 2014-11-21T13:47:15.153

0

Try change the column name. In many languages the "name" keyword is reserved

Edit

With the new query, try this:

select IIF(FPOLDOVTiP=0,99,FPOLDOVTiP) AS doviz from pol where IIF(FPOLDOVTiP=0,99,FPOLDOVTiP)=99

You can't use calculated fields on where clause.

edited Nov 21 '14 at 13:47

answered Nov 21 '14 at 13:25

ericpap

You are right. Looks like name is a reserved word http://msdn.microsoft.com/en-us/library/xztfc506%28v=vs.80%29.aspx – Bala Nov 21 '14 at 13:27
ok worked this time, thanks. Will this have a performance effect? Calculating twice? – serdar Nov 21 '14 at 13:45
Yes but the most performace effect will be to use a where on a calculated field. You can't index that search so it will have a perfomance impact for large tables. In that case You should consider change your table structure or the way you are saving data to avoid using calculated fields. – ericpap Nov 21 '14 at 13:49

1 Answers1