6 Replies Latest reply on Jul 13, 2007 1:08 PM by Newsgroup_User

    query problem

    Nick201 Level 1
      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
        • 1. Re: query problem
          cf_dev2 Level 1
          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)
          • 2. query problem
            paross1 Level 2
            entered in error
            • 3. Re: query problem
              Nick201 Level 1
              I am not sure what you trying to say. Could you please show me in my query
              • 4. Re: query problem
                Dan Bracuk Level 5
                In your query, between this
                * 3963
                and the closing bracket, put in a comma and the number of decimal places you want.
                • 5. Re: query problem
                  Level 7
                  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.

                  • 6. Re: query problem
                    Nick201 Level 1
                    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>