Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

Solved

Oracle SQL - filter records not used

Posted on 2012-12-20

1 solution

Medium Priority

365 Views

Last Modified: 2012-12-21

Hello all

I have this SQL bellow that search for all TL_RATE used on LOAD and gives me a count on how many times they where used.

But i would like to do the opposite.

I would like to search all rates that was never used on a load. That, i really dont know what to start with.

Probably i just need to update the bellow SQL.

How can i do that?

Thanks again for your help.

Full SQL:

SELECT
    COUNT(DISTINCT BILL),
    LOAD.RATE_KEY,
    R.FROM_LOW_KEY_VALUE,
    R.FROM_HIGH_KEY_VALUE,
    R.FROM_COUNTRY,
    R.TO_LOW_KEY_VALUE,
    R.TO_HIGH_KEY_VALUE,
    R.TO_COUNTRY,
    LOAD.CARRIER_ID,
    TR.EFFECTIVE,
    TR.DATE_INVALID
FROM 
    LOAD, TL_RATE TR, LANE_RATE_NETWORK R
WHERE
    BEST_DEP > TO_DATE('20090830','YYYYMMDD')
    AND LOAD.RATE_KEY=TR.RATE_KEY
    AND TR.LANE_ID=R.ID
    GROUP BY LOAD.RATE_KEY,
    R.FROM_LOW_KEY_VALUE,
    R.FROM_HIGH_KEY_VALUE,
    R.FROM_COUNTRY,
    R.TO_LOW_KEY_VALUE,
    R.TO_HIGH_KEY_VALUE,
    R.TO_COUNTRY,
    LOAD.CARRIER_ID, 
    TR.EFFECTIVE,
    TR.DATE_INVALID
ORDER BY COUNT(DISTINCT BILL) DESC;

Open in new window

Comment

Question by:Wilder1626

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

4 Comments

LVL 23

Accepted Solution

by:Steve Wales

Steve Wales earned 2000 total points

ID: 387112432012-12-20

Something like this should get you all the rates that aren't in load

select *
from tl_rate
where not (tl_rate.rate_key in (select load.rate_key from load))

LVL 11

Author Comment

by:Wilder1626

ID: 387116422012-12-20

Hi sjwales

I think i'm missing one join somewhere cause i dont have any error and i done have any records also.

I think i have to also use this: AND TR.LANE_ID=R.ID

LVL 11

Author Comment

by:Wilder1626

ID: 387116622012-12-20

I worked the code.

Let me test like this

SELECT   *
  FROM   TL_RATE
 WHERE   NOT (TL_RATE.RATE_KEY IN (SELECT   LOAD.RATE_KEY FROM LOAD)
              AND NOT (TL_RATE.LANE_ID IN
                             (SELECT   LANE_RATE_NETWORK.ID
                                FROM   LANE_RATE_NETWORK)))
         AND TL_RATE.DATE_INVALID >
               TO_DATE (TO_CHAR (SYSDATE, 'YYYYMMDD'), 'YYYYMMDD');

Open in new window

LVL 11

Author Closing Comment

by:Wilder1626

ID: 387131132012-12-21

Many thanks for your help

all good now

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Beware of Between

Article by: PortletPaul

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …

Query Syntax

The Lone Date Ranger :: there is just one right way

Article by: PortletPaul

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …

Query Syntax

How to Extract Information from the Oracle Data Dictionary Views

Video by: Steve

This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…