-
PL/SQL Zip code distance calc
I am moving an application from Sybase to Oracle. I have about 2 hours experience with Oracle so I need some help identifying what the issue is with this SQL. Please don't beat me up to much…
I am getting the following error with this SQL...
Code:
========================== ERROR ===============================
ORA-06550
PLS-00103
[Begins with the first select]
========================== END ERROR ===========================
Code:
========================== CODE PL/SQL ==========================
DECLARE
lng FLOAT;
lat FLOAT;
distance NUMBER := 500;
zip VARCHAR(64) := '19464';
BEGIN
SELECT (((SELECT DISTINCT a.LONGITUDE FROM W6ADMIN.W6ZIPDATA a WHERE a.zipcode=zip)*180)/3.14) INTO lng;
SELECT (((SELECT DISTINCT a.LATITUDE FROM W6ADMIN.W6ZIPDATA a WHERE a.zipcode=zip)*180)/3.14) INTO lat;
SELECT a.*,
POWER((POWER((lat- (W6ADMIN.W6ZIPDATA.LATITUDE*180)/3.14),2)+POWER((lng-(W6ADMIN.W6ZIPDATA.LONGITUDE*180)/3.14),2)),.5) AS Distance
FROM W6ADMIN.W6ENGINEERS a, W6ADMIN.W6ZIPDATA
WHERE RTRIM(a.POSTCODE)=RTRIM(W6ADMIN.W6ZIPDATA.zipcode) AND
(POWER((POWER((lat-(W6ADMIN.W6ZIPDATA.LATITUDE*180)/3.14),2)+POWER((lng-(W6ADMIN.W6ZIPDATA.LONGITUDE*180)/3.14),2)),.5)) <= distance
ORDER BY POWER((lat-(W6ADMIN.W6ZIPDATA.LATITUDE*180)/3.14),2)+POWER((lng-(W6ADMIN.W6ZIPDATA.LONGITUDE*180)/3.14),2);
END;
========================== END CODE PL/SQL ======================
Thanks in advance for your help.
-
what do you mean by SELECT a.*, what's up the the 'dot star'?
I'm stmontgo and I approve of this message
-
DECLARE
lng FLOAT;
lat FLOAT;
distance NUMBER := 500;
zip VARCHAR(64) := '19464';
v_power number;
BEGIN
--SELECT (((SELECT DISTINCT a.LONGITUDE FROM W6ADMIN.W6ZIPDATA a WHERE a.zipcode=zip)*180)/3.14) INTO lng;
--SELECT (((SELECT DISTINCT a.LATITUDE FROM W6ADMIN.W6ZIPDATA a WHERE a.zipcode=zip)*180)/3.14) INTO lat;
SELECT DISTINCT ((( a.LONGITUDE )*180)/3.14) INTO lng FROM W6ADMIN.W6ZIPDATA a WHERE a.zipcode=zip;
SELECT DISTINCT ((( a.LATITUDE )*180)/3.14) INTO lat FROM W6ADMIN.W6ZIPDATA a WHERE a.zipcode=zip;
SELECT -- a.*,
POWER((POWER((lat- (W6ADMIN.W6ZIPDATA.LATITUDE*180)/3.14),2)+POWER((lng-(W6ADMIN.W6ZIPDATA.LONGITUDE*180)/3.14),2)),.5) AS Distance INTO v_power
FROM W6ADMIN.W6ENGINEERS a, W6ADMIN.W6ZIPDATA
WHERE RTRIM(a.POSTCODE)=RTRIM(W6ADMIN.W6ZIPDATA.zipcode) AND
(POWER((POWER((lat-(W6ADMIN.W6ZIPDATA.LATITUDE*180)/3.14),2)+POWER((lng-(W6ADMIN.W6ZIPDATA.LONGITUDE*180)/3.14),2)),.5)) <= distance ORDER BY POWER((lat-(W6ADMIN.W6ZIPDATA.LATITUDE*180)/3.14),2)+POWER((lng-(W6ADMIN.W6ZIPDATA.LONGITUDE*180)/3.14),2);
END;
Aleš Orehek
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|