What's Wrong with this Query Statement

[scripter73]

Hi,

I have a query as follows:



<cfquery name=&quot;agency&quot; datasource=&quot;#SESSION.DSN#&quot; maxrows=20>
select agcy_agent_code,
agcy_agt_corp_name,
agcy_master_agent,
agcy_principal_name,
agcy_entity_type
from AGAGENCY
where (agcy_master_agent <> '' and Substr('#agcy_agent_code#',1,2) = '10')
</cfquery>


The datasource resolves and it grabs the fields, etc. It is the partial compare. I want to grab all records where one field (which is a text field) begins with 10.It hangs on the #agcy_agent_code# part.

I have tried all different combinations. I've tried putting quote around #agcy_agent_code# and the '10', I've tried taking them out, but nothing works.

The error message I keep getting is an Error Diagnostic,


Error resolving parameter AGCY_AGENT_CODE


ColdFusion was unable to determine the value of the parameter. This problem is very likely due to the fact that either:

You have misspelled the parameter name, or
You have not specified a QUERY attribute for a CFOUTPUT, CFMAIL, or CFTABLE tag.


The error occurred while processing an element with a general identifier of (#agcy_agent_code#), occupying document position (16:45) to (16:61).


Please help.

Thanks,
scripter73
Change Your Thinking, Change Your Life.

 

[shammack]

The error message seems to suggest that you have not assigned a value to the variables AGCY_AGENT_CODE. Is this really a variable, or is it the name of a column in your table? The syntax in the SUBSTR command is probably wrong. Try this if AGCY_AGENT_CODE is actually a column name

select agcy_agent_code,
agcy_agt_corp_name,
agcy_master_agent,
agcy_principal_name,
agcy_entity_type
from AGAGENCY
where (agcy_master_agent <> '' and Substr(agcy_agent_code,1,2) = '10')

If AGCY_AGENT_CODE is a CF variable, you should be using a CF string function like MID.

 

[webmigit]

Try Changing:

where (agcy_master_agent <> '' and Substr(agcy_agent_code,1,2) = '10')

To:

where (agcy_master_agent <> '' and agcy_agent_code like &quot;10%&quot;

Left() could also work:

where (agcy_master_agent <> '' AND Left(agcy_agent_code,2)=10)

 

[scripter73]

Thanks shammack and webmigit for all of your suggestions,

Hmmm... Let's see.

In the past, I've tried to incorporate CFML functions like Left() and Mid into my SQL statements and none of them have been successful. I don't think its possible. For example, I tried the following and it complains about the &quot;=&quot; operand in the latter part of the where statement.


<cfquery name=&quot;agency&quot; datasource=&quot;#SESSION.DSN#&quot;>
select agcy_agent_code,
agcy_agt_corp_name,
agcy_master_agent,
agcy_principal_name,
agcy_entity_type
from AGAGENCY
where agcy_master_agent = ' ' and Left(agcy_agent_code,2)=10

</cfquery>



However, I tried what you suggested yesterday webmigit, which is

select .... blah-blah .... where agcy_agent_code like '10%'

And that worked great. Thanks again for your help and if you guys figure out how to include CFML logic within a SQL query, give me a shout!

Thanks,
scripter73
Change Your Thinking, Change Your Life.

 

[scripter73]

Shammack, Webmigit!

I figured it out. Its all in the quotes. This works:

<cfquery name=&quot;agency&quot; datasource=&quot;#SESSION.DSN#&quot;>
select agcy_agent_code,
agcy_agt_corp_name,
agcy_master_agent,
agcy_principal_name,
agcy_entity_type
from AGAGENCY
where agcy_master_agent = ' ' and Left(agcy_agent_code,2)='10'

</cfquery>

Thanks again,
scripter73
Change Your Thinking, Change Your Life.