query problem

[Guest]

I'm getting error message in this query not sure why? The round function requires 2 to 3 arguments.
SELECT zipcode,
latitude,
longitude,
state,
city,
ROUND((ACOS((SIN(+42.019093/57.2958) * SIN(latitude/57.2958)) + (COS(+42.019093/57.2958) * COS(latitude/57.2958) * COS(longitude/57.2958 - -088.178475/57.2958)))) * 3963) AS distance
FROM zip_codes
WHERE (latitude >= +42.019093 - (5/111))
And (latitude <= +42.019093 + (5/111))
AND (longitude >= -088.178475 - (5/111))
AND (longitude <= -088.178475 + (5/111))
ORDER BY distance

thanx

[cf_dev2]

You must also supply a length/precision value. Its used to indicate how ms sql should round the value.

SELECT ROUND( theValue, theLength ) ... or
SELECT ROUND( 12.578, 1)

[paross1]

entered in error

[Guest]

I am not sure what you trying to say. Could you please show me in my query

[Dan_Bracuk]

In your query, between this
* 3963
and the closing bracket, put in a comma and the number of decimal places you want.

[Newsgroup_User]

Nick201 wrote:
> I am not sure what you trying to say. Could you please show me in my query

ROUND((ACOS((SIN(+42.019093/57.2958) * SIN(latitude/57.2958)) +
(COS(+42.019093/57.2958) * COS(latitude/57.2958) *
COS(longitude/57.2958- -088.178475/57.2958)))) * 3963)

Your Round() function wants two or three parameters, you are only
providing it one; a fairly complex formula to provide a float to be
rounded. The function wants to know at least to what precision (decimal
point) to round it.

[Guest]

here is my code not getting right result.
--------------------------------------------------------------------
<CFPARAM name="errormessage" type="string" default="">
<CFPARAM name="passedzipcode" type="string" default="78626">

<cfquery name="passedzip" datasource="TEST">
SELECT * FROM zip_codes
WHERE ZIPcode=#passedzipcode#
</cfquery>

<cfif passedzip.recordcount eq 0>
<cfset errormessage = "No records found for passed search #passedzipcode#">
<cfabort>
</cfif>

<cfif errormessage eq ''>

<cfquery datasource="TEST" name="getlocs">
SELECT zipcode, latitude, longitude, state, city,
ROUND((ACOS((SIN(#passedzip.latitude#/57.2958) * SIN(latitude/57.2958)) +
(COS(#passedzip.latitude#/57.2958) * COS(latitude/57.2958) *
COS(longitude/57.2958 - #passedzip.longitude#/57.2958))))
* 3963,15) AS distance
FROM zip_codes
WHERE (latitude >= #passedzip.latitude# - (#passedradius#/111))
And (latitude <= #passedzip.latitude# + (#passedradius#/111))
AND (longitude >= #passedzip.longitude# - (#passedradius#/111))
AND (longitude <= #passedzip.longitude# + (#passedradius#/111))
ORDER BY distance
</cfquery>

</cfif>


<CFPARAM name="dsname" type="string" default="your datasource name">
<CFPARAM name="passedzipcode" type="string" default="78626">
<CFPARAM name="passedradius" type="string" default="20">
<CFPARAM name="passedreturndataformat" type="string" default="table">
<CFPARAM name="passedreturneddata" type="string" default="">

<CFPARAM name="rowonecolor" type="string" default="whitesmoke">
<CFPARAM name="rowtwocolor" type="string" default="white">
<CFPARAM name="rowthreecolor" type="string" default="000080">

<cfif passedreturndataformat eq 'table'>
<cfset passedreturneddata = passedreturneddata & "<table width=55% border=0 cellpadding=0 cellspacing=0>
<!--- this is used to make the headers of the table colored --->

<tr>
<td valign=""center"" align=""center"" bgcolor=""#rowthreecolor#""><font color=""#rowtwocolor#"">City</font></td>
<td valign=""center"" align=""center"" bgcolor=""#rowthreecolor#""><font color=""#rowtwocolor#"">State</font></td>
<td valign=""center"" align=""center"" bgcolor=""#rowthreecolor#""><font color=""#rowtwocolor#"">ZipCode</font></td>
<td valign=""center"" align=""center"" bgcolor=""#rowthreecolor#""><font color=""#rowtwocolor#"">Distance</font></td>
</tr>">


<cfoutput query="getlocs">
<!--- this is used to make the headers of the table colored --->
<!--- cf_bgcolortoggle can be downloaded from cfmsource.com --->


<cfset passedreturneddata = passedreturneddata & "
<tr>
<td align=""center"" >#city#</td>
<td align=""center"" >#state#</td>
<td align=""center"" >#zipcode#</td>
<td align=""center"">#round(distance)#</td>
</tr>">


</cfoutput>
<cfset passedreturneddata = passedreturneddata & "
</table>">
<cfset errormessage = "">
<cfelse>
<cfset passedreturneddata = "">
<cfoutput query="getlocs">
<cfif passedreturneddata gt "">
<cfset passedreturneddata = passedreturneddata & "|#city#,#state#,#zipcode#,#round(distance)#">
<cfelse>
<cfset passedreturneddata = passedreturneddata & "#city#,#state#,#zipcode#,#round(distance)#">
</cfif>
</cfoutput>
<cfset errormessage = "">
</cfif>


<cfoutput>
<cfif errormessage gt ''>
#errormessage#
<cfelse>
#passedreturneddata#
</cfif>
</cfoutput>