Hi Everybody,

I've got a dotnet application (1.1) that works with a foxpro dbase (using
the oledb provider for FoxPro 8.0).

Most of the code works, But I've run across a strange problem:

When I run the following sql statement:

SELECT     fldscorpio.id AS POINT_ID, fu_id, fldunit.name as fu_name,
fldscorpio.name, fldscorpio.description,slavetype,  fldscorpio.dc_status
+IIF(fldscorpio.ac_fail,524288,000000)+fldscorpio.comm_fail*15 as status,
0 as channel,0 as slot, fldscorpio.address as address, fldscorpio.piu as
piu, 0 as base_type,
(fldscorpio.dc_status
+IIF(fldscorpio.ac_fail,256,000)+fldscorpio.comm_fail)>0 as FAULT
FROM         fldscorpio
  INNER JOIN fldunit ON fldunit.id=fldscorpio.fu_id
  LEFT OUTER JOIN userarea ON userarea.area=fldunit.area
WHERE (((? IS NULL) OR (slavetype=?))) AND  (((? IS NULL) OR (FAULT=?)))

UNION

SELECT fldpoint.POINT_ID as POINT_ID,  fldpoint.fu_id, fldunit.name as
fu_name, fldpoint.name, fldpoint.desc as description,0 as slavetype, 
fldpoint.status as status,
fldpoint.channel as channel,fldpoint.slot as slot, fldpoint.rack as address,
0 as piu,
fldpoint.base_type as base_type,
fldpoint.status >0 as FAULT
from fldpoint
  INNER JOIN fldunit ON fldpoint.fu_id=fldunit.id
  LEFT OUTER JOIN userarea ON userarea.area=fldunit.area
where (((? IS NULL) OR (slavetype=?))) AND  (((? IS NULL) OR (FAULT=?))) AND
fldpoint.slot >0 and not(exists(select id from fldscorpio where
fldpoint.slot=fldscorpio.id))

I get an error:
System.Data.OleDb.OleDbException: SQL: Column 'FAULT' is not found.

In the sql , in both statements I've got a calculated column called 'FAULT',
so why does it complain that it can't find column 'FAULT'?

Nadav

I did some more testing.

It looks like I can't use columns defined with AS inside the where clause.
If I replace the column name with the expression the query works.

Nadav

Show quoteHide quote

Hi Nadav,

First of all, I'd like to confirm my understanding of your issue.
According to your description, I understand that you get the error message
when using columns defined with AS inside the WHERE clause.
If I misunderstood anything here, please don't hesitate to correct me.

If the value contained in the column referenced by the column alias is the
result of a set function, it can not occur in the WHERE clause;
if we tried to do that, we will get the error message as below:
Invalid column name 'fault'.
For detailed information, you can refer to the following article According
to
http://publib.boulder.ibm.com/infocenter/rbhelp/v6r3/index.jsp?topic=/com.ib
m.redbrick.doc6.3/ssg/ssg32.htm.

I'm afraid that we can't use columns defined with AS inside the where
clause.
This is a restriction related to SQL Command.

For this reason, we suggest you can modify your select command as below:
SELECT fldscorpio.id AS POINT_ID, fu_id, fldunit.name as fu_name, 
fldscorpio.name, fldscorpio.description,slavetype, 
fldscorpio.dc_status+IIF(fldscorpio.ac_fail,524288,000000)+fldscorpio.comm_f
ail*15 as status,
0 as channel,0 as slot, fldscorpio.address as address, fldscorpio.piu as
piu, 0 as base_type,
(fldscorpio.dc_status+IIF(fldscorpio.ac_fail,256,000)+fldscorpio.comm_fail)>
0 as FAULT FROM fldscorpio
INNER JOIN fldunit ON fldunit.id=fldscorpio.fu_id
LEFT OUTER JOIN userarea ON userarea.area=fldunit.area
WHERE (((? IS NULL) OR (slavetype=?))) AND  (((? IS NULL) OR
(((fldscorpio.dc_status+IIF(fldscorpio.ac_fail,256,000)+fldscorpio.comm_fail
)>0)=?)))

If you have any question, please feel free to post here.

Wen Yuan
===============================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
===============================
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

Hi WenYuan,

Thanks for your reply.

I changed the select statement as you suggested and it works.

I knew I could solve this problem this way (i.e. copying the expression to
the where clause).
I just wanted to make sure that this behavior is 'as designed'.

Thanks

Nadav

Hi Nadav,

Yes, this is designed by SQL(Structured Query Language) Standard.
As you mentioned above, we should copy the expression to the where clause.

If there is anything unclear, please don't hesitate to post in the
newsgroup and we will follow up.

Sincerely,
Wen Yuan

Simple multiple table design question
Dot Net 2.0 or SQL Server 2005 problem?
Typed Dataset event handling problem
TableAdapter Preserve Original Values on Update (no Accept Changes
Binary Serialization -- how to actually insert into database?