Remote view logical field problem

[aharrisreid]

I am trying to create a remote view which selects all fields from a SQL table, plus an extra logical field (not related to any back-end field).

Using native tables, I could use syntax like...

SELECT *, .F. AS MyNewField ;
FROM MySQLTable ;
WHERE MyFilterCondition

but I get an 'incorrect syntax near AS...' ODBC error message. If I use '0 as MyNewField' it defaults to an integer field and if I then try and change the field data-type to logical, I get the error message 'Type conversion required by the DataType property for field "MyField" is invalid' (Error 1543).

There are other bit fields in the backend table which translate fine to logical fields, so how can I add a logical field to my field list, or am I restricted to using 1/0 as an integer?

Many thanks,
Alan

 

[MarciaAkins]

Hi Alan.

>> SELECT *, .F. AS MyNewField ;
FROM MySQLTable ;
WHERE MyFilterCondition <<

You should be able to use the syntax .F. as MyNewField to define your remote view. ODBC makes the translation between the logical VFP field and the BIT field in SQL Server.

I think that your problem is the fact that you do not have the "*" aliased like so:

MySQLTable.*




Marcia G. Akins

http://www.TightlineComputers.com

 

[aharrisreid]

Hi Marcia.

>You should be able to use the syntax .F. as MyNewField to >define your remote view. ODBC makes the translation >between the logical VFP field and the BIT field in SQL >Server.
>I think that your problem is the fact that you do not >have the "*" aliased like so:
>MySQLTable.*

Unfortunately prefixing the * with the table name still produces the 'incorrect syntax near 'AS' ODBC error.

Over on the UT, Sergey suggested...
SELECT *, CAST(0 AS bit) AS MyNewField FROM ...

Seems to do the trick.

Regards,
Alan

 

[MarciaAkins]

Hi Alan.

>> SELECT *, CAST(0 AS bit) AS MyNewField FROM ... <<

You are correct. You need to put the SELECT in syntax that the back end can understand. My mistake - I think I answered the wrong question ;-).

What I meant was that when you select a bit field from SQL server, ODBC automatically does the translation to a logical VFP field for you.

Sorry about that.



Marcia G. Akins

http://www.TightlineComputers.com