SEO

Hello,

I'm new to stored procedures and I'm having a bit of trouble figuring
out how to pass a declared variable into an IN statement.

The purpose of this procedure is to populate a table with zip codes
within set distances (0.5, 1, 2, 5, 10, 20, 50, 100 miles). I store
them in a text field like so: '06119',06107'. For future searches I'll
reference this field instead of running a distance calculation on the
entire US/Canada zip code table (this is too slow).

I set up a table with the appropriate fields and the first statement
in the loop works fine. It populates the d_100 field with all the zips
separated with ','.

For the following statements I'd like to pass the results of the
previous statement in the where clause. For example, once I figure out
all zip codes within 100 miles, I only want to run the statement for
50 miles on the records that have been defined as within 100 miles. To
do so I add 'AND postalCode IN (previous_var)'. The previous_var
contains the correct syntax for the IN and is populated successfully
but it doesn't work. If I recreate the statement in the query browser
it works fine.

Does anyone know how I can get this to work? This would save me
literally days of processing time. Each distance search on the entire
table takes about 5-6 seconds and it's significantly less if I limit
the scope.

Below is the full procedure, but here's a simple procedure showing the
same problem (The update doesn't work, no errors reported).

postalCode is a varchar(7)

CREATE PROCEDURE zip_test()
BEGIN
DECLARE test TEXT;
SET test = "'06119','11206'";
UPDATE zipcodes_distances SET updated=1 WHERE postalCode IN (test);
SELECT test;
END

UPDATE zipcodes_distances SET updated=1 WHERE postalCode IN
('06119','11206'); works as expected in the query browser.

Full procedure:

CREATE PROCEDURE zip_calcDistance()
BEGIN
DECLARE zip VARCHAR(7);
DECLARE csv_05 TEXT;
DECLARE csv_1 TEXT;
DECLARE csv_2 TEXT;
DECLARE csv_5 TEXT;
DECLARE csv_10 TEXT;
DECLARE csv_20 TEXT;
DECLARE csv_50 TEXT;
DECLARE csv_100 TEXT;
DECLARE done INT DEFAULT 0;
DECLARE src_lat DOUBLE;
DECLARE src_long DOUBLE;

DECLARE postalCodes
CURSOR FOR
SELECT postalCode
FROM zipcodes_distances
WHERE updated=0 limit 1;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

OPEN postalCodes;
WHILE NOT done DO
FETCH postalCodes INTO zip;

SELECT latitude, longitude INTO src_lat, src_long FROM zipcodes WHERE
postalcode = zip;

SELECT CONCAT("'",GROUP_CONCAT(postalCode SEPARATOR "','"),"'") INTO
csv_100 FROM zipcodes WHERE (POW((69.1 * (longitude - src_long) *
COS(src_lat / 57.3)), 2) + POW((69.1 * (latitude - src_lat)), 2)) <=
(100 * 100);

SELECT CONCAT("'",GROUP_CONCAT(postalCode SEPARATOR "','"),"'") INTO
csv_50 FROM zipcodes WHERE (POW((69.1 * (longitude - src_long) *
COS(src_lat / 57.3)), 2) + POW((69.1 * (latitude - src_lat)), 2)) <=
(50 * 50) AND postalCode IN ( csv_100 );

SELECT CONCAT("'",GROUP_CONCAT(postalCode SEPARATOR "','"),"'") INTO
csv_20 FROM zipcodes WHERE (POW((69.1 * (longitude - src_long) *
COS(src_lat / 57.3)), 2) + POW((69.1 * (latitude - src_lat)), 2)) <=
(20 * 20) AND postalCode IN ( csv_50 );

SELECT CONCAT("'",GROUP_CONCAT(postalCode SEPARATOR "','"),"'") INTO
csv_10 FROM zipcodes WHERE (POW((69.1 * (longitude - src_long) *
COS(src_lat / 57.3)), 2) + POW((69.1 * (latitude - src_lat)), 2)) <=
(10 * 10) AND postalCode IN ( csv_20 );

SELECT CONCAT("'",GROUP_CONCAT(postalCode SEPARATOR "','"),"'") INTO
csv_5 FROM zipcodes WHERE (POW((69.1 * (longitude - src_long) *
COS(src_lat / 57.3)), 2) + POW((69.1 * (latitude - src_lat)), 2)) <=
(5 * 5) AND postalCode IN ( csv_10 );

SELECT CONCAT("'",GROUP_CONCAT(postalCode SEPARATOR "','"),"'") INTO
csv_2 FROM zipcodes WHERE (POW((69.1 * (longitude - src_long) *
COS(src_lat / 57.3)), 2) + POW((69.1 * (latitude - src_lat)), 2)) <=
(2 * 2) AND postalCode IN ( csv_5 );

SELECT CONCAT("'",GROUP_CONCAT(postalCode SEPARATOR "','"),"'") INTO
csv_1 FROM zipcodes WHERE (POW((69.1 * (longitude - src_long) *
COS(src_lat / 57.3)), 2) + POW((69.1 * (latitude - src_lat)), 2)) <=
(1 * 1) AND postalCode IN ( csv_2 );

SELECT CONCAT("'",GROUP_CONCAT(postalCode SEPARATOR "','"),"'") INTO
csv_05 FROM zipcodes WHERE (POW((69.1 * (longitude - src_long) *
COS(src_lat / 57.3)), 2) + POW((69.1 * (latitude - src_lat)), 2)) <=
(0.5 * 0.5) AND postalCode IN ( csv_1 );

UPDATE zipcodes_distances SET
d_05=csv_05,d_1=csv_1,d_2=csv_2,d_5=csv_5,d_10=csv _10,d_20=csv_20,d_50=csv_50,d_100=csv_100,updated= 1
WHERE postalCode=zip;

END WHILE;
CLOSE postalCodes;
END

not sure if this is what u want but I am also trying to do a select
based on a value from the previous select. IA cursor didnt work, so I
used a plain old LOOP.

Here is my loop code:
region_loop: LOOP

SELECT regionName, regionParentID
INTO v_name, v_parentID
FROM region
WHERE regionID = i_regionID;

IF (flag_last_row=-1) THEN
LEAVE region_loop;
END IF;

IF (v_catName = '-1') THEN
SET v_catName = v_name;
ELSE
set v_catName = CONCAT(v_name,'-',v_catName);
END IF;

SET i_regionID = v_parentID;

END LOOP region_loop;

RETURN v_catName + cnt;

Hope this helps
Jeff







On Tue, 19 Jun 2007 16:24:09 -0700, JRigby <jeff.rigby@gmail.com>
wrote:

>Hello,
>
>I'm new to stored procedures and I'm having a bit of trouble figuring
>out how to pass a declared variable into an IN statement.
>
>The purpose of this procedure is to populate a table with zip codes
>within set distances (0.5, 1, 2, 5, 10, 20, 50, 100 miles). I store
>them in a text field like so: '06119',06107'. For future searches I'll
>reference this field instead of running a distance calculation on the
>entire US/Canada zip code table (this is too slow).
>
>I set up a table with the appropriate fields and the first statement
>in the loop works fine. It populates the d_100 field with all the zips
>separated with ','.
>
>For the following statements I'd like to pass the results of the
>previous statement in the where clause. For example, once I figure out
>all zip codes within 100 miles, I only want to run the statement for
>50 miles on the records that have been defined as within 100 miles. To
>do so I add 'AND postalCode IN (previous_var)'. The previous_var
>contains the correct syntax for the IN and is populated successfully
>but it doesn't work. If I recreate the statement in the query browser
>it works fine.
>
>Does anyone know how I can get this to work? This would save me
>literally days of processing time. Each distance search on the entire
>table takes about 5-6 seconds and it's significantly less if I limit
>the scope.
>
>Below is the full procedure, but here's a simple procedure showing the
>same problem (The update doesn't work, no errors reported).
>
>postalCode is a varchar(7)
>
>CREATE PROCEDURE zip_test()
>BEGIN
> DECLARE test TEXT;
> SET test = "'06119','11206'";
> UPDATE zipcodes_distances SET updated=1 WHERE postalCode IN (test);
> SELECT test;
>END
>
>UPDATE zipcodes_distances SET updated=1 WHERE postalCode IN
>('06119','11206'); works as expected in the query browser.
>
>Full procedure:
>
>CREATE PROCEDURE zip_calcDistance()
>BEGIN
>DECLARE zip VARCHAR(7);
>DECLARE csv_05 TEXT;
>DECLARE csv_1 TEXT;
>DECLARE csv_2 TEXT;
>DECLARE csv_5 TEXT;
>DECLARE csv_10 TEXT;
>DECLARE csv_20 TEXT;
>DECLARE csv_50 TEXT;
>DECLARE csv_100 TEXT;
>DECLARE done INT DEFAULT 0;
>DECLARE src_lat DOUBLE;
>DECLARE src_long DOUBLE;
>
>DECLARE postalCodes
>CURSOR FOR
>SELECT postalCode
>FROM zipcodes_distances
>WHERE updated=0 limit 1;
>
>DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
>
>OPEN postalCodes;
>WHILE NOT done DO
>FETCH postalCodes INTO zip;
>
>SELECT latitude, longitude INTO src_lat, src_long FROM zipcodes WHERE
>postalcode = zip;
>
>SELECT CONCAT("'",GROUP_CONCAT(postalCode SEPARATOR "','"),"'") INTO
>csv_100 FROM zipcodes WHERE (POW((69.1 * (longitude - src_long) *
>COS(src_lat / 57.3)), 2) + POW((69.1 * (latitude - src_lat)), 2)) <=
>(100 * 100);
>
>SELECT CONCAT("'",GROUP_CONCAT(postalCode SEPARATOR "','"),"'") INTO
>csv_50 FROM zipcodes WHERE (POW((69.1 * (longitude - src_long) *
>COS(src_lat / 57.3)), 2) + POW((69.1 * (latitude - src_lat)), 2)) <=
>(50 * 50) AND postalCode IN ( csv_100 );
>
>SELECT CONCAT("'",GROUP_CONCAT(postalCode SEPARATOR "','"),"'") INTO
>csv_20 FROM zipcodes WHERE (POW((69.1 * (longitude - src_long) *
>COS(src_lat / 57.3)), 2) + POW((69.1 * (latitude - src_lat)), 2)) <=
>(20 * 20) AND postalCode IN ( csv_50 );
>
>SELECT CONCAT("'",GROUP_CONCAT(postalCode SEPARATOR "','"),"'") INTO
>csv_10 FROM zipcodes WHERE (POW((69.1 * (longitude - src_long) *
>COS(src_lat / 57.3)), 2) + POW((69.1 * (latitude - src_lat)), 2)) <=
>(10 * 10) AND postalCode IN ( csv_20 );
>
>SELECT CONCAT("'",GROUP_CONCAT(postalCode SEPARATOR "','"),"'") INTO
>csv_5 FROM zipcodes WHERE (POW((69.1 * (longitude - src_long) *
>COS(src_lat / 57.3)), 2) + POW((69.1 * (latitude - src_lat)), 2)) <=
>(5 * 5) AND postalCode IN ( csv_10 );
>
>SELECT CONCAT("'",GROUP_CONCAT(postalCode SEPARATOR "','"),"'") INTO
>csv_2 FROM zipcodes WHERE (POW((69.1 * (longitude - src_long) *
>COS(src_lat / 57.3)), 2) + POW((69.1 * (latitude - src_lat)), 2)) <=
>(2 * 2) AND postalCode IN ( csv_5 );
>
>SELECT CONCAT("'",GROUP_CONCAT(postalCode SEPARATOR "','"),"'") INTO
>csv_1 FROM zipcodes WHERE (POW((69.1 * (longitude - src_long) *
>COS(src_lat / 57.3)), 2) + POW((69.1 * (latitude - src_lat)), 2)) <=
>(1 * 1) AND postalCode IN ( csv_2 );
>
>SELECT CONCAT("'",GROUP_CONCAT(postalCode SEPARATOR "','"),"'") INTO
>csv_05 FROM zipcodes WHERE (POW((69.1 * (longitude - src_long) *
>COS(src_lat / 57.3)), 2) + POW((69.1 * (latitude - src_lat)), 2)) <=
>(0.5 * 0.5) AND postalCode IN ( csv_1 );
>
>UPDATE zipcodes_distances SET
>d_05=csv_05,d_1=csv_1,d_2=csv_2,d_5=csv_5,d_10=cs v_10,d_20=csv_20,d_50=csv_50,d_100=csv_100,updated =1
>WHERE postalCode=zip;
>
>END WHILE;
>CLOSE postalCodes;
>END