Solved

SQL for Oracle 9I - where clause issue

Posted on 2014-12-18
33
221 Views
Last Modified: 2014-12-28
Hi,

I'm trying to work on an SQL that would search for all orders SOURCE_ID and DEST_ID combination from the ORD table, look at the location table and attach to each SOURCE_ID and DEST_ID the city, state, zip, zone, and then, validate if a rate can match to that order.

I only want to search order SOURCE_ID and DEST_ID where i will not have any rates.

Since all locations have ID, city, state, zip and zone, this make the search criteria more complexe as it needs to find all combination types.

In attachment, you will have a small data set for the ORD, LOCATION, RATE tables, plus another sheet with the result that the query should provide.

Only pull the SOURCE_ID and DEST_ID that does not have any rates.

What i have so far is the bellow, but it just run forever.

Thanks again for your help.

  SELECT distinct SOURCE_ID,
  L1.CITY,
  L1.STATE,
  L1.ZONE,
  L1.ZIP,
  DEST_ID,
  L2.CITY,
  L2.STATE,
  L2.ZONE,
  L2.ZIP
FROM ORD,
  LOCATION L1,
  LOCATION L2
WHERE EARLY_DEL > '13-12-04'
AND SOURCE_ID   = L1.ID
AND DEST_ID     = L2.ID

