SQL subquery not working in FP 2003

[jackle_usmc]

I keep getting an error message when I run a custom query in DRW. The
syntax is good because it works in query analyzer.

If I do a simple select statement like

"select field1, field2, (select field from table2 where
t1.field1=table2.field) from table1 as t1
where field1='text' "

everything is fine.

If I do

"select field1, field2, (select field from table2 where
t1.field1=table2.field) from table1 as t1
where field1='::text::' "

I get the error message "the custom query contains errors".

I am doing this via database result wizard and doing a custom query in
both cases.

 

[David Berry]

is "text" a literal or a variable name? What type of field is field1?

 

[jackle_usmc]

text is not a literal. I was using that as an example. The fields are
integers.

I've tried very simple select statements with the same results.
Anytime I use a parameter in my where clause, I get the error. If I
use an actual value in my where clause, I get the results I expect.

From an sql standpoint there is nothing wrong with my syntax. FP2K3

even says the syntax is right if I use a value instead of the
parameter.

 

[David Berry]

Sorry, I don't use the database wizard because it's not as flexible as
straight ASP Code. In ASP it would simply be:

where field1=" & text

 

[Kathleen Anderson [MVP - FrontPage]]

Try where field1 = ::text::

--

~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/

 

[jackle_usmc]

didn't work. Any other ideas? :-(