Solved

Oracle I9 - Query that pulls duplicates when only single records are required

Posted on 2014-12-25
43
191 Views
Last Modified: 2014-12-25
Hi and Happy holiday to everybody.

I'm having this query that pulls what i need, accept that i also get duplicated O.ID when i only want to get distinct IDs.

Since using DISTINCT is a killer for any queries, is there another way to only retrieve 1 record of each O.ID?

Since i have a huge amount of ID, when i use the DISTINCT, it never ends. When i query without the DISTINCT, it take 10 seconds but with duplications.

Thanks again for your help.

SELECT O.ID, O.source_id, 
       L1.city, 
       L1.state, 
       l1.zone, 
       L1.zip, 
       O.dest_id, 
       l2.city, 
       l2.state, 
       L2.zone, 
       L2.zip 
FROM   ord O 
       JOIN location L1 
         ON O.source_id = L1.id 
       JOIN location L2 
         ON O.dest_id = L2.id 
       LEFT JOIN (SELECT LRN.from_low_key_value 
                  FROM   lane_rate_network LRN, 
                         tl_rate TLR 
                  WHERE  LRN.id = TLR.lane_id 
                         AND TLR.carrier_id NOT LIKE ( '%SPOT_RATE%' ) 
                         AND TLR.carrier_id NOT LIKE ( '%HOLD%' ) 
                         AND TLR.carrier_id NOT LIKE ( 'ADDON%' ) 
                         AND TLR.date_invalid > To_date( 
                             To_char(sysdate, 'YYYYMMDD'))) 
              ON from_low_key_value IN ( L1.id, L1.zip, L1.zone, L1.state, 
                                         L1.city ) 
       LEFT JOIN (SELECT LRN.to_low_key_value 
                  FROM   lane_rate_network LRN, 
                         tl_rate TLR 
                  WHERE  LRN.id = TLR.lane_id 
                         AND TLR.carrier_id NOT LIKE ( '%SPOT_RATE%' ) 
                         AND TLR.carrier_id NOT LIKE ( '%HOLD%' ) 
                         AND TLR.carrier_id NOT LIKE ( 'ADDON%' ) 
                         AND TLR.date_invalid > To_date( 
                             To_char(sysdate, 'YYYYMMDD'))) 
              ON to_low_key_value IN ( L2.id, L2.zip, L2.zone, L2.state, 
                                     L2.city ) 
WHERE  TO_CHAR(O.EARLY_DEL,'mm-dd-YYYY') = '11-11-2014'; 

Open in new window

0
Comment
Question by:Wilder1626
  • 21
  • 19
  • 3
43 Comments
 
LVL 76

Expert Comment

by:arnold
ID: 40517669
usually the answer will be to add distinct before the
select distinct o.id

The other option is to make sure duplicates are either not permitted, or if there duplicates, is to convert the
row1
row2
into row with data from row1 and the different data from row2.
without seeing the underlying data, it is hard to see what the difference if any between your two rows or what causes their.
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 40517681
I don't know if this could help you but here is a sample of the tables i use.
3-tables-extract-no-2.xlsx
0
 
LVL 76

Expert Comment

by:arnold
ID: 40517688
If your source Id is always the same, 03, I would hard write the L1 and see whether that eliminates the duplication.

Do you have one row with L1 populated, and one with L2?
Using the table example, could you post a sample output that you get?
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 40517696
i'm actually connected to an Oracle table with thousand of records. What i sent you is only a small data set.

But if i run  this single query below, i get 1 record as the query result:
SELECT id FROM ORD WHERE ID = '750022555602';

Open in new window


So normally, i should also get 1 result with the other query but i dont.
If i force it to query again for 1 single ID, i get many many duplicated results:

SELECT  O.ID, O.source_id, 
       L1.city, 
       L1.state, 
       l1.zone, 
       L1.zip, 
       O.dest_id, 
       l2.city, 
       l2.state, 
       L2.zone, 
       L2.zip 
FROM   ord O 
       JOIN location L1 
         ON O.source_id = L1.id 
       JOIN location L2 
         ON O.dest_id = L2.id 
       LEFT JOIN (SELECT LRN.from_low_key_value 
                  FROM   lane_rate_network LRN, 
                         tl_rate TLR 
                  WHERE  LRN.id = TLR.lane_id 
                         AND TLR.carrier_id NOT LIKE ( '%SPOT_RATE%' ) 
                         AND TLR.carrier_id NOT LIKE ( '%HOLD%' ) 
                         AND TLR.carrier_id NOT LIKE ( 'ADDON%' ) 
                         AND TLR.date_invalid > To_date( 
                             To_char(sysdate, 'YYYYMMDD'))) 
              ON from_low_key_value IN ( L1.id, L1.zip, L1.zone, L1.state, 
                                         L1.city ) 
       LEFT JOIN (SELECT LRN.to_low_key_value 
                  FROM   lane_rate_network LRN, 
                         tl_rate TLR 
                  WHERE  LRN.id = TLR.lane_id 
                         AND TLR.carrier_id NOT LIKE ( '%SPOT_RATE%' ) 
                         AND TLR.carrier_id NOT LIKE ( '%HOLD%' ) 
                         AND TLR.carrier_id NOT LIKE ( 'ADDON%' ) 
                         AND TLR.date_invalid > To_date( 
                             To_char(sysdate, 'YYYYMMDD'))) 
              ON to_low_key_value IN ( L2.id, L2.zip, L2.zone, L2.state, 
                                     L2.city ) 
