PL/SQL Zip code distance calc
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: PL/SQL Zip code distance calc

Hybrid View

  1. #1
    Join Date
    Sep 2003
    Location
    Philadelphia Suburbs
    Posts
    1

    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.

  2. #2
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    what do you mean by SELECT a.*, what's up the the 'dot star'?
    I'm stmontgo and I approve of this message

  3. #3
    Join Date
    Oct 2002
    Location
    Ljubljana,Slovenia
    Posts
    28
    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
  •  


Click Here to Expand Forum to Full Width