Friday, December 4, 2015

Using sys.dbms_debug_vc2coll Collections

Sometimes you run across the need to perform a LIKE comparison on a value with a wildcard. Simple right?

WHERE gzrrqap_security_role LIKE 'STU_ADM_PROFESSIONAL%'

Now, what if you wanted a list of values to compare to? You can't just list them the way you're used to doing it in a static IN list. What now?

Use the sys.dbms_debug_vc2coll collection to basically perform a collection in the WHERE clause by building a table on the fly in the FROM.

SELECT
...
FROM
...
table(sys.dbms_debug_vc2coll('STU_ADM_PROFESSIONAL%','STU_ONESTOP_%')) ck_role
WHERE
 gzrrqap_security_role LIKE CK_role.COLUMN_VALUE


or a mixture of static and wildcard values:

        TABLE (sys.dbms_debug_vc2coll ('P09',
                                       'P10%',
                                       'P11%',
                                       'P12%',
                                       'P13%',
                                       'P14%')) CK_value
           WHERE  
                 ....
                 AND PROGRAM_LEVEL_2 LIKE CK_value.COLUMN_VALUE)


It's pretty handy and can solve many problems. It simply converts parameters (values) into the "table representation" - basically, the opposite to "pivot" result.

Monday, November 30, 2015

Calculating Distance between Zipcodes

If you have done some searching before arriving here, you may have realized that this task is not a simple as it looks on the surface. Surely there is an function or something? No. Not exactly.

[In SAS there apparently is such a built-in function]

Here's what I used to solve this problem:

1. Functions (3)
2. Table of Zipcodes with Longitude/Latitude

I obtained the functions used from this thread: http://www.tek-tips.com/viewthread.cfm?qid=801025

To recap, I created these three Functions:

CREATE OR REPLACE function DISTANCE
    (p1lon number
    ,p1lat number
    ,p2lon number
    ,p2lat number)
    return number
is
begin
    return    get_distance
        (conv_LongLat(p1lon,'LO','SM')
        ,conv_LongLat(p1lat,'LA','SM')
        ,conv_LongLat(p2lon,'LO','SM')
        ,conv_LongLat(p2lat,'LA','SM')
        );
end;
/

CREATE OR REPLACE function Conv_LongLat
    (LongLat    in number
    ,LongOrLat    in varchar2
    ,Conversion_Units in varchar2
    )
    return number
is
    LoLa        Char(2);
    Degree_in_output_units    number;
begin
    LoLa    := upper(substr(LongOrLat,1,2));
    if    LoLa not in ('LO','LA') then
        raise_application_error(-20000,
            'Error: Longitude/Latitude indicator, "'||LongOrLat||
            '", must begin with "LO" or "LA".');
    end if;
    if    LoLa = 'LO' then     -- Longitude
        if    LongLat > 180 then
            raise_application_error(-20001,
                'Error: Longitude ('||LongLat||') must <= 180 degrees.');
        end if;
        if upper(Conversion_Units) = 'M'     -- 'Meters' per degree 
            then Degree_in_output_units := 111303;
        elsif upper(Conversion_Units) = 'F'    -- 'Feet' per degree
            then Degree_in_output_units := 365166;
        elsif upper(Conversion_Units) = 'SM'    -- 'Statute Miles' per degree
            then Degree_in_output_units := 69.16022727272727;
        elsif upper(Conversion_Units) = 'NM'    -- 'Nautical Miles' per degree
            then Degree_in_output_units := 60.098594281230234;
        else
            raise_application_error(-20002,
            'Error: Conversion-Unit indicator must be "M","F","SM",or "NM"');
        end if;
    elsif    LoLa = 'LA' then    -- Latitude
        if    LongLat > 90 then
            raise_application_error(-20003,
                'Error: Latitude ('||LongLat||') must <= 90 degrees.');
        end if;
        if upper(Conversion_Units) = 'M'    -- 'Meters' per degree
            then Degree_in_output_units := 110575;
        elsif upper(Conversion_Units) = 'F'     -- 'Feet' per degree
            then Degree_in_output_units := 362778;
        elsif upper(Conversion_Units) = 'SM'    -- 'Statute Miles' per degree
            then Degree_in_output_units := 68.70795454545454;
        elsif upper(Conversion_Units) = 'NM'    -- 'Nautical Miles' per degree
            then Degree_in_output_units := 59.70558002704562;
        else
            raise_application_error(-20002,
            'Error: Conversion-Unit indicator must be "M","F","SM",or "NM"');
        end if;
    end if;
    Return    LongLat*Degree_in_output_units;
end;
/

CREATE OR REPLACE function DISTANCE
    (p1lon number
    ,p1lat number
    ,p2lon number
    ,p2lat number)
    return number
is
begin
    return    get_distance
        (conv_LongLat(p1lon,'LO','SM')
        ,conv_LongLat(p1lat,'LA','SM')
        ,conv_LongLat(p2lon,'LO','SM')
        ,conv_LongLat(p2lat,'LA','SM')
        );
end;
/

The Table didn't seem like a big deal but after getting in a looking at the data, I had to try a few sources to get one that worked well. The problem I ran into was the same Long/Lat information being used for surrounding zipcodes as the major city.

What I finally settled on was this Zip Code Database from Softwaretools.com at: https://softwaretools.com/zip-code-database

Loading the data took me a few tries using Oracle SQL*Developer since it never gets the column sizing just right it seems. Here's is what I ended up with:













CREATE TABLE ZIPCODES_SWT
(
  ZIPCODE     VARCHAR2(5 BYTE),
  STATE       VARCHAR2(2 BYTE),
  CITY        VARCHAR2(50 BYTE),
  COUNTY      VARCHAR2(25 BYTE),
  STATEFIPS   VARCHAR2(2 BYTE),
  COUNTYFIPS  VARCHAR2(3 BYTE),
  LATITUDE    VARCHAR2(8 BYTE),
  LONGITUDE   VARCHAR2(8 BYTE),
  PREFERENCE  VARCHAR2(1 BYTE),
  TYPE        VARCHAR2(3 BYTE)
)
TABLESPACE USERS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          160K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
MONITORING;

COMMENT ON TABLE ZIPCODES_SWT IS 'Data obtained at: https://softwaretools.com/zip-code-database on 11/30/2015.';

From here i was able to calculate someone's distance (hard-coding my Long/Lat in values 3 and 4) using:

SELECT ROUND(distance(-106.71, 35.14, -106.62, 35.11) AS DISTANCE
  FROM DUAL;


  DISTANCE
----------
6.55683725
1 row selected.

Or Join this cross reference in your query. Remember to filter these Table values: WHERE PREFERENCE = 'A'

Good luck!