WHERE  TO_CHAR(O.EARLY_DEL,'mm-dd-YYYY') = '11-11-2014'
AND O.ID = '750022555602'
; 

Open in new window

0
 
LVL 51

Expert Comment

by:HainKurt
ID: 40517709
is ord.id unique? i dont see it in your excel data
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 40517715
Sorry, yes it is unique. I forgot to add the column.
0
 
LVL 76

Expert Comment

by:arnold
ID: 40517723
The duplication is from the addition of the location left joins.

replace the initial step with a view that will then be left joined.

create view source_destination SELECT O.ID, O.source_id,
       L1.city,
       L1.state,
       l1.zone,
       L1.zip,
       O.dest_id,
       l2.city,
       l2.state,
       L2.zone,
       L2.zip
FROM   ord O
       JOIN location L1
         ON O.source_id = L1.id
       JOIN location L2
         ON O.dest_id = L2.id

then see if using this view with the left joins on the lane network.
you might want to use views that consolidate you data and whose data you can directly join versus trying to assemble.

from_low_key_value should be matched to L1.zip with the to_low_key_value should be matched to L2.zip not sure why you use in with parameters that seem to never be able to match.


create a view for this as well
create view rate_lane_network_view_from  SELECT LRN.from_low_key_value
                  FROM   lane_rate_network LRN,
                         tl_rate TLR
                  WHERE  LRN.id = TLR.lane_id
                         AND TLR.carrier_id NOT LIKE ( '%SPOT_RATE%' )
                         AND TLR.carrier_id NOT LIKE ( '%HOLD%' )
                         AND TLR.carrier_id NOT LIKE ( 'ADDON%' )
                         AND TLR.date_invalid > To_date(
                             To_char(sysdate, 'YYYYMMDD')))

repeat for the to.

though there might be two possible low rates based on the source, and one based on the destination.

Presumably you are trying to get the lowest available rate determined by source => destination for each line, but your duplication occurs because you start with the orders, build them up and then try to find the lowest rate based on the source then trying to combine in the destiantion.
to avoid duplicate records, I think you have to combine completed views of the assembled data.

i.e. source/destiantion first join queries.
second view deals with the various from/to/shipper/rates/dates
select from first view joining on the second view where from/to are matched and using the where clause to select the min(cost)
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 40517728
this return multiple, Line 16-24

SELECT LRN.from_low_key_value 
                  FROM   lane_rate_network LRN, 
                         tl_rate TLR 
                  WHERE  LRN.id = TLR.lane_id 
                         AND TLR.carrier_id NOT LIKE ( '%SPOT_RATE%' ) 
                         AND TLR.carrier_id NOT LIKE ( '%HOLD%' ) 
                         AND TLR.carrier_id NOT LIKE ( 'ADDON%' ) 
                         AND TLR.date_invalid > To_date( 
                             To_char(sysdate, 'YYYYMMDD'))

Open in new window


so it is normal to get duplicates...

also you can use this

To_date(to_char(sysdate, 'YYYYMMDD')
>>>
trunc(sysdate)

so, you should review your logic here... for each id, you join with multiple lane_rate_network
what are you trying to do with this inner query?
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 40517731
i simplified your query as

with 
from_key as (
SELECT LRN.from_low_key_value 
                  FROM   lane_rate_network LRN, 
                         tl_rate TLR 
                  WHERE  LRN.id = TLR.lane_id 
                         AND TLR.carrier_id NOT LIKE ( '%SPOT_RATE%' ) 
                         AND TLR.carrier_id NOT LIKE ( '%HOLD%' ) 
                         AND TLR.carrier_id NOT LIKE ( 'ADDON%' ) 
                         AND TLR.date_invalid > trunc(sysdate)
),
to_key as (
SELECT LRN.to_low_key_value 
                  FROM   lane_rate_network LRN, 
                         tl_rate TLR 
                  WHERE  LRN.id = TLR.lane_id 
                         AND TLR.carrier_id NOT LIKE ( '%SPOT_RATE%' ) 
                         AND TLR.carrier_id NOT LIKE ( '%HOLD%' ) 
                         AND TLR.carrier_id NOT LIKE ( 'ADDON%' ) 
                         AND TLR.date_invalid > trunc(sysdate)
)
SELECT O.ID, O.source_id, 
       L1.city, 
       L1.state, 
       l1.zone, 
       L1.zip, 
       O.dest_id, 
       l2.city, 
       l2.state, 
       L2.zone, 
       L2.zip 
FROM   ord O 
       JOIN location L1 ON O.source_id = L1.id 
       JOIN location L2 ON O.dest_id = L2.id 
       LEFT JOIN from_key ON from_low_key_value IN ( L1.id, L1.zip, L1.zone, L1.state, L1.city ) 
       LEFT JOIN to_key ON to_low_key_value IN ( L2.id, L2.zip, L2.zone, L2.state, L2.city ) 
WHERE  TO_CHAR(O.EARLY_DEL,'mm-dd-YYYY') = '11-11-2014'; 

Open in new window

0
 
LVL 11

Author Comment

by:Wilder1626
ID: 40517732
Here is what i'm trying to do.

 i only need to pull all SOURCE_ID and DEST_ID where there is NO MATCH in the rate table.

This part is the basic columns i'm looking at:
SELECT O.source_id,
  l1.city,
  l1.state,
  l1.ZONE,
  L1.ZIP,
  O.dest_id,
  l2.city,
  l2.state,
  l2.ZONE,
  L2.ZIP

Open in new window


Now, the orders are in table ORD. So this would be needed in the FROM clause. Also to JOIN the location details also for the SOURCE_ID and DEST_ID to get the city, zip, zones etc on each order sources_id and dest_id:
FROM ORD O
JOIN LOCATION L1 ON O.SOURCE_ID = L1.ID
JOIN LOCATION L2 ON O.DEST_ID = L2.ID

Open in new window


Now where it gets a little bit complex is probably with the LEFT JOIN part.

I need to map those 2 table for the rates search:
tABLE: LANE_RATE_NETWORK LRN          --(MUST USE:  ID, FROM_LOW_KEY_VALUE, TO_LOW_KEY_VALUE)
SELECT * FROM TL_RATE TLR                     -- (MUST USE: LANE_ID, INVALID_DATE)

LRN.ID = TLR.LANE_ID are the KEY field that link them together.

Then, the final part would be to search if no rates exist for each POs SOURCE_ID and DEST_ID combination.

The fields we would use to validate each combinations are in table :  LANE_RATE_NETWORK LRN
The fields would be:
SOURCE_ID in field:  FROM_LOW_KEY_VALUE
DEST_ID in field:  TO_LOW_KEY_VALUE

Not that each SOURCE_ID and DEST_ID have specific CITY, ZONE, STATE, ZIP, it needs to also search for any records that would also match each of those combinations.

End finally, I want to be able to filter from table TL_RATE TLR  where:
TLR.INVALID_DATE  > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD')

Open in new window

0
 
LVL 51

Expert Comment

by:HainKurt
ID: 40517733
more simplification and beautification :)

with 
from_key as (
  SELECT LRN.from_low_key_value 
    FROM lane_rate_network LRN inner join tl_rate TLR on LRN.id = TLR.lane_id 
   WHERE TLR.carrier_id NOT LIKE ( '%SPOT_RATE%' ) 
     AND TLR.carrier_id NOT LIKE ( '%HOLD%' ) 
     AND TLR.carrier_id NOT LIKE ( 'ADDON%' ) 
     AND TLR.date_invalid > trunc(sysdate)
),
to_key as (
  SELECT LRN.to_low_key_value 
    FROM lane_rate_network LRN inner join tl_rate TLR on LRN.id = TLR.lane_id  
   WHERE TLR.carrier_id NOT LIKE ( '%SPOT_RATE%' ) 
     AND TLR.carrier_id NOT LIKE ( '%HOLD%' ) 
     AND TLR.carrier_id NOT LIKE ( 'ADDON%' ) 
     AND TLR.date_invalid > trunc(sysdate)
)
SELECT O.ID, O.source_id, 
       L1.city, 
       L1.state, 
       l1.zone, 
       L1.zip, 
       O.dest_id, 
       l2.city, 
       l2.state, 
       L2.zone, 
       L2.zip 
FROM   ord O 
       JOIN location L1 ON O.source_id = L1.id 
       JOIN location L2 ON O.dest_id = L2.id 
       LEFT JOIN from_key ON from_low_key_value IN ( L1.id, L1.zip, L1.zone, L1.state, L1.city ) 
       LEFT JOIN to_key ON to_low_key_value IN ( L2.id, L2.zip, L2.zone, L2.state, L2.city ) 
WHERE  TO_CHAR(O.EARLY_DEL,'mm-dd-YYYY') = '11-11-2014';

Open in new window

0
 
LVL 51

Expert Comment

by:HainKurt
ID: 40517740
the issue is you get multiple from or to key from your tl_rate
so with one order you have multiple rates, so your join (no matter what you do), you will end up getting multiple record per order

unless you do something with those rates, like max, min, average, top first etc...
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 40517741
for example, take the query @ ID: 40517733
comment out line 31 & 32...

what do you get?
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 40517743
or more simply...

for order 1 you find 3 carriers that meets condition... what do you want now? 3 records one for each carrier?
if you need one record per order, what will you do with 3 carriers that meets the condition?
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 40517744
after testing ID: 40517733, it run faster but still getting duplicated order
 dup
dup-records-no-1.png
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 40517746
after commenting out line 31 & 32 ????
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 40517747
or more simply...

for order 1 you find 3 carriers that meets condition... what do you want now? 3 records one for each carrier?
if you need one record per order, what will you do with 3 carriers that meets the condition?

I'm not really looking at all possible combination of rates on source_id and dest_id combo.

As long as i have 1 combination that match, That's all I need to know. But if i dont have any rates that can match one of the combinations, that's what i need to know.
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 40517750
sorry but what are you saying by: after commenting out line 31 & 32 ????

Not sure I understand.
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 40517755
ok, try this, and let us know what do you think:

with 
from_key as (
  SELECT LRN.from_low_key_value 
    FROM lane_rate_network LRN inner join tl_rate TLR on LRN.id = TLR.lane_id 
   WHERE TLR.carrier_id NOT LIKE ( '%SPOT_RATE%' ) 
     AND TLR.carrier_id NOT LIKE ( '%HOLD%' ) 
     AND TLR.carrier_id NOT LIKE ( 'ADDON%' ) 
     AND TLR.date_invalid > trunc(sysdate)
),
to_key as (
  SELECT LRN.to_low_key_value 
    FROM lane_rate_network LRN inner join tl_rate TLR on LRN.id = TLR.lane_id  
   WHERE TLR.carrier_id NOT LIKE ( '%SPOT_RATE%' ) 
     AND TLR.carrier_id NOT LIKE ( '%HOLD%' ) 
     AND TLR.carrier_id NOT LIKE ( 'ADDON%' ) 
     AND TLR.date_invalid > trunc(sysdate)
)
SELECT O.ID, O.source_id, 
       L1.city, 
       L1.state, 
       l1.zone, 
       L1.zip, 
       O.dest_id, 
       l2.city, 
       l2.state, 
       L2.zone, 
       L2.zip,
       (select count(1) from from_key where from_low_key_value in ( L1.id, L1.zip, L1.zone, L1.state, L1.city )) from_keys,
       (select count(1) from to_key where to_low_key_value in ( L2.id, L2.zip, L2.zone, L2.state, L2.city )) to_keys
  FROM ord O 
       JOIN location L1 ON O.source_id = L1.id 
       JOIN location L2 ON O.dest_id = L2.id 
 WHERE TO_CHAR(O.EARLY_DEL,'mm-dd-YYYY') = '11-11-2014';

Open in new window

0
 
LVL 51

Expert Comment

by:HainKurt
ID: 40517756
sorry but what are you saying by: after commenting out line 31 & 32 ????

i said, comment out those lines (add "--" in front or just delete), run the query and check the results
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 40517761
I just whent over some result of the query from ID: 40517755

Very quick result compare to the other ones.

But i'm seing ID with SOURCE_ID and DEST_ID combination with rates when it should only tell me the ones without rates.

Would you know why?
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 11

Author Comment

by:Wilder1626
ID: 40517764
sorry but what are you saying by: after commenting out line 31 & 32 ????


i said, comment out those lines (add "--" in front or just delete), run the query and check the results

when i remove the 3 rows, i get this error:

ORA-32035: unreferenced query name defined in WITH clause
32035. 00000 -  "unreferenced query name defined in WITH clause"
*Cause:    There is at least one WITH clause query name that is not
           referenced in any place.
*Action:   remove the unreferenced query name and retry
Error at Line: 29 Column: 12
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 40517765
check the counts... the ones > 0 is what you want...
we can filter it later... basically what we will do is

select * from (...line 18-33 here...) where (from_keys + to_keys) >0
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 40517767
when i remove the 3 rows, i get this error:

oracle does not like unused with :) it is ok, just run this

SELECT O.ID, O.source_id, 
       L1.city, 
       L1.state, 
       l1.zone, 
       L1.zip, 
       O.dest_id, 
       l2.city, 
       l2.state, 
       L2.zone, 
       L2.zip
  FROM ord O 
       JOIN location L1 ON O.source_id = L1.id 
       JOIN location L2 ON O.dest_id = L2.id 
 WHERE TO_CHAR(O.EARLY_DEL,'mm-dd-YYYY') = '11-11-2014';

Open in new window


this gives all orders but it should be unique...
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 40517771
again, check the results of ID: 40517755
if it is ok, but you want to filter some (based on from_keys & to_keys value) we should be so close to solution...

just let me know what do you get and which ones do you want to get rid of...
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 40517772
for ID: 40517767

I did 2 things.

I get 10642 records with query:
SELECT O.ID, O.source_id, 
       L1.city, 
       L1.state, 
       l1.zone, 
       L1.zip, 
       O.dest_id, 
       l2.city, 
       l2.state, 
       L2.zone, 
       L2.zip
  FROM ord O 
       JOIN location L1 ON O.source_id = L1.id 
       JOIN location L2 ON O.dest_id = L2.id 
 WHERE TO_CHAR(O.EARLY_DEL,'mm-dd-YYYY') = '11-11-2014';

Open in new window


I also get 10462 records with:
SELECT O.ID
  FROM ord O 
 WHERE TO_CHAR(O.EARLY_DEL,'mm-dd-YYYY') = '11-11-2014';

Open in new window

0
 
LVL 51

Expert Comment

by:HainKurt
ID: 40517776
10462 from both, so good, all unique values...

now from ID: 40517755, you should get same number...
check the last 2 column values and tell me which ones do you want/filter out...
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 40517777
i will look at ID: 40517755 and get back to you very quick
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 40517783
looks like we can even more simplify the query :) just one with query:

