troubleshooting Question

SQL to search zone and zip

Avatar of Wilder1626
Wilder1626Flag for Canada asked on
Oracle Database
16 Comments2 Solutions471 ViewsLast Modified:
Hello All

I would need your help about a SQL.

I have 2 SQL, 1 that pull all rates:
SELECT 
TLR.CARRIER_ID,
LRN.FROM_KEY_NUM,
LRN.FROM_LOW_KEY_VALUE,
LRN.FROM_HIGH_KEY_VALUE,
LRN.FROM_COUNTRY,
LRN.TO_KEY_NUM,
LRN.TO_LOW_KEY_VALUE,
LRN.TO_HIGH_KEY_VALUE,
LRN.TO_COUNTRY
from TL_RATE tlr, TL_RATE_DETAIL trd, LANE_RATE_NETWORK lrn
WHERE tlr.CARRIER_ID = trd.CARRIER_ID (+)
and tlr.LANE_ID = trd.LANE_ID (+)
and tlr.TARIFF_CLASS_ID = trd.TARIFF_CLASS_ID (+)
and tlr.EFFECTIVE = trd.EFFECTIVE (+)
and tlr.LANE_ID = lrn.ID
and tlr.EXPIRATION > to_date(to_char(sysdate,'YYYYMMDD'), 'YYYYMMDD');

And one that will Pull all locations:
SELECT 
ID,
LOC_TYPE_ID,
NAME,
ADDR1,
ADDR2,
CITY,
STATE,
COUNTRY,
ZIP,
CONTACT,
PHONE,
PHONE_SPD,
FAX,
LATITUDE,
LONGITUDE,
ZONE
from location


Now what i would like to do is this:

I would like to validate if i have any ZONE or ZIP that are wrong in the rate table, if i validate it with all the ZONES and ZIP from my location table.

At the end, i just want a result with the rates with bad ZIP and ZONE.

Now to determine the zip and the zone from the rate table is like this:

If LRN.FROM_KEY_NUM = 3, then that equals to ZONE.
If LRN.FROM_KEY_NUM = 8, then that equals to ZIP.

Same for LRN.TO_KEY_NUM:
If LRN.TO_KEY_NUM = 3, then that equals to ZONE.
If LRN.TO_KEY_NUM= 8, then that equals to ZIP.

The ZONE or the ZIP from the rate table will be in column:
LRN.FROM_LOW_KEY_VALUE,
LRN.FROM_HIGH_KEY_VALUE,
LRN.TO_LOW_KEY_VALUE,
LRN.TO_HIGH_KEY_VALUE,

and in the Location table, the zone or the zip will be in columns:
ZIP,
ZONE


How can i extract all zip and zone that do not exist in the location tab?

Thanks
ASKER CERTIFIED SOLUTION
flow01
IT-specialist

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 16 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 16 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros