在sql查询中使用带有AND的'<'符号时遇到的问题

Question

I'm getting issue when I try to use < symbol with AND condition in sql query.

   WHERE (latitude - 0.009 < AND latitude + 0.00 >)
           AND (longitude - 0.009 < 
                AND longitude + 0.009 > )

Here is my code in CorePHP:

<?php
ini_set("display_errors", 0);

$lat = $_REQUEST['latit'];
$long = $_REQUEST['longit'];

include ("commanvar.php");
include ("class.db.php");

$coordinates_ = '';

// function to calculate distance between two latitudes and longitudes
function distance($lat1, $lng1, $lat2, $lng2, $miles = true)
{
    $pi80 = M_PI / 180;
    $lat1 *= $pi80;
    $lng1 *= $pi80;
    $lat2 *= $pi80;
    $lng2 *= $pi80;

    $r = 6372.797; // mean radius of Earth in km
    $dlat = $lat2 - $lat1;
    $dlng = $lng2 - $lng1;
    $a = sin($dlat / 2) * sin($dlat / 2) + cos($lat1) * cos($lat2) * sin($dlng / 2) * sin($dlng / 2);
    $c = 2 * atan2(sqrt($a), sqrt(1 - $a));
    $km = $r * $c;

    return ($miles ? $km * 0.621371192 : $km);
}

$obj = new db_connect();

// added by pramod

$sql = "SELECT name,
       latitude,
       longitude,
       TYPE
  FROM (SELECT name,
                TO_BINARY_FLOAT(latitude) latitude,
                TO_BINARY_FLOAT(longitude) longitude,
               TYPE
          FROM (SELECT name,
                       latitude,
                       longitude,
                       TYPE,
                       is_number (latitude) latisnum,
                       is_number (longitude) longisnum
                  FROM (SELECT name,
                               REGEXP_SUBSTR (latlon,
                                              '[^,]+',
                                              1,
                                              1)
                                  latitude,
                               REGEXP_SUBSTR (latlon,
                                              '[^,]+',
                                              1,
                                              2)
                                  longitude,
                               TYPE
                          FROM (SELECT olt.name,
                                       olt_details.latlon,
                                       'olt' AS TYPE
                                  FROM ftth.olt, ftth.olt_details
                                 WHERE olt_id = id
                                UNION
                                SELECT name, latlon, TYPE FROM ftth.splitters
                                ))
                 WHERE latitude IS NOT NULL AND longitude IS NOT NULL)
         WHERE latisnum = 1 AND longisnum = 1)
 WHERE (latitude - 0.009 < $lat
        AND latitude + 0.00 > $lat)
       AND (longitude - 0.009 < $long
            AND longitude + 0.009 > $long)";
//die();

$obj->db_query($sql);

// echo $sql;

// echo $lat . ',' . $long;
// define json array coordinates and prepare it's elements for returning via AJAX

$coordinates = '{
"coordinates": [';

while ($result = $obj->db_fetch_array(1)) {

    $latitude = $result['LATITUDE'];
    $longitude = $result['LONGITUDE'];
    $name = $result['NAME'];
    $type = $result['TYPE'];

    $latlon_fiber = $result['LATITUDE'] . ", " . $result['LONGITUDE'];

    $distance_fromswitch = distance($lat, $long, $latitude, $longitude, FALSE);
    $distance_fromswitch = floor($distance_fromswitch * 1000);

    $coordinates_ .= '{ "distance":"' . $distance_fromswitch . '" ,"site_name":"' . $name . '" , "latitude":"' . $latitude . '" , "longitude":"' . $longitude . '" , "device_type":"' . $type . '" },';
}

$coordinates .= rtrim($coordinates_, ',');

$coordinates .= "]}";

echo $coordinates;
$obj->free();
?>

I'm getting the following error due to this part < AND on line WHERE (latitude - 0.009 < AND latitude + 0.00 >)

ORA-00936: missing expression
00936. 00000 -  "missing expression"

I think I've used some improper syntax while using < with AND on line WHERE (latitude - 0.009 < AND latitude + 0.00 >)

What can be the correction that need to be done here?

Answer 1

Think of it! The error message is self-explanatory.

You're trying to compare the result of subtraction to nothing. To correct this, you have to change it to:

WHERE (latitude - 0.009 < SomeValueHere AND latitude + 0.00 > SomeValueHere)

For further details, please see: ORA-00936 missing expression

[EDIT]

As to changes made in the question...

You're asking for help in debugging (inspect) your code. Let me quote the comment to your comment:

probably the variables do not have any values and passing empty strings into your query.. – skybunk

Thank you, @skybunk!