with 
from_to_key as (
  SELECT from_low_key_value, to_low_key_value 
    FROM lane_rate_network LRN inner join tl_rate TLR on LRN.id = TLR.lane_id 
   WHERE TLR.carrier_id NOT LIKE ( '%SPOT_RATE%' ) 
     AND TLR.carrier_id NOT LIKE ( '%HOLD%' ) 
     AND TLR.carrier_id NOT LIKE ( 'ADDON%' ) 
     AND TLR.date_invalid > trunc(sysdate)
),
SELECT O.ID, O.source_id, 
       L1.city, 
       L1.state, 
       l1.zone, 
       L1.zip, 
       O.dest_id, 
       l2.city, 
       l2.state, 
       L2.zone, 
       L2.zip,
       (select count(1) from from_to_key where from_low_key_value in ( L1.id, L1.zip, L1.zone, L1.state, L1.city )) from_keys,
       (select count(1) from from_to_key where to_low_key_value in ( L2.id, L2.zip, L2.zone, L2.state, L2.city )) to_keys
  FROM ord O 
       JOIN location L1 ON O.source_id = L1.id 
       JOIN location L2 ON O.dest_id = L2.id 
 WHERE TO_CHAR(O.EARLY_DEL,'mm-dd-YYYY') = '11-11-2014';

Open in new window


also, I did not like this line:

WHERE TO_CHAR(O.EARLY_DEL,'mm-dd-YYYY') = '11-11-2014';
>>>
WHERE O.EARLY_DEL >= to_date('11-11-2014', 'mm-dd-YYYY') and O.EARLY_DEL < to_date('11-11-2014', 'mm-dd-YYYY') + 1;

should be much faster... if you have an index on this column, much much better...

with 
from_to_key as (
  SELECT from_low_key_value, to_low_key_value 
    FROM lane_rate_network LRN inner join tl_rate TLR on LRN.id = TLR.lane_id 
   WHERE TLR.carrier_id NOT LIKE ( '%SPOT_RATE%' ) 
     AND TLR.carrier_id NOT LIKE ( '%HOLD%' ) 
     AND TLR.carrier_id NOT LIKE ( 'ADDON%' ) 
     AND TLR.date_invalid > trunc(sysdate)
),
SELECT O.ID, O.source_id, 
       L1.city, 
       L1.state, 
       l1.zone, 
       L1.zip, 
       O.dest_id, 
       l2.city, 
       l2.state, 
       L2.zone, 
       L2.zip,
       (select count(1) from from_to_key where from_low_key_value in ( L1.id, L1.zip, L1.zone, L1.state, L1.city )) from_keys,
       (select count(1) from from_to_key where to_low_key_value in ( L2.id, L2.zip, L2.zone, L2.state, L2.city )) to_keys
  FROM ord O 
       JOIN location L1 ON O.source_id = L1.id 
       JOIN location L2 ON O.dest_id = L2.id 
 WHERE O.EARLY_DEL >= to_date('11-11-2014', 'mm-dd-YYYY') 
   AND O.EARLY_DEL < to_date('11-11-2014', 'mm-dd-YYYY') + 1;

Open in new window

0
 
LVL 11

Author Comment

by:Wilder1626
ID: 40517790
Looks like the records i need to keep are the ones with FROM_KEYS or TO_KEYS at ZERO.

are those 2 columns counting how many times the SOURCE_ID and DEST_ID for the O.ID are matching on a rate?
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 40517793
ok, then check this

with 
from_to_key as (
  SELECT from_low_key_value, to_low_key_value 
    FROM lane_rate_network LRN inner join tl_rate TLR on LRN.id = TLR.lane_id 
   WHERE TLR.carrier_id NOT LIKE ( '%SPOT_RATE%' ) 
     AND TLR.carrier_id NOT LIKE ( '%HOLD%' ) 
     AND TLR.carrier_id NOT LIKE ( 'ADDON%' ) 
     AND TLR.date_invalid > trunc(sysdate)
),
SELECT * from (
SELECT O.ID, O.source_id, 
       L1.city, 
       L1.state, 
       l1.zone, 
       L1.zip, 
       O.dest_id, 
       l2.city, 
       l2.state, 
       L2.zone, 
       L2.zip,
       (select count(1) from from_to_key where from_low_key_value in ( L1.id, L1.zip, L1.zone, L1.state, L1.city )) from_keys,
       (select count(1) from from_to_key where to_low_key_value in ( L2.id, L2.zip, L2.zone, L2.state, L2.city )) to_keys
  FROM ord O 
       JOIN location L1 ON O.source_id = L1.id 
       JOIN location L2 ON O.dest_id = L2.id 
 WHERE O.EARLY_DEL >= to_date('11-11-2014', 'mm-dd-YYYY') 
   AND O.EARLY_DEL < to_date('11-11-2014', 'mm-dd-YYYY') + 1
) x where (from_keys=0 or to_keys=0);

Open in new window


not sure what those counts mean :)

maybe number of carriers that matches your criteria for that order... you should know that
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 40517794
I just tried the below query but i get this below error:

ORA-00903: invalid table name
00903. 00000 -  "invalid table name"
*Cause:    
*Action:
Error at Line: 9 Column: 3

