Solved

Oracle SQL - filter records not used

Posted on 2012-12-20
4
363 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

0
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
  • 3
4 Comments
 
LVL 22

Accepted Solution

by:
Steve Wales earned 500 total points
ID: 38711243
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))
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 38711642
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
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 38711662
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

0
 
LVL 11

Author Closing Comment

by:Wilder1626
ID: 38713113
Many thanks for your help

all good now
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
error starting form builder in 11g 2 49
scheduler notification 9 80
How do I partition this table on date? 5 55
SQL Query 20 25
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question