1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

query using CASE to separate points acording to their distances

Discussion in 'SQL PL/SQL' started by fbastian, Apr 12, 2011.

  1. fbastian

    fbastian Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Hello,
    I am trying to write a query which will give me a buffer 1/2 the size of the actual distance between two points.
    This is not a spatial query as the distance is already in one of the tables as an attribute.
    Below is the query I've written so far but it doesnt work yet:

    Code (SQL):
    SELECT m.rwo_key_id,MIN(buffer_size)buffer_size
    FROM (SELECT m.rwo_key_id,
            CASE
                WHEN MIN(p.route_length)/2<100 THEN urban
                ELSE MIN(p.route_length)/2 rural
                END buffer_size
                FROM hv_span@na_to_sorepview p, mz_cl_poles m
                WHERE p.construction_status = 'as constructed'
                AND (p.pole_1 = m.rwo_key_id OR p.pole_2 = m.rwo_key_id))
                GROUP BY p.rwo_key_id;
    I've also included screen dumps of the tables i'm querying.
    Can someone help??

    thank you!
     

    Attached Files:

  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It's difficult to get usable sample data from your screen shots; please post some usable data for testing. Also define what you mean by 'doesn't work yet'.
     
  3. fbastian

    fbastian Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Hi David,
    thanks for responding.
    i am attaching a zip file with 2 csv files of the tables mentioned in the query.
    what i meant by it doesnt work is that i get a message 'missing keyword' after running the query.

    thank you.
     

    Attached Files:

  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Please post the query with the error message.
     
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Also explain what you are doing with m.rwo_key_id and p.rwo_key_id. and why not grouping in two poles?
     
  6. fbastian

    fbastian Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    select m.rwo_key_id,min(buffer_size)buffer_size
    from (select m.rwo_key_id,
    (case
    when min(p.route_length)/2<100 then 'urban'
    else min(p.route_length)/2 then 'rural'
    end) buffer_size
    from hv_span@na_to_sorepview p, mz_cl_poles m
    where p.construction_status = 'as constructed'
    and (p.pole_1 = m.rwo_key_id or p.pole_2 = m.rwo_key_id))
    group by m.rwo_key_id;

    ORA-00905: missing key word (it could be that the error is the way the query is written, may be there is another way of writing this i'm not sure)

    I am trying to separate poles into two groups,
    urban = distance between the two poles is < than 100 mtrs.
    rural = distance between two poles is > than 100 mtrs.

    thanks.
     
  7. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    I am not sure what exactly you are trying. May be the following may fix the issue.

    Code (SQL):
    SELECT M.RWO_KEY_ID,MIN(P.ROUTE_LENGTH)/2 BUFFER_SIZE,
            (CASE
               WHEN MIN(P.ROUTE_LENGTH)/2<=100 THEN 'URBAN'
               ELSE 'RURAL'
             END) BUFFER_FLAG
    FROM HV_SPAN@NA_TO_SOREPVIEW P, MZ_CL_POLES M
    WHERE P.CONSTRUCTION_STATUS = 'AS CONSTRUCTED'
    AND (P.POLE_1 = M.RWO_KEY_ID OR P.POLE_2 = M.RWO_KEY_ID)
    GROUP BY M.RWO_KEY_ID;
     
  8. fbastian

    fbastian Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Hi Raj,
    I just tried that query and it works as in it runs but i get no results back from it.
    There must be something missing.

    thanks for your help so far.
     
  9. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Try this:

    Code (SQL):
    SELECT t.rwo_key_id, t.buffer_size,
    CASE WHEN t.buffer_size < 100 THEN 'urban' ELSE 'rural' END geography
    FROM
    (SELECT m.rwo_key_id,MIN(buffer_size) buffer_size,
    FROM (SELECT m.rwo_key_id, MIN(p.route_length)/2 buffer_size
    FROM [EMAIL="hv_span@na_to_sorepview"]hv_span@na_to_sorepview[/EMAIL] p, mz_cl_poles m
    WHERE p.construction_status = 'as constructed'
    AND (p.pole_1 = m.rwo_key_id OR p.pole_2 = m.rwo_key_id))
    GROUP BY m.rwo_key_id) t;
     
     
  10. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Or try this. You may have to do self join n case when you refer two different poles from the same master data.

    Code (SQL):

    SELECT M1.RWO_KEY_ID,MIN(P.ROUTE_LENGTH)/2 BUFFER_SIZE,
            (CASE
               WHEN MIN(P.ROUTE_LENGTH)/2<=100 THEN 'URBAN'
               ELSE 'RURAL'
             END) BUFFER_FLAG
    FROM HV_SPAN@NA_TO_SOREPVIEW P,
         MZ_CL_POLES M1,
         MZ_CL_POLES M2
    WHERE P.CONSTRUCTION_STATUS = 'AS CONSTRUCTED'
    AND P.POLE_1 = M1.RWO_KEY_ID
    AND P.POLE_2 = M2.RWO_KEY_ID