with 
from_to_key as (
  SELECT from_low_key_value, to_low_key_value 
    FROM lane_rate_network LRN inner join tl_rate TLR on LRN.id = TLR.lane_id 
   WHERE TLR.carrier_id NOT LIKE ( '%SPOT_RATE%' ) 
     AND TLR.carrier_id NOT LIKE ( '%HOLD%' ) 
     AND TLR.carrier_id NOT LIKE ( 'ADDON%' ) 
     AND TLR.date_invalid > trunc(sysdate)
), 
SELECT O.ID, O.source_id, 
       L1.city, 
       L1.state, 
       l1.zone, 
       L1.zip, 
       O.dest_id, 
       l2.city, 
       l2.state, 
       L2.zone, 
       L2.zip,
       (select count(1) from from_to_key where from_low_key_value in ( L1.id, L1.zip, L1.zone, L1.state, L1.city )) from_keys,
       (select count(1) from from_to_key where to_low_key_value in ( L2.id, L2.zip, L2.zone, L2.state, L2.city )) to_keys
  FROM ord O 
       JOIN location L1 ON O.source_id = L1.id 
       JOIN location L2 ON O.dest_id = L2.id 
 WHERE O.EARLY_DEL >= to_date('11-11-2014', 'mm-dd-YYYY') 
   AND O.EARLY_DEL < to_date('11-11-2014', 'mm-dd-YYYY') + 1;

Open in new window

0
 
LVL 51

Expert Comment

by:HainKurt
ID: 40517796
line 9. extra "," i forgot to remove

here is the fixed one

with 
from_to_key as (
  SELECT from_low_key_value, to_low_key_value 
    FROM lane_rate_network LRN inner join tl_rate TLR on LRN.id = TLR.lane_id 
   WHERE TLR.carrier_id NOT LIKE ( '%SPOT_RATE%' ) 
     AND TLR.carrier_id NOT LIKE ( '%HOLD%' ) 
     AND TLR.carrier_id NOT LIKE ( 'ADDON%' ) 
     AND TLR.date_invalid > trunc(sysdate)
)
SELECT * from (
SELECT O.ID, O.source_id, 
       L1.city, 
       L1.state, 
       l1.zone, 
       L1.zip, 
       O.dest_id, 
       l2.city, 
       l2.state, 
       L2.zone, 
       L2.zip,
       (select count(1) from from_to_key where from_low_key_value in ( L1.id, L1.zip, L1.zone, L1.state, L1.city )) from_keys,
       (select count(1) from from_to_key where to_low_key_value in ( L2.id, L2.zip, L2.zone, L2.state, L2.city )) to_keys
  FROM ord O 
       JOIN location L1 ON O.source_id = L1.id 
       JOIN location L2 ON O.dest_id = L2.id 
 WHERE O.EARLY_DEL >= to_date('11-11-2014', 'mm-dd-YYYY') 
   AND O.EARLY_DEL < to_date('11-11-2014', 'mm-dd-YYYY') + 1
) x where (from_keys=0 or to_keys=0);

Open in new window


and the filtered one...
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 40517798
Weird, now i get this error:
ORA-00918: column ambiguously defined
00918. 00000 -  "column ambiguously defined"
*Cause:    
*Action:
Error at Line: 10 Column: 7

with 
from_to_key as (
  SELECT from_low_key_value, to_low_key_value 
    FROM lane_rate_network LRN inner join tl_rate TLR on LRN.id = TLR.lane_id 
   WHERE TLR.carrier_id NOT LIKE ( '%SPOT_RATE%' ) 
     AND TLR.carrier_id NOT LIKE ( '%HOLD%' ) 
     AND TLR.carrier_id NOT LIKE ( 'ADDON%' ) 
     AND TLR.date_invalid > trunc(sysdate)
)
SELECT * from (
SELECT O.ID, O.source_id, 
       L1.city, 
       L1.state, 
       l1.zone, 
       L1.zip, 
       O.dest_id, 
       l2.city, 
       l2.state, 
       L2.zone, 
       L2.zip,
       (select count(1) from from_to_key where from_low_key_value in ( L1.id, L1.zip, L1.zone, L1.state, L1.city )) from_keys,
       (select count(1) from from_to_key where to_low_key_value in ( L2.id, L2.zip, L2.zone, L2.state, L2.city )) to_keys
  FROM ord O 
       JOIN location L1 ON O.source_id = L1.id 
       JOIN location L2 ON O.dest_id = L2.id 
 WHERE O.EARLY_DEL >= to_date('11-11-2014', 'mm-dd-YYYY') 
   AND O.EARLY_DEL < to_date('11-11-2014', 'mm-dd-YYYY') + 1
) x where (from_keys=0 or to_keys=0);

Open in new window

0
 
LVL 11

Author Comment

by:Wilder1626
ID: 40517805
Question for you, can we count each O.ID by a combination of from_low_key_value and To_low_key_value?

ex:

Instead of:
(select count(1) from from_to_key where from_low_key_value in ( L1.id, L1.zip, L1.zone, L1.state, L1.city )) from_keys,
       (select count(1) from from_to_key where to_low_key_value in ( L2.id, L2.zip, L2.zone, L2.state, L2.city )) to_keys

Open in new window


Something like:
(select count(1) from from_to_key where from_low_key_value in ( L1.id, L1.zip, L1.zone, L1.state, L1.city )) from_keys, AND 
       (select count(1) from from_to_key where to_low_key_value in ( L2.id, L2.zip, L2.zone, L2.state, L2.city )) to_keys

Open in new window