--VALIDATE IF A RATE EXIST OR NOT
--AND ((SELECT LRN.FROM_LOW_KEY_VALUE FROM LANE_RATE_NETWORK LRN WHERE LRN.FROM_LOW_KEY_VALUE = L1.CITY ))
AND ((L1.CITY NOT IN (SELECT DISTINCT LRN.FROM_LOW_KEY_VALUE FROM TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN WHERE LRN.FROM_LOW_KEY_VALUE = L1.CITY AND 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.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD'))
OR (L1.STATE not in (SELECT DISTINCT LRN.FROM_LOW_KEY_VALUE FROM TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN WHERE LRN.FROM_LOW_KEY_VALUE = L1.STATE and 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.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')))
OR (L1.ZONE not in (SELECT DISTINCT LRN.FROM_LOW_KEY_VALUE FROM TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN WHERE LRN.FROM_LOW_KEY_VALUE = L1.ZONE and 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.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')))
OR (L1.ZIP not in(SELECT DISTINCT LRN.FROM_LOW_KEY_VALUE FROM TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN WHERE LRN.FROM_LOW_KEY_VALUE = L1.ZIP AND 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.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')))
OR (SOURCE_ID not in (SELECT DISTINCT LRN.FROM_LOW_KEY_VALUE FROM TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN WHERE LRN.FROM_LOW_KEY_VALUE = SOURCE_ID and 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.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD'))))

and (L2.CITY not in (SELECT DISTINCT LRN.TO_LOW_KEY_VALUE FROM TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN WHERE LRN.TO_LOW_KEY_VALUE = L2.CITY and 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.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')))
OR (L2.STATE not in (SELECT DISTINCT LRN.TO_LOW_KEY_VALUE FROM TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN WHERE LRN.TO_LOW_KEY_VALUE = L2.STATE and 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.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')))
OR (L2.ZONE NOT IN (SELECT DISTINCT LRN.TO_LOW_KEY_VALUE FROM TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN WHERE LRN.TO_LOW_KEY_VALUE = L2.ZONE AND 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.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')))
OR (L2.ZIP not in (SELECT DISTINCT LRN.TO_LOW_KEY_VALUE FROM TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN WHERE LRN.TO_LOW_KEY_VALUE = L2.ZIP and 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.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')))
OR (DEST_ID not in (SELECT DISTINCT LRN.TO_LOW_KEY_VALUE FROM TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN WHERE LRN.TO_LOW_KEY_VALUE = DEST_ID and 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.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD'))))

ORDER BY SOURCE_ID,
  DEST_ID;

Open in new window

3-tables-extract.xlsx
0
Comment
Question by:Wilder1626
  • 18
  • 9
  • 6
33 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40507960
First try getting rid of all the distincts.

For the in-lists, distinct shouldn't matter.

I'm still looking at the rest of it.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40507996
I went through and added formatting to all the SQL to indent based on parans and I think there is one out of place.

In the code below look for:
-- the AND above ends here

What I think you have is
...
and (
    this
    or
    this
    or
    this
)
and
(   
    this
)
or
    this
or
    this
...

Open in new window


...
and
(
	L2.CITY not in (
		SELECT DISTINCT LRN.TO_LOW_KEY_VALUE
		FROM TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN
		WHERE LRN.TO_LOW_KEY_VALUE = L2.CITY and
			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.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')
		)
) -- the AND above ends here
OR
(
	L2.STATE not in (
		SELECT DISTINCT LRN.TO_LOW_KEY_VALUE
		FROM TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN
		WHERE LRN.TO_LOW_KEY_VALUE = L2.STATE and
			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.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')
	)
)
...

Open in new window

0
 
LVL 11

Author Comment

by:Wilder1626
ID: 40508012
Do i still leave the DISTINCT?

I will give it a try.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40508015
>>Do i still leave the DISTINCT?

Distinct is a performance killer so don't use it unless you have to.

In this case, I don't think you have to.
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 40508040
Pretty strange

When i only run the below SQL, it takes 2 second and only 2 records
      SELECT SOURCE_ID,
  L1.CITY,
  L1.STATE,
  L1.ZONE,
  L1.ZIP,
  DEST_ID,
  L2.CITY,
  L2.STATE,
  L2.ZONE,
  L2.ZIP
FROM ORD,
  LOCATION L1,
  LOCATION L2
WHERE EARLY_DEL > '13-12-04'
AND SOURCE_ID   = L1.ID
AND DEST_ID     = L2.ID
and source_id = '67' and dest_id = '72'
;

Open in new window



When i run below SQL with same first criterias, it never end searching:

 SELECT SOURCE_ID,
  L1.CITY,
  L1.STATE,
  L1.ZONE,
  L1.ZIP,
  DEST_ID,
  L2.CITY,
  L2.STATE,
  L2.ZONE,
  L2.ZIP
FROM ORD,
  LOCATION L1,
  LOCATION L2
WHERE EARLY_DEL > '13-12-04'
AND SOURCE_ID   = L1.ID
AND DEST_ID     = L2.ID
and source_id = '67' and dest_id = '72'
and
(
	L2.CITY not in (
		SELECT LRN.TO_LOW_KEY_VALUE
		FROM TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN
		WHERE LRN.TO_LOW_KEY_VALUE = L2.CITY and
			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.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')
		)
) -- the AND above ends here
OR
(
	L2.STATE not in (
		SELECT LRN.TO_LOW_KEY_VALUE
		FROM TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN
		WHERE LRN.TO_LOW_KEY_VALUE = L2.STATE and
			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.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')
	)
)

OR
(
	L2.ZIP not in (
		SELECT LRN.TO_LOW_KEY_VALUE
		FROM TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN
		WHERE LRN.TO_LOW_KEY_VALUE = L2.STATE and
			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.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')
	)
)

OR
(
	L2.ZONE not in (
		SELECT LRN.TO_LOW_KEY_VALUE
		FROM TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN
		WHERE LRN.TO_LOW_KEY_VALUE = L2.STATE and
			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.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')
	)
)
OR
(
	L2.ID not in (
		SELECT LRN.TO_LOW_KEY_VALUE
		FROM TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN
		WHERE LRN.TO_LOW_KEY_VALUE = L2.STATE and
			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.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')
	)
)

AND

(
	L1.CITY not in (
		SELECT LRN.FROM_LOW_KEY_VALUE
		FROM TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN
		WHERE LRN.FROM_LOW_KEY_VALUE = L1.CITY and
			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.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')
		)
) -- the AND above ends here
OR
(
	L1.STATE not in (
		SELECT LRN.FROM_LOW_KEY_VALUE
		FROM TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN
		WHERE LRN.FROM_LOW_KEY_VALUE = L1.STATE and
			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.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')
	)
)

OR
(
	L1.ZIP not in (
		SELECT LRN.FROM_LOW_KEY_VALUE
		FROM TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN
		WHERE LRN.FROM_LOW_KEY_VALUE = L1.STATE and
			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.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')
	)
)

OR
(
	L1.ZONE not in (
		SELECT LRN.FROM_LOW_KEY_VALUE
		FROM TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN
		WHERE LRN.FROM_LOW_KEY_VALUE = L1.STATE and
			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.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')
	)
)
OR
(
	L1.ID not in (
		SELECT LRN.FROM_LOW_KEY_VALUE
		FROM TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN
		WHERE LRN.FROM_LOW_KEY_VALUE = L1.STATE and
			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.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')
	)
)

ORDER BY SOURCE_ID,
  DEST_ID;

Open in new window

0
 
LVL 11

Author Comment

by:Wilder1626
ID: 40508066
weird, just by removing order by, it goes very quick.
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 40508080
The combination of Not In (subselect), where the subselect returns a lot of rows, and Or is a performance killer. Oracle is not able to optimize that, so you need to do. Replacing the Not In with Not Exists and a correlated subquery referring to the main queries' ID etc. fields can be a major improvement.
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 40508108
Hi Qlemo

when i flip it to Not Exists, i get this error:
ORA-00920: invalid relational operator
00920. 00000 -  "invalid relational operator"
*Cause:    
*Action:
Error at Line: 21 Column: 2

SQL:
SELECT SOURCE_ID,
  L1.CITY,
  L1.STATE,
  L1.ZONE,
  L1.ZIP,
  DEST_ID,
  L2.CITY,
  L2.STATE,
  L2.ZONE,
  L2.ZIP
FROM ORD,
  LOCATION L1,
  LOCATION L2
WHERE EARLY_DEL > '13-12-04'
AND SOURCE_ID   = L1.ID
AND DEST_ID     = L2.ID
and source_id = '67' and dest_id = '72'
and
(
	L2.CITY Not Exists (
		SELECT LRN.TO_LOW_KEY_VALUE
		FROM TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN
		WHERE LRN.TO_LOW_KEY_VALUE = L2.CITY and
			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.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')
		)
) -- the AND above ends here
OR
(
	L2.STATE Not Exists (
		SELECT LRN.TO_LOW_KEY_VALUE
		FROM TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN
		WHERE LRN.TO_LOW_KEY_VALUE = L2.STATE and
			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.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')
	)
)

OR
(
	L2.ZIP Not Exists (
		SELECT LRN.TO_LOW_KEY_VALUE
		FROM TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN
		WHERE LRN.TO_LOW_KEY_VALUE = L2.STATE and
			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.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')
	)
)

OR
(
	L2.ZONE Not Exists (
		SELECT LRN.TO_LOW_KEY_VALUE
		FROM TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN
		WHERE LRN.TO_LOW_KEY_VALUE = L2.STATE and
			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.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')
	)
)
OR
(
	L2.ID Not Exists (
		SELECT LRN.TO_LOW_KEY_VALUE
		FROM TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN
		WHERE LRN.TO_LOW_KEY_VALUE = L2.STATE and
			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.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')
	)
)

AND

(
	L1.CITY Not Exists (
		SELECT LRN.FROM_LOW_KEY_VALUE
		FROM TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN
		WHERE LRN.FROM_LOW_KEY_VALUE = L1.CITY and
			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.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')
		)
) -- the AND above ends here
OR
(
	L1.STATE Not Exists (
		SELECT LRN.FROM_LOW_KEY_VALUE
		FROM TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN
		WHERE LRN.FROM_LOW_KEY_VALUE = L1.STATE and
			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.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')
	)
)

OR
(
	L1.ZIP Not Exists (
		SELECT LRN.FROM_LOW_KEY_VALUE
		FROM TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN
		WHERE LRN.FROM_LOW_KEY_VALUE = L1.STATE and
			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.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')
	)
)

OR
(
	L1.ZONE Not Exists (
		SELECT LRN.FROM_LOW_KEY_VALUE
		FROM TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN
		WHERE LRN.FROM_LOW_KEY_VALUE = L1.STATE and
			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.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')
	)
)
OR
(
	L1.ID Not Exists (
		SELECT LRN.FROM_LOW_KEY_VALUE
		FROM TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN
		WHERE LRN.FROM_LOW_KEY_VALUE = L1.STATE and
			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.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')
	)
)

ORDER BY SOURCE_ID,
  DEST_ID;

Open in new window

0
 
LVL 11

Author Comment

by:Wilder1626
ID: 40508119
I'm also seeing that it does not tell me if a rate does not exist.

when i manually validate, i see order SOURCE_ID and DEST_ID with a rate when it should only tell me the ones without a rate.
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 40508136
I'm also seeing that for each SOURCE_ID and DEST_ID, i have the wrong city, state, zip, zones.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40508223
>>I'm also seeing that it does not tell me if a rate does not exist.
>>I'm also seeing that for each SOURCE_ID and DEST_ID, i have the wrong city, state, zip, zones.

With what SQL?

>>When i run below SQL with same first criterias, it never end searching:

That is because you still have what I feel is incorrect AND/OR combinations.

You have:
something
and
something
or
something
or
something
and
something
or
something


What I think you wanted based on your original SQL is
(something or something or something)
and
(something or something or something)


I'm getting ready to set up a test case based on your original data you posted.  Hope to have something later.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40508257
OK,
You provided sample data and expected results in the Excel spreadsheet however, the posted SQL has extra tables and columns.

Therefore, I cannot set up a test case.

Either provide the missing tables/columns
or
Modify the posted SQL

I need to be able to take your provided information and current SQL that gives the expected results.

Then I can work on making it 'better'.

For example:
The tables TL_RATE, TL_RATE_DETAIL, LANE_RATE_NETWORK aren't in the spreadsheet.
What table has the column EARLY_DEL?

Not part of the performance issue but this is unnecessary:
TLR.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')


This should be the same:
TLR.DATE_INVALID > trunc(sysdate)
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 40508261
I understand.

Let me get the table for you.

Be back soon
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 40508288
Agree, the AND/OR combination is incorrect. As I see it, you want to check if any of 5 fields (ID, City, State, ZIP, Zone) of L1 is not contained in LRN.FROM_LOW_KEY_VALUE of the subselect, and the same is true for L2.

In regard of the NOT EXISTS: The syntax for the first part would be
AND (
  not exists (SELECT * FROM TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN
		WHERE LRN.TO_LOW_KEY_VALUE = L2.CITY and
			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.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')
                 and L1.CITY = LRN.TO_LOW_KEY_VALUE
   or
   not exists -- and so on
)
AND
( -- the same for L2)

Open in new window


However, your conditions in the subselect are strange and intermixed, in a way I have to wonder whether it makes sense at all. I'll let Steve figure that out :D.
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 40508305
slightwv, you will see 3 new table from full SQL:
1- LANE_RATE_NETWORK
2- TL_RATE_DETAIL
3- TL_RATE

for the EARLY_DEL column, it belongs to the ORD table. also added.

Thanks again for your help.
3-tables-extract.xlsx
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40508359
Thanks for the new data.

I hope none of it is considered company sensitive.

One more issue for now: LRN.ID doesn't exist.

There are 4 ID columns in the sample data and I don't want to guess.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 11

Author Comment

by:Wilder1626
ID: 40508372
Dont worry, there is no company sensitive data in what i sent. :)

 LRN.ID doesn't exist = Table LANE_RATE_NETWORK column ID
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40508386
>> LRN.ID doesn't exist = Table LANE_RATE_NETWORK column ID

I don't understand what that means.

I'm trying to set up a runable test case using all the data provided so the SQL you posted produces the expected results you provided.

When I run it, I have a missing column:
                                TLR.LANE_ID = LRN.ID AND
                                              *
ERROR at line 30:
ORA-00904: "LRN"."ID": invalid identifier

Open in new window


I need to know how to resolve that error.

Here is what I have:
set define off

--drop table ord purge;
create table ord(SOURCE_ID varchar2(2), DEST_ID varchar2(5), early_del varchar2(8));
insert into ord values('03','00041','14-12-04');
insert into ord values('03','00048','14-12-04');
insert into ord values('03','00050','14-12-04');
insert into ord values('03','00052','14-12-04');
insert into ord values('03','00055','14-12-04');
insert into ord values('03','00065','14-12-04');
insert into ord values('03','00067','14-12-04');
insert into ord values('03','00072','14-12-04');
insert into ord values('03','00073','14-12-04');
commit;

--drop table location purge;
create table location(ID varchar2(5), CITY varchar2(10), STATE varchar2(2), ZIP varchar2(10), ZONE varchar2(10)); 

insert into location values('00041','ETOBICOKE','ON','M9W 7J7','ZONE_17');
insert into location values('00048','HAMILTON','ON','L8V 5B5','ZONE_12');
insert into location values('00050','BURLINGTON','ON','L7L 1V2','ZONE_17');
insert into location values('00052','NORTH YORK','ON','M6A 3B4','ZONE_17');
insert into location values('00055','WOODBRIDGE','ON','L4L 1A7','ZONE_17');
insert into location values('00065','HAMILTON','ON','L9B 1K3','ZONE_12');
insert into location values('00067','HAMILTON','ON','L8S 1E6','ZONE_12');
insert into location values('00072','HAMILTON','ON','L8P 4W3','ZONE_12');
insert into location values('00073','CALGARY','AB','H4P 3H9','AB_ZONE');
insert into location values('03','CAMBRIDGE','ON','N3H 4R7',null);
commit;

--drop table rate purge;
create table rate(FROM_LOW_KEY_VALUE varchar2(10), TO_LOW_KEY_VALUE varchar2(10));

insert into rate values('N3H 4R7','ZONE_17');
insert into rate values('N3H 4R7','ZONE_12');
commit;


--drop table lane_rate_network purge;
create table lane_rate_network(
ID varchar2(5), FROM_LOW_KEY_VALUE varchar2(10), TO_LOW_KEY_VALUE varchar2(10));

insert into lane_rate_network values('12240','N3H 4R7','ZONE_12');
insert into lane_rate_network values('12242','N3H 4R7','ZONE_17');
commit;

--drop table lane_rate_network purge;
create table lane_rate_network(CARRIER_ID varchar2(15), LANE_ID varchar2(15), LANE_ID_1 varchar2(15), TARIFF_CLASS_ID varchar2(15), EFFECTIVE varchar2(8));


insert into lane_rate_network values('BEACON_03','12242','12242','FLEET','09-04-01');
insert into lane_rate_network values('BEACON_03','12242','12242','FLEET','09-04-01');
insert into lane_rate_network values('BEACON_PW03','12240','12240','DRY_2X','14-07-07');
insert into lane_rate_network values('BEACON_PW03','12240','12240','DRY_3X','14-07-07');
insert into lane_rate_network values('BEACON_PW03','12240','12240','DRY_4X','14-07-07');
insert into lane_rate_network values('BEACON_PW03','12240','12240','REF_2X','14-07-07');
insert into lane_rate_network values('BEACON_PW03','12240','12240','REF_3X','14-07-07');
insert into lane_rate_network values('BEACON_PW03','12242','12242','DRY_2X','14-07-07');
insert into lane_rate_network values('BEACON_PW03','12242','12242','DRY_3X','14-07-07');
insert into lane_rate_network values('BEACON_PW03','12242','12242','DRY_4X','14-07-07');
insert into lane_rate_network values('BEACON_PW03','12242','12242','REF_2X','14-07-07');
insert into lane_rate_network values('BEACON_PW03','12242','12242','REF_3X','14-07-07');
insert into lane_rate_network values('CANCART_PW03','12240','12240','DRY_2X','14-07-07');
insert into lane_rate_network values('CANCART_PW03','12240','12240','DRY_3X','14-07-07');
insert into lane_rate_network values('CANCART_PW03','12240','12240','DRY_4X','14-07-07');
insert into lane_rate_network values('CANCART_PW03','12240','12240','REF_2X','14-07-07');
insert into lane_rate_network values('CANCART_PW03','12240','12240','REF_3X','14-07-07');
insert into lane_rate_network values('CANCART_PW03','12242','12242','DRY_2X','14-07-07');
insert into lane_rate_network values('CANCART_PW03','12242','12242','DRY_3X','14-07-07');
insert into lane_rate_network values('CANCART_PW03','12242','12242','DRY_4X','14-07-07');
insert into lane_rate_network values('CANCART_PW03','12242','12242','REF_2X','14-07-07');
insert into lane_rate_network values('CANCART_PW03','12242','12242','REF_3X','14-07-07');
insert into lane_rate_network values('FORTIGO_PW03','12240','12240','DRY_2X','14-07-07');
insert into lane_rate_network values('FORTIGO_PW03','12240','12240','DRY_3X','14-07-07');
insert into lane_rate_network values('FORTIGO_PW03','12240','12240','DRY_4X','14-07-07');
insert into lane_rate_network values('FORTIGO_PW03','12240','12240','REF_2X','14-07-07');
insert into lane_rate_network values('FORTIGO_PW03','12240','12240','REF_3X','14-07-07');
insert into lane_rate_network values('FORTIGO_PW03','12242','12242','DRY_2X','14-07-07');
insert into lane_rate_network values('FORTIGO_PW03','12242','12242','DRY_3X','14-07-07');
insert into lane_rate_network values('FORTIGO_PW03','12242','12242','DRY_4X','14-07-07');
insert into lane_rate_network values('FORTIGO_PW03','12242','12242','REF_2X','14-07-07');
insert into lane_rate_network values('FORTIGO_PW03','12242','12242','REF_3X','14-07-07');
insert into lane_rate_network values('MFS_PW03','12240','12240','DRY_2X','14-07-07');
insert into lane_rate_network values('MFS_PW03','12240','12240','DRY_3X','14-07-07');
insert into lane_rate_network values('MFS_PW03','12240','12240','DRY_4X','14-07-07');
insert into lane_rate_network values('MFS_PW03','12240','12240','REF_2X','14-07-07');
insert into lane_rate_network values('MFS_PW03','12240','12240','REF_3X','14-07-07');
insert into lane_rate_network values('MFS_PW03','12242','12242','DRY_2X','14-07-07');
insert into lane_rate_network values('MFS_PW03','12242','12242','DRY_3X','14-07-07');
insert into lane_rate_network values('MFS_PW03','12242','12242','DRY_4X','14-07-07');
insert into lane_rate_network values('MFS_PW03','12242','12242','REF_2X','14-07-07');
insert into lane_rate_network values('MFS_PW03','12242','12242','REF_3X','14-07-07');
insert into lane_rate_network values('MUIRS_PW03','12240','12240','DRY_2X','14-07-07');
insert into lane_rate_network values('MUIRS_PW03','12240','12240','DRY_3X','14-07-07');
insert into lane_rate_network values('MUIRS_PW03','12240','12240','DRY_4X','14-07-07');
insert into lane_rate_network values('MUIRS_PW03','12240','12240','REF_2X','14-07-07');
insert into lane_rate_network values('MUIRS_PW03','12240','12240','REF_3X','14-07-07');
insert into lane_rate_network values('MUIRS_PW03','12242','12242','DRY_2X','14-07-07');
insert into lane_rate_network values('MUIRS_PW03','12242','12242','DRY_3X','14-07-07');
insert into lane_rate_network values('MUIRS_PW03','12242','12242','DRY_4X','14-07-07');
insert into lane_rate_network values('MUIRS_PW03','12242','12242','REF_2X','14-07-07');
insert into lane_rate_network values('MUIRS_PW03','12242','12242','REF_3X','14-07-07');
insert into lane_rate_network values('PINNACL_PW03','12240','12240','DRY_2X','14-07-07');
insert into lane_rate_network values('PINNACL_PW03','12240','12240','DRY_3X','14-07-07');
insert into lane_rate_network values('PINNACL_PW03','12240','12240','DRY_4X','14-07-07');
insert into lane_rate_network values('PINNACL_PW03','12240','12240','REF_2X','14-07-07');
insert into lane_rate_network values('PINNACL_PW03','12240','12240','REF_3X','14-07-07');
insert into lane_rate_network values('PINNACL_PW03','12242','12242','DRY_2X','14-07-07');
insert into lane_rate_network values('PINNACL_PW03','12242','12242','DRY_3X','14-07-07');
insert into lane_rate_network values('PINNACL_PW03','12242','12242','DRY_4X','14-07-07');
insert into lane_rate_network values('PINNACL_PW03','12242','12242','REF_2X','14-07-07');
insert into lane_rate_network values('PINNACL_PW03','12242','12242','REF_3X','14-07-07');
insert into lane_rate_network values('STEALTH','12240','12240','STLTH1','09-01-01');
insert into lane_rate_network values('STEALTH','12240','12240','STLTH1','13-12-01');
insert into lane_rate_network values('STEALTH','12240','12240','STLTH2','09-01-01');
insert into lane_rate_network values('STEALTH','12240','12240','STLTH2','13-12-01');
insert into lane_rate_network values('STEALTH','12242','12242','STLTH1','09-01-01');
insert into lane_rate_network values('STEALTH','12242','12242','STLTH1','13-12-01');
insert into lane_rate_network values('STEALTH','12242','12242','STLTH2','09-01-01');
insert into lane_rate_network values('STEALTH','12242','12242','STLTH2','13-12-01');
insert into lane_rate_network values('STEALTH_RETL','12240','12240','FLEET','09-01-01');
insert into lane_rate_network values('STEALTH_RETL','12242','12242','FLEET','09-01-01');
insert into lane_rate_network values('UNITED_PW03','12240','12240','DRY_2X','14-07-07');
insert into lane_rate_network values('UNITED_PW03','12240','12240','DRY_3X','14-07-07');
insert into lane_rate_network values('UNITED_PW03','12240','12240','DRY_4X','14-07-07');
insert into lane_rate_network values('UNITED_PW03','12240','12240','REF_2X','14-07-07');
insert into lane_rate_network values('UNITED_PW03','12240','12240','REF_3X','14-07-07');
insert into lane_rate_network values('UNITED_PW03','12242','12242','DRY_2X','14-07-07');
insert into lane_rate_network values('UNITED_PW03','12242','12242','DRY_3X','14-07-07');
insert into lane_rate_network values('UNITED_PW03','12242','12242','DRY_4X','14-07-07');
insert into lane_rate_network values('UNITED_PW03','12242','12242','REF_2X','14-07-07');
insert into lane_rate_network values('UNITED_PW03','12242','12242','REF_3X','14-07-07');
insert into lane_rate_network values('WILLIAMDAY03','12242','12242','FLEET','10-04-01');
commit;

--drop table tl_rate_detail purge;
create table tl_rate_detail(
CARRIER_ID varchar2(15), LANE_ID varchar2(5), LANE_ID_1 varchar2(5), TARIFF_CLASS_ID varchar2(6), EFFECTIVE varchar2(8)
);

insert into tl_rate_detail values('BEACON_03','12242','12242','FLEET','09-04-01');
insert into tl_rate_detail values('BEACON_03','12242','12242','FLEET','09-04-01');
insert into tl_rate_detail values('BEACON_PW03','12240','12240','DRY_2X','14-07-07');
insert into tl_rate_detail values('BEACON_PW03','12240','12240','DRY_3X','14-07-07');
insert into tl_rate_detail values('BEACON_PW03','12240','12240','DRY_4X','14-07-07');
insert into tl_rate_detail values('BEACON_PW03','12240','12240','REF_2X','14-07-07');
insert into tl_rate_detail values('BEACON_PW03','12240','12240','REF_3X','14-07-07');
insert into tl_rate_detail values('BEACON_PW03','12242','12242','DRY_2X','14-07-07');
insert into tl_rate_detail values('BEACON_PW03','12242','12242','DRY_3X','14-07-07');
insert into tl_rate_detail values('BEACON_PW03','12242','12242','DRY_4X','14-07-07');
insert into tl_rate_detail values('BEACON_PW03','12242','12242','REF_2X','14-07-07');
insert into tl_rate_detail values('BEACON_PW03','12242','12242','REF_3X','14-07-07');
insert into tl_rate_detail values('CANCART_PW03','12240','12240','DRY_2X','14-07-07');
insert into tl_rate_detail values('CANCART_PW03','12240','12240','DRY_3X','14-07-07');
insert into tl_rate_detail values('CANCART_PW03','12240','12240','DRY_4X','14-07-07');
insert into tl_rate_detail values('CANCART_PW03','12240','12240','REF_2X','14-07-07');
insert into tl_rate_detail values('CANCART_PW03','12240','12240','REF_3X','14-07-07');
insert into tl_rate_detail values('CANCART_PW03','12242','12242','DRY_2X','14-07-07');
insert into tl_rate_detail values('CANCART_PW03','12242','12242','DRY_3X','14-07-07');
insert into tl_rate_detail values('CANCART_PW03','12242','12242','DRY_4X','14-07-07');
insert into tl_rate_detail values('CANCART_PW03','12242','12242','REF_2X','14-07-07');
insert into tl_rate_detail values('CANCART_PW03','12242','12242','REF_3X','14-07-07');
insert into tl_rate_detail values('FORTIGO_PW03','12240','12240','DRY_2X','14-07-07');
insert into tl_rate_detail values('FORTIGO_PW03','12240','12240','DRY_3X','14-07-07');
insert into tl_rate_detail values('FORTIGO_PW03','12240','12240','DRY_4X','14-07-07');
insert into tl_rate_detail values('FORTIGO_PW03','12240','12240','REF_2X','14-07-07');
insert into tl_rate_detail values('FORTIGO_PW03','12240','12240','REF_3X','14-07-07');
insert into tl_rate_detail values('FORTIGO_PW03','12242','12242','DRY_2X','14-07-07');
insert into tl_rate_detail values('FORTIGO_PW03','12242','12242','DRY_3X','14-07-07');
insert into tl_rate_detail values('FORTIGO_PW03','12242','12242','DRY_4X','14-07-07');
insert into tl_rate_detail values('FORTIGO_PW03','12242','12242','REF_2X','14-07-07');
insert into tl_rate_detail values('FORTIGO_PW03','12242','12242','REF_3X','14-07-07');
insert into tl_rate_detail values('MFS_PW03','12240','12240','DRY_2X','14-07-07');
insert into tl_rate_detail values('MFS_PW03','12240','12240','DRY_3X','14-07-07');
insert into tl_rate_detail values('MFS_PW03','12240','12240','DRY_4X','14-07-07');
insert into tl_rate_detail values('MFS_PW03','12240','12240','REF_2X','14-07-07');
insert into tl_rate_detail values('MFS_PW03','12240','12240','REF_3X','14-07-07');
insert into tl_rate_detail values('MFS_PW03','12242','12242','DRY_2X','14-07-07');
insert into tl_rate_detail values('MFS_PW03','12242','12242','DRY_3X','14-07-07');
insert into tl_rate_detail values('MFS_PW03','12242','12242','DRY_4X','14-07-07');
insert into tl_rate_detail values('MFS_PW03','12242','12242','REF_2X','14-07-07');
insert into tl_rate_detail values('MFS_PW03','12242','12242','REF_3X','14-07-07');
insert into tl_rate_detail values('MUIRS_PW03','12240','12240','DRY_2X','14-07-07');
insert into tl_rate_detail values('MUIRS_PW03','12240','12240','DRY_3X','14-07-07');
insert into tl_rate_detail values('MUIRS_PW03','12240','12240','DRY_4X','14-07-07');
insert into tl_rate_detail values('MUIRS_PW03','12240','12240','REF_2X','14-07-07');
insert into tl_rate_detail values('MUIRS_PW03','12240','12240','REF_3X','14-07-07');
insert into tl_rate_detail values('MUIRS_PW03','12242','12242','DRY_2X','14-07-07');
insert into tl_rate_detail values('MUIRS_PW03','12242','12242','DRY_3X','14-07-07');
insert into tl_rate_detail values('MUIRS_PW03','12242','12242','DRY_4X','14-07-07');
insert into tl_rate_detail values('MUIRS_PW03','12242','12242','REF_2X','14-07-07');
insert into tl_rate_detail values('MUIRS_PW03','12242','12242','REF_3X','14-07-07');
insert into tl_rate_detail values('PINNACL_PW03','12240','12240','DRY_2X','14-07-07');
insert into tl_rate_detail values('PINNACL_PW03','12240','12240','DRY_3X','14-07-07');
insert into tl_rate_detail values('PINNACL_PW03','12240','12240','DRY_4X','14-07-07');
insert into tl_rate_detail values('PINNACL_PW03','12240','12240','REF_2X','14-07-07');
insert into tl_rate_detail values('PINNACL_PW03','12240','12240','REF_3X','14-07-07');
insert into tl_rate_detail values('PINNACL_PW03','12242','12242','DRY_2X','14-07-07');
insert into tl_rate_detail values('PINNACL_PW03','12242','12242','DRY_3X','14-07-07');
insert into tl_rate_detail values('PINNACL_PW03','12242','12242','DRY_4X','14-07-07');
insert into tl_rate_detail values('PINNACL_PW03','12242','12242','REF_2X','14-07-07');
insert into tl_rate_detail values('PINNACL_PW03','12242','12242','REF_3X','14-07-07');
insert into tl_rate_detail values('STEALTH','12240','12240','STLTH1','09-01-01');
insert into tl_rate_detail values('STEALTH','12240','12240','STLTH1','13-12-01');
insert into tl_rate_detail values('STEALTH','12240','12240','STLTH2','09-01-01');
insert into tl_rate_detail values('STEALTH','12240','12240','STLTH2','13-12-01');
insert into tl_rate_detail values('STEALTH','12242','12242','STLTH1','09-01-01');
insert into tl_rate_detail values('STEALTH','12242','12242','STLTH1','13-12-01');
insert into tl_rate_detail values('STEALTH','12242','12242','STLTH2','09-01-01');
insert into tl_rate_detail values('STEALTH','12242','12242','STLTH2','13-12-01');
insert into tl_rate_detail values('STEALTH_RETL','12240','12240','FLEET','09-01-01');
insert into tl_rate_detail values('STEALTH_RETL','12242','12242','FLEET','09-01-01');
insert into tl_rate_detail values('UNITED_PW03','12240','12240','DRY_2X','14-07-07');
insert into tl_rate_detail values('UNITED_PW03','12240','12240','DRY_3X','14-07-07');
insert into tl_rate_detail values('UNITED_PW03','12240','12240','DRY_4X','14-07-07');
insert into tl_rate_detail values('UNITED_PW03','12240','12240','REF_2X','14-07-07');
insert into tl_rate_detail values('UNITED_PW03','12240','12240','REF_3X','14-07-07');
insert into tl_rate_detail values('UNITED_PW03','12242','12242','DRY_2X','14-07-07');
insert into tl_rate_detail values('UNITED_PW03','12242','12242','DRY_3X','14-07-07');
insert into tl_rate_detail values('UNITED_PW03','12242','12242','DRY_4X','14-07-07');
insert into tl_rate_detail values('UNITED_PW03','12242','12242','REF_2X','14-07-07');
insert into tl_rate_detail values('UNITED_PW03','12242','12242','REF_3X','14-07-07');
insert into tl_rate_detail values('WILLIAMDAY03','12242','12242','FLEET','10-04-01');
commit;

--drop table tl_rate purge;
create table tl_rate( CARRIER_ID varchar2(15), LANE_ID varchar2(15), TARIFF_CLASS_ID varchar2(15), DATE_INVALID varchar2(15), EFFECTIVE varchar2(8));

insert into tl_rate values('FALCON_03','12240','FLEET','14-08-09','09-01-01');
insert into tl_rate values('KOCH_03','12240','FLEET','12-12-02','09-01-01');
insert into tl_rate values('LAWBURN_03','12240','FLEET','13-11-15','09-01-01');
insert into tl_rate values('PENN_03','12240','FLEET','14-08-09','09-01-01');
insert into tl_rate values('UNITED_03','12240','FLEET','14-07-07','09-01-01');
insert into tl_rate values('WALLACE_03','12240','FLEET','13-11-15','09-01-01');
insert into tl_rate values('YOUNG_03','12240','FLEET','13-11-22','09-01-01');
insert into tl_rate values('FLEET_03','12240','FLEET','27-01-01','09-01-01');
insert into tl_rate values('ITS','12240','FLEET','11-02-28','09-01-01');
insert into tl_rate values('DAYROSS_03','12240','FLEET','13-11-15','11-10-03');
insert into tl_rate values('PINNACL_03','12240','FLEET','12-07-30','12-04-03');
insert into tl_rate values('PINNACL_03','12240','FLEET','13-12-06','12-07-31');
insert into tl_rate values('CLEMENT_03','12240','FLEET','13-11-15','12-12-09');
insert into tl_rate values('WFS_03','12240','FLEET','13-12-06','12-11-26');
insert into tl_rate values('A&A_03','12240','FLEET','14-05-16','09-01-01');
insert into tl_rate values('CAN_CARTG_03','12240','FLEET','13-11-15','09-01-01');
insert into tl_rate values('CHALLENGR_03','12240','FLEET','14-08-18','09-01-01');
insert into tl_rate values('STEALTH','12240','STLTH1','13-12-13','09-01-01');
insert into tl_rate values('STEALTH','12240','STLTH2','13-12-13','09-01-01');
insert into tl_rate values('ITS_03','12240','FLEET','13-11-15','11-03-01');
insert into tl_rate values('MFS_03','12240','FLEET','14-07-07','11-03-01');
insert into tl_rate values('CONNORS_03','12240','FLEET','13-11-15','11-11-09');
insert into tl_rate values('IFS_03','12240','FLEET','15-01-15','11-11-09');
insert into tl_rate values('FORTIGO_03','12240','FLEET','13-12-06','11-11-14');
insert into tl_rate values('STEALTH_RETL','12240','FLEET','27-01-01','09-01-01');
insert into tl_rate values('CDI_03','12240','FLEET','13-11-15','12-10-30');
insert into tl_rate values('CANCART_PW03','12240','DRY_3X','15-04-22','14-07-07');
insert into tl_rate values('CANCART_PW03','12240','DRY_4X','15-04-22','14-07-07');
insert into tl_rate values('CANCART_PW03','12240','REF_2X','15-04-22','14-07-07');
insert into tl_rate values('CANCART_PW03','12240','REF_3X','15-04-22','14-07-07');
insert into tl_rate values('S&SFRWD_PH03','12240','DRY_2X','15-04-22','14-10-27');
insert into tl_rate values('BEACON_PW03','12240','DRY_2X','15-04-22','14-07-07');
insert into tl_rate values('BEACON_PW03','12240','DRY_3X','15-04-22','14-07-07');
insert into tl_rate values('BEACON_PW03','12240','DRY_4X','15-04-22','14-07-07');
insert into tl_rate values('BEACON_PW03','12240','REF_2X','15-04-22','14-07-07');
insert into tl_rate values('WFS_03','12240','FLEET','14-08-09','13-12-09');
insert into tl_rate values('PINNACL_PW03','12240','DRY_2X','15-04-22','14-07-07');
insert into tl_rate values('PINNACL_PW03','12240','DRY_3X','15-04-22','14-07-07');
insert into tl_rate values('PINNACL_PW03','12240','DRY_4X','15-04-22','14-07-07');
insert into tl_rate values('PINNACL_PW03','12240','REF_2X','15-04-22','14-07-07');
insert into tl_rate values('PINNACL_PW03','12240','REF_3X','15-04-22','14-07-07');
insert into tl_rate values('BEACON_PW03','12240','REF_3X','15-04-22','14-07-07');
insert into tl_rate values('FLEET_03N','12240','DRY_2X','27-01-01','14-02-13');
insert into tl_rate values('FORTIGO_PW03','12240','DRY_2X','15-04-22','14-07-07');
insert into tl_rate values('FORTIGO_PW03','12240','DRY_3X','15-04-22','14-07-07');
insert into tl_rate values('FORTIGO_PW03','12240','DRY_4X','15-04-22','14-07-07');
insert into tl_rate values('FORTIGO_PW03','12240','REF_2X','15-04-22','14-07-07');
insert into tl_rate values('FORTIGO_PW03','12240','REF_3X','15-04-22','14-07-07');
insert into tl_rate values('MUIRS_PW03','12240','DRY_2X','15-04-22','14-07-07');
insert into tl_rate values('MUIRS_PW03','12240','DRY_3X','15-04-22','14-07-07');
insert into tl_rate values('MUIRS_PW03','12240','DRY_4X','15-04-22','14-07-07');
insert into tl_rate values('MUIRS_PW03','12240','REF_2X','15-04-22','14-07-07');
insert into tl_rate values('MUIRS_PW03','12240','REF_3X','15-04-22','14-07-07');
insert into tl_rate values('ROCKCTY_PH03','12240','DRY_2X','15-04-22','14-10-27');
insert into tl_rate values('FORTIGO_03','12240','FLEET','14-07-07','13-12-09');
insert into tl_rate values('CANCART_PW03','12240','DRY_2X','15-04-22','14-07-07');
insert into tl_rate values('ROCKCTY_PH03','12240','REF_2X','15-04-22','14-10-27');
insert into tl_rate values('PINNACL_03','12240','FLEET','14-07-07','13-12-09');
insert into tl_rate values('MFS_PW03','12240','DRY_2X','15-04-22','14-07-07');
insert into tl_rate values('MFS_PW03','12240','DRY_3X','15-04-22','14-07-07');
insert into tl_rate values('MFS_PW03','12240','DRY_4X','15-04-22','14-07-07');
insert into tl_rate values('MFS_PW03','12240','REF_2X','15-04-22','14-07-07');
insert into tl_rate values('MFS_PW03','12240','REF_3X','15-04-22','14-07-07');
insert into tl_rate values('FLEET_03N','12240','DRY_2X','14-02-12','13-05-29');
insert into tl_rate values('S&SFRWD_PH03','12240','REF_2X','15-04-22','14-10-27');
insert into tl_rate values('UNITED_PW03','12240','DRY_2X','15-04-22','14-07-07');
insert into tl_rate values('UNITED_PW03','12240','DRY_3X','15-04-22','14-07-07');
insert into tl_rate values('UNITED_PW03','12240','DRY_4X','15-04-22','14-07-07');
insert into tl_rate values('UNITED_PW03','12240','REF_2X','15-04-22','14-07-07');
insert into tl_rate values('UNITED_PW03','12240','REF_3X','15-04-22','14-07-07');
insert into tl_rate values('TRANSINT_03','12240','FLEET','13-11-15','11-12-23');
insert into tl_rate values('STEALTH','12240','STLTH2','15-04-22','13-12-01');
insert into tl_rate values('STEALTH','12240','STLTH1','15-04-22','13-12-01');
insert into tl_rate values('FALCON_03','12242','FLEET','14-08-09','09-01-01');
insert into tl_rate values('KOCH_03','12242','FLEET','12-12-02','09-01-01');
insert into tl_rate values('LAWBURN_03','12242','FLEET','13-11-15','09-01-01');
insert into tl_rate values('UNITED_03','12242','FLEET','14-07-07','09-01-01');
insert into tl_rate values('WALLACE_03','12242','FLEET','13-11-15','09-01-01');
insert into tl_rate values('YOUNG_03','12242','FLEET','13-11-22','09-01-01');
insert into tl_rate values('FLEET_03','12242','FLEET','27-01-01','09-01-01');
insert into tl_rate values('ITS','12242','FLEET','11-02-28','09-01-01');
insert into tl_rate values('CONNORS_03','12242','FLEET','13-11-15','11-11-09');
insert into tl_rate values('PINNACL_03','12242','FLEET','12-07-30','12-04-03');
insert into tl_rate values('CLEMENT_03','12242','FLEET','13-11-15','12-12-09');
insert into tl_rate values('WFS_03','12242','FLEET','13-12-06','12-11-26');
insert into tl_rate values('A&A_03','12242','FLEET','14-05-16','09-01-01');
insert into tl_rate values('CAN_CARTG_03','12242','FLEET','13-11-15','09-01-01');
insert into tl_rate values('CHALLENGR_03','12242','FLEET','14-08-18','09-01-01');
insert into tl_rate values('STEALTH','12242','STLTH1','13-12-13','09-01-01');
insert into tl_rate values('STEALTH','12242','STLTH2','13-12-13','09-01-01');
insert into tl_rate values('BEACON_03','12242','FLEET','11-02-28','09-04-01');
insert into tl_rate values('BEACON_03','12242','FLEET','11-02-28','09-04-01');
insert into tl_rate values('ITS_03','12242','FLEET','13-11-15','11-03-01');
insert into tl_rate values('MFS_03','12242','FLEET','14-07-07','11-03-01');
insert into tl_rate values('IFS_03','12242','FLEET','15-01-15','11-11-09');
insert into tl_rate values('FORTIGO_03','12242','FLEET','13-12-06','11-11-14');
insert into tl_rate values('STEALTH_RETL','12242','FLEET','27-01-01','09-01-01');
insert into tl_rate values('CDI_03','12242','FLEET','13-11-15','12-10-30');
insert into tl_rate values('CANCART_PW03','12242','DRY_2X','15-04-22','14-07-07');
insert into tl_rate values('CANCART_PW03','12242','DRY_3X','15-04-22','14-07-07');
insert into tl_rate values('CANCART_PW03','12242','DRY_4X','15-04-22','14-07-07');
insert into tl_rate values('CANCART_PW03','12242','REF_2X','15-04-22','14-07-07');
insert into tl_rate values('CANCART_PW03','12242','REF_3X','15-04-22','14-07-07');
insert into tl_rate values('WFS_03','12242','FLEET','14-08-09','13-12-09');
insert into tl_rate values('PINNACL_PW03','12242','DRY_2X','15-04-22','14-07-07');
insert into tl_rate values('PINNACL_PW03','12242','DRY_3X','15-04-22','14-07-07');
insert into tl_rate values('PINNACL_PW03','12242','DRY_4X','15-04-22','14-07-07');
insert into tl_rate values('PINNACL_PW03','12242','REF_2X','15-04-22','14-07-07');
insert into tl_rate values('PINNACL_PW03','12242','REF_3X','15-04-22','14-07-07');
insert into tl_rate values('BEACON_PW03','12242','DRY_2X','15-04-22','14-07-07');
insert into tl_rate values('BEACON_PW03','12242','DRY_3X','15-04-22','14-07-07');
insert into tl_rate values('BEACON_PW03','12242','DRY_4X','15-04-22','14-07-07');
insert into tl_rate values('BEACON_PW03','12242','REF_2X','15-04-22','14-07-07');
insert into tl_rate values('BEACON_PW03','12242','REF_3X','15-04-22','14-07-07');
insert into tl_rate values('FORTIGO_PW03','12242','DRY_2X','15-04-22','14-07-07');
insert into tl_rate values('FORTIGO_PW03','12242','DRY_3X','15-04-22','14-07-07');
insert into tl_rate values('FORTIGO_PW03','12242','DRY_4X','15-04-22','14-07-07');
insert into tl_rate values('FORTIGO_PW03','12242','REF_2X','15-04-22','14-07-07');
insert into tl_rate values('FORTIGO_PW03','12242','REF_3X','15-04-22','14-07-07');
insert into tl_rate values('MUIRS_PW03','12242','DRY_2X','15-04-22','14-07-07');
insert into tl_rate values('MUIRS_PW03','12242','DRY_3X','15-04-22','14-07-07');
insert into tl_rate values('MUIRS_PW03','12242','DRY_4X','15-04-22','14-07-07');
insert into tl_rate values('MUIRS_PW03','12242','REF_2X','15-04-22','14-07-07');
insert into tl_rate values('MUIRS_PW03','12242','REF_3X','15-04-22','14-07-07');
insert into tl_rate values('STEALTH','12242','STLTH2','15-04-22','13-12-01');
insert into tl_rate values('STEALTH','12242','STLTH1','15-04-22','13-12-01');
insert into tl_rate values('ROCKCTY_PH03','12242','DRY_2X','15-04-22','14-10-27');
insert into tl_rate values('FORTIGO_03','12242','FLEET','14-07-07','13-12-09');
insert into tl_rate values('ROCKCTY_PH03','12242','REF_2X','15-04-22','14-10-27');
insert into tl_rate values('PINNACL_03','12242','FLEET','14-07-07','13-12-09');
insert into tl_rate values('MFS_PW03','12242','DRY_2X','15-04-22','14-07-07');
insert into tl_rate values('MFS_PW03','12242','DRY_3X','15-04-22','14-07-07');
insert into tl_rate values('MFS_PW03','12242','DRY_4X','15-04-22','14-07-07');
insert into tl_rate values('MFS_PW03','12242','REF_2X','15-04-22','14-07-07');
insert into tl_rate values('MFS_PW03','12242','REF_3X','15-04-22','14-07-07');
insert into tl_rate values('PENN_03','12242','FLEET','14-08-09','09-01-01');
insert into tl_rate values('UNITED_PW03','12242','DRY_2X','15-04-22','14-07-07');
insert into tl_rate values('UNITED_PW03','12242','DRY_3X','15-04-22','14-07-07');
insert into tl_rate values('UNITED_PW03','12242','DRY_4X','15-04-22','14-07-07');
insert into tl_rate values('UNITED_PW03','12242','REF_2X','15-04-22','14-07-07');
insert into tl_rate values('UNITED_PW03','12242','REF_3X','15-04-22','14-07-07');
insert into tl_rate values('S&SFRWD_PH03','12242','REF_2X','15-04-22','14-10-27');
insert into tl_rate values('S&SFRWD_PH03','12242','DRY_2X','15-04-22','14-10-27');
insert into tl_rate values('WILLIAMDAY03','12242','FLEET','10-06-29','10-04-01');
insert into tl_rate values('DAYROSS_03','12242','FLEET','13-11-15','11-10-03');
insert into tl_rate values('TRANSINT_03','12242','FLEET','13-11-15','11-12-23');
insert into tl_rate values('PINNACL_03','12242','FLEET','13-12-06','12-07-31');
commit;


/*
*/

set define on

SELECT distinct SOURCE_ID,
  L1.CITY,
  L1.STATE,
  L1.ZONE,
  L1.ZIP,
  DEST_ID,
  L2.CITY,
  L2.STATE,
  L2.ZONE,
  L2.ZIP
FROM ORD,
  LOCATION L1,
  LOCATION L2
WHERE
--No sample data for this EARLY_DEL > '13-12-04' and
SOURCE_ID   = L1.ID
AND DEST_ID     = L2.ID
--VALIDATE IF A RATE EXIST OR NOT
--AND ((SELECT LRN.FROM_LOW_KEY_VALUE FROM LANE_RATE_NETWORK LRN WHERE LRN.FROM_LOW_KEY_VALUE = L1.CITY ))
AND (
	(
		L1.CITY NOT IN (
			SELECT DISTINCT LRN.FROM_LOW_KEY_VALUE
			FROM TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN
			WHERE LRN.FROM_LOW_KEY_VALUE = L1.CITY AND
				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.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')
			)
		OR (
		L1.STATE not in (
			SELECT DISTINCT LRN.FROM_LOW_KEY_VALUE
			FROM TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN
			WHERE LRN.FROM_LOW_KEY_VALUE = L1.STATE and 
				LR.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.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')
			)
		)
		OR (
		L1.ZONE not in (
			SELECT DISTINCT LRN.FROM_LOW_KEY_VALUE
			FROM TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN
			WHERE LRN.FROM_LOW_KEY_VALUE = L1.ZONE and
				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.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')
			)
		)
		OR (
		L1.ZIP not in(
			SELECT DISTINCT LRN.FROM_LOW_KEY_VALUE
			FROM TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN
			WHERE LRN.FROM_LOW_KEY_VALUE = L1.ZIP and
				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.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')
			)
		)
		OR (
		SOURCE_ID not in (
			SELECT DISTINCT LRN.FROM_LOW_KEY_VALUE
			FROM TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN
			WHERE LRN.FROM_LOW_KEY_VALUE = SOURCE_ID and
				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.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')
			)
		)
	)
and
(
	L2.CITY not in (
		SELECT DISTINCT LRN.TO_LOW_KEY_VALUE
		FROM TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN
		WHERE LRN.TO_LOW_KEY_VALUE = L2.CITY and
			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.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')
		)
) -- the AND above ends here
OR
(
	L2.STATE not in (
		SELECT DISTINCT LRN.TO_LOW_KEY_VALUE
		FROM TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN
		WHERE LRN.TO_LOW_KEY_VALUE = L2.STATE and
			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.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')
	)
)
OR
(
	L2.ZONE NOT IN (
		SELECT DISTINCT LRN.TO_LOW_KEY_VALUE
		FROM TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN
		WHERE LRN.TO_LOW_KEY_VALUE = L2.ZONE and
			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.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')
	)
)
OR
(
	L2.ZIP not in (
		SELECT DISTINCT LRN.TO_LOW_KEY_VALUE
		FROM TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN
		WHERE LRN.TO_LOW_KEY_VALUE = L2.ZIP and
			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.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')
	)
)
OR (
	DEST_ID not in (
		SELECT DISTINCT LRN.TO_LOW_KEY_VALUE
		FROM TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN
		WHERE LRN.TO_LOW_KEY_VALUE = DEST_ID and
			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.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')
		)
)
)
ORDER BY SOURCE_ID,
DEST_ID;

Open in new window

0
 
LVL 11

Author Comment

by:Wilder1626
ID: 40508412
sorry

I wanted to says: LRN.ID= Table LANE_RATE_NETWORK column ID

LRN = LANE_RATE_NETWORD

ID column
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40508453
Thanks for that.  I found my mistake.  I had from tables messed up when I created the test case.

I get output now.  Let me see if I can figure out how to get your expected results.

For other Experts that happen to read down this far, here is my test case:
set define off

--drop table ord purge;
create table ord(SOURCE_ID varchar2(2), DEST_ID varchar2(5), early_del varchar2(8));
insert into ord values('03','00041','14-12-04');
insert into ord values('03','00048','14-12-04');
insert into ord values('03','00050','14-12-04');
insert into ord values('03','00052','14-12-04');
insert into ord values('03','00055','14-12-04');
insert into ord values('03','00065','14-12-04');
insert into ord values('03','00067','14-12-04');
insert into ord values('03','00072','14-12-04');
insert into ord values('03','00073','14-12-04');
commit;

--drop table location purge;
create table location(ID varchar2(5), CITY varchar2(10), STATE varchar2(2), ZIP varchar2(10), ZONE varchar2(10)); 

insert into location values('00041','ETOBICOKE','ON','M9W 7J7','ZONE_17');
insert into location values('00048','HAMILTON','ON','L8V 5B5','ZONE_12');
insert into location values('00050','BURLINGTON','ON','L7L 1V2','ZONE_17');
insert into location values('00052','NORTH YORK','ON','M6A 3B4','ZONE_17');
insert into location values('00055','WOODBRIDGE','ON','L4L 1A7','ZONE_17');
insert into location values('00065','HAMILTON','ON','L9B 1K3','ZONE_12');
insert into location values('00067','HAMILTON','ON','L8S 1E6','ZONE_12');
insert into location values('00072','HAMILTON','ON','L8P 4W3','ZONE_12');
insert into location values('00073','CALGARY','AB','H4P 3H9','AB_ZONE');
insert into location values('03','CAMBRIDGE','ON','N3H 4R7',null);
commit;

--drop table rate purge;
create table rate(FROM_LOW_KEY_VALUE varchar2(10), TO_LOW_KEY_VALUE varchar2(10));

insert into rate values('N3H 4R7','ZONE_17');
insert into rate values('N3H 4R7','ZONE_12');
commit;


--drop table lane_rate_network purge;
create table lane_rate_network(
ID varchar2(5), FROM_LOW_KEY_VALUE varchar2(10), TO_LOW_KEY_VALUE varchar2(10));

insert into lane_rate_network values('12240','N3H 4R7','ZONE_12');
insert into lane_rate_network values('12242','N3H 4R7','ZONE_17');
commit;

--drop table tl_rate_detail purge;
create table tl_rate_detail(
CARRIER_ID varchar2(15), LANE_ID varchar2(5), LANE_ID_1 varchar2(5), TARIFF_CLASS_ID varchar2(6), EFFECTIVE varchar2(8)
);

insert into tl_rate_detail values('BEACON_03','12242','12242','FLEET','09-04-01');
insert into tl_rate_detail values('BEACON_03','12242','12242','FLEET','09-04-01');
insert into tl_rate_detail values('BEACON_PW03','12240','12240','DRY_2X','14-07-07');
insert into tl_rate_detail values('BEACON_PW03','12240','12240','DRY_3X','14-07-07');
insert into tl_rate_detail values('BEACON_PW03','12240','12240','DRY_4X','14-07-07');
insert into tl_rate_detail values('BEACON_PW03','12240','12240','REF_2X','14-07-07');
insert into tl_rate_detail values('BEACON_PW03','12240','12240','REF_3X','14-07-07');
insert into tl_rate_detail values('BEACON_PW03','12242','12242','DRY_2X','14-07-07');
insert into tl_rate_detail values('BEACON_PW03','12242','12242','DRY_3X','14-07-07');
insert into tl_rate_detail values('BEACON_PW03','12242','12242','DRY_4X','14-07-07');
insert into tl_rate_detail values('BEACON_PW03','12242','12242','REF_2X','14-07-07');
insert into tl_rate_detail values('BEACON_PW03','12242','12242','REF_3X','14-07-07');
insert into tl_rate_detail values('CANCART_PW03','12240','12240','DRY_2X','14-07-07');
insert into tl_rate_detail values('CANCART_PW03','12240','12240','DRY_3X','14-07-07');
insert into tl_rate_detail values('CANCART_PW03','12240','12240','DRY_4X','14-07-07');
insert into tl_rate_detail values('CANCART_PW03','12240','12240','REF_2X','14-07-07');
insert into tl_rate_detail values('CANCART_PW03','12240','12240','REF_3X','14-07-07');
insert into tl_rate_detail values('CANCART_PW03','12242','12242','DRY_2X','14-07-07');
insert into tl_rate_detail values('CANCART_PW03','12242','12242','DRY_3X','14-07-07');
insert into tl_rate_detail values('CANCART_PW03','12242','12242','DRY_4X','14-07-07');
insert into tl_rate_detail values('CANCART_PW03','12242','12242','REF_2X','14-07-07');
insert into tl_rate_detail values('CANCART_PW03','12242','12242','REF_3X','14-07-07');
insert into tl_rate_detail values('FORTIGO_PW03','12240','12240','DRY_2X','14-07-07');
insert into tl_rate_detail values('FORTIGO_PW03','12240','12240','DRY_3X','14-07-07');
insert into tl_rate_detail values('FORTIGO_PW03','12240','12240','DRY_4X','14-07-07');
insert into tl_rate_detail values('FORTIGO_PW03','12240','12240','REF_2X','14-07-07');
insert into tl_rate_detail values('FORTIGO_PW03','12240','12240','REF_3X','14-07-07');
insert into tl_rate_detail values('FORTIGO_PW03','12242','12242','DRY_2X','14-07-07');
insert into tl_rate_detail values('FORTIGO_PW03','12242','12242','DRY_3X','14-07-07');
insert into tl_rate_detail values('FORTIGO_PW03','12242','12242','DRY_4X','14-07-07');
insert into tl_rate_detail values('FORTIGO_PW03','12242','12242','REF_2X','14-07-07');
insert into tl_rate_detail values('FORTIGO_PW03','12242','12242','REF_3X','14-07-07');
insert into tl_rate_detail values('MFS_PW03','12240','12240','DRY_2X','14-07-07');
insert into tl_rate_detail values('MFS_PW03','12240','12240','DRY_3X','14-07-07');
insert into tl_rate_detail values('MFS_PW03','12240','12240','DRY_4X','14-07-07');
insert into tl_rate_detail values('MFS_PW03','12240','12240','REF_2X','14-07-07');
insert into tl_rate_detail values('MFS_PW03','12240','12240','REF_3X','14-07-07');
insert into tl_rate_detail values('MFS_PW03','12242','12242','DRY_2X','14-07-07');
insert into tl_rate_detail values('MFS_PW03','12242','12242','DRY_3X','14-07-07');
insert into tl_rate_detail values('MFS_PW03','12242','12242','DRY_4X','14-07-07');
insert into tl_rate_detail values('MFS_PW03','12242','12242','REF_2X','14-07-07');
insert into tl_rate_detail values('MFS_PW03','12242','12242','REF_3X','14-07-07');
insert into tl_rate_detail values('MUIRS_PW03','12240','12240','DRY_2X','14-07-07');
insert into tl_rate_detail values('MUIRS_PW03','12240','12240','DRY_3X','14-07-07');
insert into tl_rate_detail values('MUIRS_PW03','12240','12240','DRY_4X','14-07-07');
insert into tl_rate_detail values('MUIRS_PW03','12240','12240','REF_2X','14-07-07');
insert into tl_rate_detail values('MUIRS_PW03','12240','12240','REF_3X','14-07-07');
insert into tl_rate_detail values('MUIRS_PW03','12242','12242','DRY_2X','14-07-07');
insert into tl_rate_detail values('MUIRS_PW03','12242','12242','DRY_3X','14-07-07');
insert into tl_rate_detail values('MUIRS_PW03','12242','12242','DRY_4X','14-07-07');
insert into tl_rate_detail values('MUIRS_PW03','12242','12242','REF_2X','14-07-07');
insert into tl_rate_detail values('MUIRS_PW03','12242','12242','REF_3X','14-07-07');
insert into tl_rate_detail values('PINNACL_PW03','12240','12240','DRY_2X','14-07-07');
insert into tl_rate_detail values('PINNACL_PW03','12240','12240','DRY_3X','14-07-07');
insert into tl_rate_detail values('PINNACL_PW03','12240','12240','DRY_4X','14-07-07');
insert into tl_rate_detail values('PINNACL_PW03','12240','12240','REF_2X','14-07-07');
insert into tl_rate_detail values('PINNACL_PW03','12240','12240','REF_3X','14-07-07');
insert into tl_rate_detail values('PINNACL_PW03','12242','12242','DRY_2X','14-07-07');
insert into tl_rate_detail values('PINNACL_PW03','12242','12242','DRY_3X','14-07-07');
insert into tl_rate_detail values('PINNACL_PW03','12242','12242','DRY_4X','14-07-07');
insert into tl_rate_detail values('PINNACL_PW03','12242','12242','REF_2X','14-07-07');
insert into tl_rate_detail values('PINNACL_PW03','12242','12242','REF_3X','14-07-07');
insert into tl_rate_detail values('STEALTH','12240','12240','STLTH1','09-01-01');
insert into tl_rate_detail values('STEALTH','12240','12240','STLTH1','13-12-01');
insert into tl_rate_detail values('STEALTH','12240','12240','STLTH2','09-01-01');
insert into tl_rate_detail values('STEALTH','12240','12240','STLTH2','13-12-01');
insert into tl_rate_detail values('STEALTH','12242','12242','STLTH1','09-01-01');
insert into tl_rate_detail values('STEALTH','12242','12242','STLTH1','13-12-01');
insert into tl_rate_detail values('STEALTH','12242','12242','STLTH2','09-01-01');
insert into tl_rate_detail values('STEALTH','12242','12242','STLTH2','13-12-01');
insert into tl_rate_detail values('STEALTH_RETL','12240','12240','FLEET','09-01-01');
insert into tl_rate_detail values('STEALTH_RETL','12242','12242','FLEET','09-01-01');
insert into tl_rate_detail values('UNITED_PW03','12240','12240','DRY_2X','14-07-07');
insert into tl_rate_detail values('UNITED_PW03','12240','12240','DRY_3X','14-07-07');
insert into tl_rate_detail values('UNITED_PW03','12240','12240','DRY_4X','14-07-07');
insert into tl_rate_detail values('UNITED_PW03','12240','12240','REF_2X','14-07-07');
insert into tl_rate_detail values('UNITED_PW03','12240','12240','REF_3X','14-07-07');
insert into tl_rate_detail values('UNITED_PW03','12242','12242','DRY_2X','14-07-07');
insert into tl_rate_detail values('UNITED_PW03','12242','12242','DRY_3X','14-07-07');
insert into tl_rate_detail values('UNITED_PW03','12242','12242','DRY_4X','14-07-07');
insert into tl_rate_detail values('UNITED_PW03','12242','12242','REF_2X','14-07-07');
insert into tl_rate_detail values('UNITED_PW03','12242','12242','REF_3X','14-07-07');
insert into tl_rate_detail values('WILLIAMDAY03','12242','12242','FLEET','10-04-01');
commit;

--drop table tl_rate purge;
create table tl_rate( CARRIER_ID varchar2(15), LANE_ID varchar2(15), TARIFF_CLASS_ID varchar2(15), DATE_INVALID varchar2(15), EFFECTIVE varchar2(8));

insert into tl_rate values('FALCON_03','12240','FLEET','14-08-09','09-01-01');
insert into tl_rate values('KOCH_03','12240','FLEET','12-12-02','09-01-01');
insert into tl_rate values('LAWBURN_03','12240','FLEET','13-11-15','09-01-01');
insert into tl_rate values('PENN_03','12240','FLEET','14-08-09','09-01-01');
insert into tl_rate values('UNITED_03','12240','FLEET','14-07-07','09-01-01');
insert into tl_rate values('WALLACE_03','12240','FLEET','13-11-15','09-01-01');
insert into tl_rate values('YOUNG_03','12240','FLEET','13-11-22','09-01-01');
insert into tl_rate values('FLEET_03','12240','FLEET','27-01-01','09-01-01');
insert into tl_rate values('ITS','12240','FLEET','11-02-28','09-01-01');
insert into tl_rate values('DAYROSS_03','12240','FLEET','13-11-15','11-10-03');
insert into tl_rate values('PINNACL_03','12240','FLEET','12-07-30','12-04-03');
insert into tl_rate values('PINNACL_03','12240','FLEET','13-12-06','12-07-31');
insert into tl_rate values('CLEMENT_03','12240','FLEET','13-11-15','12-12-09');
insert into tl_rate values('WFS_03','12240','FLEET','13-12-06','12-11-26');
insert into tl_rate values('A&A_03','12240','FLEET','14-05-16','09-01-01');
insert into tl_rate values('CAN_CARTG_03','12240','FLEET','13-11-15','09-01-01');
insert into tl_rate values('CHALLENGR_03','12240','FLEET','14-08-18','09-01-01');
insert into tl_rate values('STEALTH','12240','STLTH1','13-12-13','09-01-01');
insert into tl_rate values('STEALTH','12240','STLTH2','13-12-13','09-01-01');
insert into tl_rate values('ITS_03','12240','FLEET','13-11-15','11-03-01');
insert into tl_rate values('MFS_03','12240','FLEET','14-07-07','11-03-01');
insert into tl_rate values('CONNORS_03','12240','FLEET','13-11-15','11-11-09');
insert into tl_rate values('IFS_03','12240','FLEET','15-01-15','11-11-09');
insert into tl_rate values('FORTIGO_03','12240','FLEET','13-12-06','11-11-14');
insert into tl_rate values('STEALTH_RETL','12240','FLEET','27-01-01','09-01-01');
insert into tl_rate values('CDI_03','12240','FLEET','13-11-15','12-10-30');
insert into tl_rate values('CANCART_PW03','12240','DRY_3X','15-04-22','14-07-07');
insert into tl_rate values('CANCART_PW03','12240','DRY_4X','15-04-22','14-07-07');
insert into tl_rate values('CANCART_PW03','12240','REF_2X','15-04-22','14-07-07');
insert into tl_rate values('CANCART_PW03','12240','REF_3X','15-04-22','14-07-07');
insert into tl_rate values('S&SFRWD_PH03','12240','DRY_2X','15-04-22','14-10-27');
insert into tl_rate values('BEACON_PW03','12240','DRY_2X','15-04-22','14-07-07');
insert into tl_rate values('BEACON_PW03','12240','DRY_3X','15-04-22','14-07-07');
insert into tl_rate values('BEACON_PW03','12240','DRY_4X','15-04-22','14-07-07');
insert into tl_rate values('BEACON_PW03','12240','REF_2X','15-04-22','14-07-07');
insert into tl_rate values('WFS_03','12240','FLEET','14-08-09','13-12-09');
insert into tl_rate values('PINNACL_PW03','12240','DRY_2X','15-04-22','14-07-07');
insert into tl_rate values('PINNACL_PW03','12240','DRY_3X','15-04-22','14-07-07');
insert into tl_rate values('PINNACL_PW03','12240','DRY_4X','15-04-22','14-07-07');
insert into tl_rate values('PINNACL_PW03','12240','REF_2X','15-04-22','14-07-07');
insert into tl_rate values('PINNACL_PW03','12240','REF_3X','15-04-22','14-07-07');
insert into tl_rate values('BEACON_PW03','12240','REF_3X','15-04-22','14-07-07');
insert into tl_rate values('FLEET_03N','12240','DRY_2X','27-01-01','14-02-13');
insert into tl_rate values('FORTIGO_PW03','12240','DRY_2X','15-04-22','14-07-07');
insert into tl_rate values('FORTIGO_PW03','12240','DRY_3X','15-04-22','14-07-07');
insert into tl_rate values('FORTIGO_PW03','12240','DRY_4X','15-04-22','14-07-07');
insert into tl_rate values('FORTIGO_PW03','12240','REF_2X','15-04-22','14-07-07');
insert into tl_rate values('FORTIGO_PW03','12240','REF_3X','15-04-22','14-07-07');
insert into tl_rate values('MUIRS_PW03','12240','DRY_2X','15-04-22','14-07-07');
insert into tl_rate values('MUIRS_PW03','12240','DRY_3X','15-04-22','14-07-07');
insert into tl_rate values('MUIRS_PW03','12240','DRY_4X','15-04-22','14-07-07');
insert into tl_rate values('MUIRS_PW03','12240','REF_2X','15-04-22','14-07-07');
insert into tl_rate values('MUIRS_PW03','12240','REF_3X','15-04-22','14-07-07');
insert into tl_rate values('ROCKCTY_PH03','12240','DRY_2X','15-04-22','14-10-27');
insert into tl_rate values('FORTIGO_03','12240','FLEET','14-07-07','13-12-09');
insert into tl_rate values('CANCART_PW03','12240','DRY_2X','15-04-22','14-07-07');
insert into tl_rate values('ROCKCTY_PH03','12240','REF_2X','15-04-22','14-10-27');
insert into tl_rate values('PINNACL_03','12240','FLEET','14-07-07','13-12-09');
insert into tl_rate values('MFS_PW03','12240','DRY_2X','15-04-22','14-07-07');
insert into tl_rate values('MFS_PW03','12240','DRY_3X','15-04-22','14-07-07');
insert into tl_rate values('MFS_PW03','12240','DRY_4X','15-04-22','14-07-07');
insert into tl_rate values('MFS_PW03','12240','REF_2X','15-04-22','14-07-07');
insert into tl_rate values('MFS_PW03','12240','REF_3X','15-04-22','14-07-07');
insert into tl_rate values('FLEET_03N','12240','DRY_2X','14-02-12','13-05-29');
insert into tl_rate values('S&SFRWD_PH03','12240','REF_2X','15-04-22','14-10-27');
insert into tl_rate values('UNITED_PW03','12240','DRY_2X','15-04-22','14-07-07');
insert into tl_rate values('UNITED_PW03','12240','DRY_3X','15-04-22','14-07-07');
insert into tl_rate values('UNITED_PW03','12240','DRY_4X','15-04-22','14-07-07');
insert into tl_rate values('UNITED_PW03','12240','REF_2X','15-04-22','14-07-07');
insert into tl_rate values('UNITED_PW03','12240','REF_3X','15-04-22','14-07-07');
insert into tl_rate values('TRANSINT_03','12240','FLEET','13-11-15','11-12-23');
insert into tl_rate values('STEALTH','12240','STLTH2','15-04-22','13-12-01');
insert into tl_rate values('STEALTH','12240','STLTH1','15-04-22','13-12-01');
insert into tl_rate values('FALCON_03','12242','FLEET','14-08-09','09-01-01');
insert into tl_rate values('KOCH_03','12242','FLEET','12-12-02','09-01-01');
insert into tl_rate values('LAWBURN_03','12242','FLEET','13-11-15','09-01-01');
insert into tl_rate values('UNITED_03','12242','FLEET','14-07-07','09-01-01');
insert into tl_rate values('WALLACE_03','12242','FLEET','13-11-15','09-01-01');
insert into tl_rate values('YOUNG_03','12242','FLEET','13-11-22','09-01-01');
insert into tl_rate values('FLEET_03','12242','FLEET','27-01-01','09-01-01');
insert into tl_rate values('ITS','12242','FLEET','11-02-28','09-01-01');
insert into tl_rate values('CONNORS_03','12242','FLEET','13-11-15','11-11-09');
insert into tl_rate values('PINNACL_03','12242','FLEET','12-07-30','12-04-03');
insert into tl_rate values('CLEMENT_03','12242','FLEET','13-11-15','12-12-09');
insert into tl_rate values('WFS_03','12242','FLEET','13-12-06','12-11-26');
insert into tl_rate values('A&A_03','12242','FLEET','14-05-16','09-01-01');
insert into tl_rate values('CAN_CARTG_03','12242','FLEET','13-11-15','09-01-01');
insert into tl_rate values('CHALLENGR_03','12242','FLEET','14-08-18','09-01-01');
insert into tl_rate values('STEALTH','12242','STLTH1','13-12-13','09-01-01');
insert into tl_rate values('STEALTH','12242','STLTH2','13-12-13','09-01-01');
insert into tl_rate values('BEACON_03','12242','FLEET','11-02-28','09-04-01');
insert into tl_rate values('BEACON_03','12242','FLEET','11-02-28','09-04-01');
insert into tl_rate values('ITS_03','12242','FLEET','13-11-15','11-03-01');
insert into tl_rate values('MFS_03','12242','FLEET','14-07-07','11-03-01');
insert into tl_rate values('IFS_03','12242','FLEET','15-01-15','11-11-09');
insert into tl_rate values('FORTIGO_03','12242','FLEET','13-12-06','11-11-14');
insert into tl_rate values('STEALTH_RETL','12242','FLEET','27-01-01','09-01-01');
insert into tl_rate values('CDI_03','12242','FLEET','13-11-15','12-10-30');
insert into tl_rate values('CANCART_PW03','12242','DRY_2X','15-04-22','14-07-07');
insert into tl_rate values('CANCART_PW03','12242','DRY_3X','15-04-22','14-07-07');
insert into tl_rate values('CANCART_PW03','12242','DRY_4X','15-04-22','14-07-07');
insert into tl_rate values('CANCART_PW03','12242','REF_2X','15-04-22','14-07-07');
insert into tl_rate values('CANCART_PW03','12242','REF_3X','15-04-22','14-07-07');
insert into tl_rate values('WFS_03','12242','FLEET','14-08-09','13-12-09');
insert into tl_rate values('PINNACL_PW03','12242','DRY_2X','15-04-22','14-07-07');
insert into tl_rate values('PINNACL_PW03','12242','DRY_3X','15-04-22','14-07-07');
insert into tl_rate values('PINNACL_PW03','12242','DRY_4X','15-04-22','14-07-07');
insert into tl_rate values('PINNACL_PW03','12242','REF_2X','15-04-22','14-07-07');
insert into tl_rate values('PINNACL_PW03','12242','REF_3X','15-04-22','14-07-07');
insert into tl_rate values('BEACON_PW03','12242','DRY_2X','15-04-22','14-07-07');
insert into tl_rate values('BEACON_PW03','12242','DRY_3X','15-04-22','14-07-07');
insert into tl_rate values('BEACON_PW03','12242','DRY_4X','15-04-22','14-07-07');
insert into tl_rate values('BEACON_PW03','12242','REF_2X','15-04-22','14-07-07');
insert into tl_rate values('BEACON_PW03','12242','REF_3X','15-04-22','14-07-07');
insert into tl_rate values('FORTIGO_PW03','12242','DRY_2X','15-04-22','14-07-07');
insert into tl_rate values('FORTIGO_PW03','12242','DRY_3X','15-04-22','14-07-07');
insert into tl_rate values('FORTIGO_PW03','12242','DRY_4X','15-04-22','14-07-07');
insert into tl_rate values('FORTIGO_PW03','12242','REF_2X','15-04-22','14-07-07');
insert into tl_rate values('FORTIGO_PW03','12242','REF_3X','15-04-22','14-07-07');
insert into tl_rate values('MUIRS_PW03','12242','DRY_2X','15-04-22','14-07-07');
insert into tl_rate values('MUIRS_PW03','12242','DRY_3X','15-04-22','14-07-07');
insert into tl_rate values('MUIRS_PW03','12242','DRY_4X','15-04-22','14-07-07');
insert into tl_rate values('MUIRS_PW03','12242','REF_2X','15-04-22','14-07-07');
insert into tl_rate values('MUIRS_PW03','12242','REF_3X','15-04-22','14-07-07');
insert into tl_rate values('STEALTH','12242','STLTH2','15-04-22','13-12-01');
insert into tl_rate values('STEALTH','12242','STLTH1','15-04-22','13-12-01');
insert into tl_rate values('ROCKCTY_PH03','12242','DRY_2X','15-04-22','14-10-27');
insert into tl_rate values('FORTIGO_03','12242','FLEET','14-07-07','13-12-09');
insert into tl_rate values('ROCKCTY_PH03','12242','REF_2X','15-04-22','14-10-27');
insert into tl_rate values('PINNACL_03','12242','FLEET','14-07-07','13-12-09');
insert into tl_rate values('MFS_PW03','12242','DRY_2X','15-04-22','14-07-07');
insert into tl_rate values('MFS_PW03','12242','DRY_3X','15-04-22','14-07-07');
insert into tl_rate values('MFS_PW03','12242','DRY_4X','15-04-22','14-07-07');
insert into tl_rate values('MFS_PW03','12242','REF_2X','15-04-22','14-07-07');
insert into tl_rate values('MFS_PW03','12242','REF_3X','15-04-22','14-07-07');
insert into tl_rate values('PENN_03','12242','FLEET','14-08-09','09-01-01');
insert into tl_rate values('UNITED_PW03','12242','DRY_2X','15-04-22','14-07-07');
insert into tl_rate values('UNITED_PW03','12242','DRY_3X','15-04-22','14-07-07');
insert into tl_rate values('UNITED_PW03','12242','DRY_4X','15-04-22','14-07-07');
insert into tl_rate values('UNITED_PW03','12242','REF_2X','15-04-22','14-07-07');
insert into tl_rate values('UNITED_PW03','12242','REF_3X','15-04-22','14-07-07');
insert into tl_rate values('S&SFRWD_PH03','12242','REF_2X','15-04-22','14-10-27');
insert into tl_rate values('S&SFRWD_PH03','12242','DRY_2X','15-04-22','14-10-27');
insert into tl_rate values('WILLIAMDAY03','12242','FLEET','10-06-29','10-04-01');
insert into tl_rate values('DAYROSS_03','12242','FLEET','13-11-15','11-10-03');
insert into tl_rate values('TRANSINT_03','12242','FLEET','13-11-15','11-12-23');
insert into tl_rate values('PINNACL_03','12242','FLEET','13-12-06','12-07-31');
commit;


/*
*/

set define on

SELECT distinct SOURCE_ID,
  L1.CITY,
  L1.STATE,
  L1.ZONE,
  L1.ZIP,
  DEST_ID,
  L2.CITY,
  L2.STATE,
  L2.ZONE,
  L2.ZIP
FROM ORD,
  LOCATION L1,
  LOCATION L2
WHERE
EARLY_DEL > '13-12-04' and
SOURCE_ID   = L1.ID
AND DEST_ID     = L2.ID
--VALIDATE IF A RATE EXIST OR NOT
--AND ((SELECT LRN.FROM_LOW_KEY_VALUE FROM LANE_RATE_NETWORK LRN WHERE LRN.FROM_LOW_KEY_VALUE = L1.CITY ))
AND (
	(
		L1.CITY NOT IN (
			SELECT DISTINCT LRN.FROM_LOW_KEY_VALUE
			FROM TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN
			WHERE LRN.FROM_LOW_KEY_VALUE = L1.CITY AND
				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.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')
			)
		OR (
		L1.STATE not in (
			SELECT DISTINCT LRN.FROM_LOW_KEY_VALUE
			FROM TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN
			WHERE LRN.FROM_LOW_KEY_VALUE = L1.STATE and 
				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.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')
			)
		)
		OR (
		L1.ZONE not in (
			SELECT DISTINCT LRN.FROM_LOW_KEY_VALUE
			FROM TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN
			WHERE LRN.FROM_LOW_KEY_VALUE = L1.ZONE and
				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.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')
			)
		)
		OR (
		L1.ZIP not in(
			SELECT DISTINCT LRN.FROM_LOW_KEY_VALUE
			FROM TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN
			WHERE LRN.FROM_LOW_KEY_VALUE = L1.ZIP and
				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.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')
			)
		)
		OR (
		SOURCE_ID not in (
			SELECT DISTINCT LRN.FROM_LOW_KEY_VALUE
			FROM TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN
			WHERE LRN.FROM_LOW_KEY_VALUE = SOURCE_ID and
				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.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')
			)
		)
	)
and
(
	L2.CITY not in (
		SELECT DISTINCT LRN.TO_LOW_KEY_VALUE
		FROM TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN
		WHERE LRN.TO_LOW_KEY_VALUE = L2.CITY and
			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.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')
		)
) -- the AND above ends here
OR
(
	L2.STATE not in (
		SELECT DISTINCT LRN.TO_LOW_KEY_VALUE
		FROM TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN
		WHERE LRN.TO_LOW_KEY_VALUE = L2.STATE and
			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.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')
	)
)
OR
(
	L2.ZONE NOT IN (
		SELECT DISTINCT LRN.TO_LOW_KEY_VALUE
		FROM TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN
		WHERE LRN.TO_LOW_KEY_VALUE = L2.ZONE and
			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.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')
	)
)
OR
(
	L2.ZIP not in (
		SELECT DISTINCT LRN.TO_LOW_KEY_VALUE
		FROM TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN
		WHERE LRN.TO_LOW_KEY_VALUE = L2.ZIP and
			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.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')
	)
)
OR (
	DEST_ID not in (
		SELECT DISTINCT LRN.TO_LOW_KEY_VALUE
		FROM TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN
		WHERE LRN.TO_LOW_KEY_VALUE = DEST_ID and
			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.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')
		)
)
)
ORDER BY SOURCE_ID,
DEST_ID;

Open in new window

0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 100 total points
ID: 40508521
Sorry but I'm out of time for the day.

I have no doubt your posted SQL can be simplified a lot.

Hopefully another Expert can pick this up and finish it.

I'll try to get more time to figure it out tomorrow.
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 40509192
I also see that the query puts all city, state, zip, zones to all SOURCE_ID and DES_ID. which is totally wrong. Each ID as is specific city etc...

normally, it should respect the city, state, zip, zones to all SOURCE_ID and DES_ID and only tell me with SOURCE_ID and DES_ID combinations does not have any rates.

Query result
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 40510276
Coming back to http:#a40508288 - is my statement correct? That is, do you need to check if the (ID, City, State, ZIP, Zone) combination is included in the rates table, both for the src and dst location? That would start to make sense.
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 40510304
Almost

If ID or City or State or ZIP or  Zone for both src and dst location combination  are not in the rate table, i need to now about them.

If one of the combination exist, example: SOURCE_ID zone to DEST_ID Zip but not the SOURCE_ID CITYto DEST_ID Zip , since a rate exist, then i'm good. But if none of the combination exist, then i have a problem since all combinations must at least have One rate.

those it make more sense?
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 40510339
I'm still not clear, because the rates are built by a complex query. But it looks like it would be sufficient to use an outer join following the core query. Something like
select ...
FROM ORD
join LOCATION L1 on SOURCE_ID   = L1.ID
join LOCATION L2 on DEST_ID     = L2.ID
left join (/* select to get all available rate pairs */) rates
on rates.FROM_LOW_KEY_VALUE in (L1.ID, L1.ZIP, L1.ZONE, L1.STATE, L1.CITY)
 and rates.TO_LOW_KEY_VALUE in (L2.ID, L2.ZIP, L2.ZONE, L2.STATE, L2.CITY)
WHERE EARLY_DEL > '13-12-04'
and rates.FROM_LOW_KEY_VALUE is null or rates.TO_LOW_KEY_VALUE is null

Open in new window

0
 
LVL 11

Author Comment

by:Wilder1626
ID: 40510344
I dont understand that part of the query below,  but yes, i agree that the rate is a bit complexe:

left join (/* select to get all available rate pairs */) rates
on rates.FROM_LOW_KEY_VALUE in (L1.ID, L1.ZIP, L1.ZONE, L1.STATE, L1.CITY)
 and rates.TO_LOW_KEY_VALUE in (L2.ID, L2.ZIP, L2.ZONE, L2.STATE, L2.CITY)
WHERE EARLY_DEL > '13-12-04'
and rates.FROM_LOW_KEY_VALUE is null or rates.TO_LOW_KEY_VALUE is null

Open in new window

0
 
LVL 68

Expert Comment

by:Qlemo
ID: 40510624
Why? Don't you know how to use ANSI outer joins (left/right/full outer join)?
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 40510720
Well, i'm pretty much a beginner. I can do easy SQL, when it gets too complicated with the inner or outer joins, i struggle a lot.
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 40511031
ok.  I went over the full details of the tables and i think we can do this a little simpler.

To do a small recap, i only need to pull where there is NO MATCH in the rate table.

This part needs to stay the same:
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


No, the orders are in table ORD. So this wiould be needed in the FROM clause. Also to JOIN the location details also for the SOURCE_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



Can you please help me on on Query?

Thanks again
3-tables-extract-NO2.xlsx
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 40511157
So far, i have tried this but having this error below:
ORA-00904: "LRN"."FROM_LOW_KEY_VALUE": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error at Line: 14 Column: 113

SELECT DISTINCT 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) 
              ON lrn.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) 
              ON lrn.to_low_key_value IN ( L2.id, L2.zip, L2.zone, L2.state, 
                                           L2.city ) 
ORDER  BY O.source_id, 
          O.dest_id; 

Open in new window

0
 
LVL 11

Author Comment

by:Wilder1626
ID: 40511201
My last update was getting a result but i was pulling SOURCE_ID and DEST_ID combination that was in the rate table. Still!!!!

Just cant seem to find how to only pull records without rates,.

SELECT 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  O.early_del > '14-10-04'; 

Open in new window

0
 
LVL 68

Accepted Solution

by:
Qlemo earned 400 total points
ID: 40520403
Use this as the final WHERE:
WHERE  O.early_del > '14-10-04'
and ( from_low_key_value is null or to_low_key_value is null );

Open in new window

0
 
LVL 11

Author Closing Comment

by:Wilder1626
ID: 40520506
Thanks, all is working now.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
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…

705 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

18 Experts available now in Live!

Get 1:1 Help Now