query to query

[Guest]

it is more complicate to describe why i have to do this but it's the only way just an example below.

My query to give me anthing from the table when i do the query to query to get what i want but when i use the left string function then i got an error below.

Query Of Queries syntax error.

Encountered "left. Incorrect conditional expression, Expected one of [like|null|between|in|comparison] condition,

thanks

<cfquery name="type" datasource="#dsn#">

select * from [dbo].[requestType]

</cfquery>

results look like

type:

=====

ADD

REMYU

RECD

I want to do the query to query to get the REMYU

<cfquery name="find" dbtype="query">

select * from type

where left(type,3) = 'rem'

</cfquery>

<cfdump var="#find#">

[Carl Von Stetten]

The left() function is typically a SQL function, and is not available in Query-of-Query syntax.  You could rewrite your first query to be more specific:

<cfquery name="type" datasource="#dsn#">

select * from [dbo].[requestType]

where left(type,3) = 'rem'

</cfquery>

Otherwise, you could use the LIKE operator instead:

<cfquery name="find" dbtype="query">

select * from type

where type LIKE 'rem%'

</cfquery>

-Carl V.

[Guest]

sorry it was mistype, it suppose to be where left(type,3) != 'rem'

then how can i peform the like fc

<cfquery name="find" dbtype="query">

select * from type

where type NOT  LIKE 'rem%' ??

</cfquery>

[Carl Von Stetten]

Yes, NOT LIKE should work.

-Carl V.

[Guest]

thank you,