It 's really by those 2 selection criterias that i need to validate if a rate exist or not.
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 40517806
yes, because we used same col names.. need to add some alias

try this:

with 
from_to_key as (
  SELECT from_low_key_value, to_low_key_value 
    FROM lane_rate_network LRN inner join tl_rate TLR on LRN.id = TLR.lane_id 
   WHERE TLR.carrier_id NOT LIKE ( '%SPOT_RATE%' ) 
     AND TLR.carrier_id NOT LIKE ( '%HOLD%' ) 
     AND TLR.carrier_id NOT LIKE ( 'ADDON%' ) 
     AND TLR.date_invalid > trunc(sysdate)
)
SELECT * from (
SELECT O.ID, 
       O.source_id, 
       L1.city f_city 
       L1.state f_state, 
       l1.zone f_zone, 
       L1.zip f_zip, 
       O.dest_id, 
       l2.city t_city, 
       l2.state t_state, 
       L2.zone t_zone, 
       L2.zip t_zip,
       (select count(1) from from_to_key where from_low_key_value in ( L1.id, L1.zip, L1.zone, L1.state, L1.city )) from_keys,
       (select count(1) from from_to_key where to_low_key_value in ( L2.id, L2.zip, L2.zone, L2.state, L2.city )) to_keys
  FROM ord O 
       JOIN location L1 ON O.source_id = L1.id 
       JOIN location L2 ON O.dest_id = L2.id 
 WHERE O.EARLY_DEL >= to_date('11-11-2014', 'mm-dd-YYYY') 
   AND O.EARLY_DEL < to_date('11-11-2014', 'mm-dd-YYYY') + 1
) x where (from_keys=0 or to_keys=0);

Open in new window

0
 
LVL 51

Expert Comment

by:HainKurt
ID: 40517808
looks like we can, but lets finalize the query above.. then we can further improve it (if it is needed)

something like this:

with
from_to_key as (
  SELECT from_low_key_value, to_low_key_value
    FROM lane_rate_network LRN inner join tl_rate TLR on LRN.id = TLR.lane_id
   WHERE TLR.carrier_id NOT LIKE ( '%SPOT_RATE%' )
     AND TLR.carrier_id NOT LIKE ( '%HOLD%' )
     AND TLR.carrier_id NOT LIKE ( 'ADDON%' )
     AND TLR.date_invalid > trunc(sysdate)
)
SELECT * from (
SELECT O.ID,
       O.source_id,
       L1.city f_City,
       L1.state f_State,
       l1.zone f_zone,
       L1.zip f_zip,
       O.dest_id,
       l2.city t_city,
       l2.state t_state,
       L2.zone t_zone,
       L2.zip t_zip,
       (select count(1)
          from from_to_key
         where from_low_key_value in ( L1.id, L1.zip, L1.zone, L1.state, L1.city )
            or to_low_key_value in ( L2.id, L2.zip, L2.zone, L2.state, L2.city )
       ) from_to_keys
  FROM ord O
       JOIN location L1 ON O.source_id = L1.id
       JOIN location L2 ON O.dest_id = L2.id
 WHERE O.EARLY_DEL >= to_date('11-11-2014', 'mm-dd-YYYY')
   AND O.EARLY_DEL < to_date('11-11-2014', 'mm-dd-YYYY') + 1
) x where from_to_keys=0;

if this works fine for you, then it is much faster than the previous one (one sub-query per records instead of 2)
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 40517812
Ok, after testing this query, i'm getting 9 records, and the 9 records don't have any rates. So i think we are very very close to the final result. :)

Now, if we can look at the combinations of those 2 criteria below, i think we will be right on the target.
(select count(1) from from_to_key where from_low_key_value in ( L1.id, L1.zip, L1.zone, L1.state, L1.city )) from_keys, AND 
       (select count(1) from from_to_key where to_low_key_value in ( L2.id, L2.zip, L2.zone, L2.state, L2.city )) to_keys

Open in new window



with 
from_to_key as (
  SELECT from_low_key_value, to_low_key_value 
    FROM lane_rate_network LRN inner join tl_rate TLR on LRN.id = TLR.lane_id 
   WHERE TLR.carrier_id NOT LIKE ( '%SPOT_RATE%' ) 
     AND TLR.carrier_id NOT LIKE ( 'HOLD%' ) 
     AND TLR.carrier_id NOT LIKE ( 'ADDON%' ) 
     AND TLR.carrier_id NOT LIKE ( 'CLEANUP%' ) 
     AND TLR.carrier_id NOT LIKE ( 'CUSTOMER%' )
     AND TLR.carrier_id NOT LIKE ( 'OVERAGES%' ) 
     AND TLR.carrier_id NOT LIKE ( 'YOUNG_US%' )
     AND TLR.date_invalid > trunc(sysdate)
)
SELECT * from (
SELECT O.ID, O.source_id, 
       L1.city f_City, 
       L1.state f_State, 
       l1.zone f_zone, 
       L1.zip f_zip, 
       O.dest_id, 
       l2.city t_city, 
       l2.state t_state, 
       L2.zone t_zone, 
       L2.zip t_zip,
       (select count(1) from from_to_key where from_low_key_value in ( L1.id, L1.zip, L1.zone, L1.state, L1.city )) from_keys,
       (select count(1) from from_to_key where to_low_key_value in ( L2.id, L2.zip, L2.zone, L2.state, L2.city )) to_keys
  FROM ord O 
       JOIN location L1 ON O.source_id = L1.id 
       JOIN location L2 ON O.dest_id = L2.id 
 WHERE O.EARLY_DEL >= to_date('11-11-2014', 'mm-dd-YYYY') 
   AND O.EARLY_DEL < to_date('11-11-2014', 'mm-dd-YYYY') + 1
) x where (from_keys=0 or to_keys=0);

Open in new window

0
 
LVL 51

Accepted Solution

by:
HainKurt earned 500 total points
ID: 40517814
if 9 records is what you want, no need for further improvement, but here it is

with 
from_to_key as (
  SELECT from_low_key_value, to_low_key_value 
    FROM lane_rate_network LRN inner join tl_rate TLR on LRN.id = TLR.lane_id 
   WHERE TLR.carrier_id NOT LIKE ( '%SPOT_RATE%' ) 
     AND TLR.carrier_id NOT LIKE ( '%HOLD%' ) 
     AND TLR.carrier_id NOT LIKE ( 'ADDON%' ) 
     AND TLR.date_invalid > trunc(sysdate)
)
SELECT * from (
SELECT O.ID, 
       O.source_id, 
       L1.city f_City,
       L1.state f_State, 
       l1.zone f_zone, 
       L1.zip f_zip, 
       O.dest_id, 
       l2.city t_city, 
       l2.state t_state, 
       L2.zone t_zone, 
       L2.zip t_zip,
       (select count(1) 
          from from_to_key 
         where from_low_key_value in ( L1.id, L1.zip, L1.zone, L1.state, L1.city )
            or to_low_key_value in ( L2.id, L2.zip, L2.zone, L2.state, L2.city )
       ) from_to_keys
  FROM ord O 
       JOIN location L1 ON O.source_id = L1.id 
       JOIN location L2 ON O.dest_id = L2.id 
 WHERE O.EARLY_DEL >= to_date('11-11-2014', 'mm-dd-YYYY') 
   AND O.EARLY_DEL < to_date('11-11-2014', 'mm-dd-YYYY') + 1
) x where from_to_keys=0;

Open in new window


this should perform much better since only one sub-query per record...
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 40517834
For the 9 records i was pulling, i don't know if I was pulling all the records that i should have. But at lease, the 9 records where good.

Ok, i have just tried your final query below, and i get more results.

I have also updated  row 24 and 25 like below. I used AND instead or OR:
where from_low_key_value in ( L1.id, L1.zip, L1.zone, L1.state, L1.city )
            AND to_low_key_value in ( L2.id, L2.zip, L2.zone, L2.state, L2.city )
       ) from_to_keys

Open in new window


But it looks like i get a huge amout of records as it take a long time to do a count rows...

but when i search for rates with the results i get with the query, so far, i dont have any rates.

with 
from_to_key as (
  SELECT from_low_key_value, to_low_key_value 
    FROM lane_rate_network LRN inner join tl_rate TLR on LRN.id = TLR.lane_id 
   WHERE TLR.carrier_id NOT LIKE ( '%SPOT_RATE%' ) 
     AND TLR.carrier_id NOT LIKE ( '%HOLD%' ) 
     AND TLR.carrier_id NOT LIKE ( 'ADDON%' ) 
     AND TLR.carrier_id NOT LIKE ( 'CLEANUP%' ) 
     AND TLR.carrier_id NOT LIKE ( 'CUSTOMER%' ) 
     AND TLR.carrier_id NOT LIKE ( 'YOUNG_US%' ) 
     AND TLR.carrier_id NOT LIKE ( 'OVERAGE%' ) 
     AND TLR.date_invalid > trunc(sysdate)
)
SELECT * from (
SELECT O.ID, 
       O.source_id, 
       L1.city f_City,
       L1.state f_State, 
       l1.zone f_zone, 
       L1.zip f_zip, 
       O.dest_id, 
       l2.city t_city, 
       l2.state t_state, 
       L2.zone t_zone, 
       L2.zip t_zip,
       (select count(1) 
          from from_to_key 
         where from_low_key_value in ( L1.id, L1.zip, L1.zone, L1.state, L1.city )
            AND to_low_key_value in ( L2.id, L2.zip, L2.zone, L2.state, L2.city )
       ) from_to_keys
  FROM ord O 
       JOIN location L1 ON O.source_id = L1.id 
       JOIN location L2 ON O.dest_id = L2.id 
 WHERE O.EARLY_DEL >= to_date('11-11-2014', 'mm-dd-YYYY') 
   AND O.EARLY_DEL < to_date('11-11-2014', 'mm-dd-YYYY') + 1
) x where from_to_keys=0;

Open in new window

0
 
LVL 11

Author Comment

by:Wilder1626
ID: 40517852
i'm actually getting 183 records

i will go over them to validate
0
 
LVL 11

Author Closing Comment

by:Wilder1626
ID: 40517905
Thank you so much for your help

This is a perfect solution for what i need. Have a vary nice holiday.
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 40517918
one of my least efficient 500x4 = 2K points :)

Thanks for your "vary nice" wish :) U2
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now