asked on

Oracle - Were clause issue

Hi all

I have this SQL below that extract rates based on LRN.FROM_LOW_KEY_VALUE, LRN.TO_LOW_KEY_VALUE values.

But what i want to add to the SQL is the below:
I want to add to the were clause that if LRN.FROM_KEY_NUM, LRN.TO_KEY_NUM exsist as a city / zip or ID (already on the SQL) , I want to also add if the LRN.FROM_KEY_NUM, LRN.TO_KEY_NUM location belong to the City 'MONCTON' of the location table in CITY column.

How can i update the SQL?

Thanks again for your help

      WHERE 
    TLR.CARRIER_ID = TRD.CARRIER_ID (+)
    AND TLR.LANE_ID = TRD.LANE_ID (+)
    AND TLR.TARIFF_CLASS_ID = TRD.TARIFF_CLASS_ID (+)
    AND TLR.EFFECTIVE = TRD.EFFECTIVE (+)
    AND TLR.LANE_ID = LRN.ID
    AND (   6 IN (LRN.FROM_KEY_NUM, LRN.TO_KEY_NUM) AND (EXISTS (SELECT ID FROM LOCATION WHERE ID IN (LRN.FROM_LOW_KEY_VALUE, LRN.TO_LOW_KEY_VALUE))
         )
        OR 8 IN (LRN.FROM_KEY_NUM, LRN.TO_KEY_NUM) AND (EXISTS (SELECT ZIP FROM LOCATION WHERE ZIP IN (LRN.FROM_LOW_KEY_VALUE, LRN.FROM_HIGH_KEY_VALUE, LRN.TO_LOW_KEY_VALUE, LRN.TO_HIGH_KEY_VALUE))
         )
         OR 7 IN (LRN.FROM_KEY_NUM, LRN.TO_KEY_NUM) AND (EXISTS (SELECT CITY FROM LOCATION WHERE CITY IN (LRN.FROM_LOW_KEY_VALUE, LRN.TO_LOW_KEY_VALUE))
         )     
        )
    AND TLR.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')
    ORDER BY TLR.CARRIER_ID;

Open in new window

Wilder1626

ASKER

eX:

iF IN LRN.FROM_LOW_KEY_VALUE i have the value ID: 16.

If in the location ID, i have 16 and that the city for that ID is MONCTON, that i have a match.

Other example

iF IN LRN.FROM_LOW_KEY_VALUE i have the value ZIP: J4G 5J1
If in the location ZIP, i have J4G 5J1 and that the city for that ZIP is MONCTON, that i have a match.

Geert G

your english is rather hard to understand

are you referring to a "case" structure ?
case
when 6 >= LRN.FROM_KEY_NUM and 6 <= LRN.TO_KEY_NUM
then (select id from LOCATION WHERE ID >= LRN.FROM_LOW_KEY_VALUE
and ID <= LRN.TO_LOW_KEY_VALUE where rownum <= 1)
when 8 >= LRN.FROM_KEY_NUM and 8 <= LRN.TO_KEY_NUM
then (SELECT ZIP FROM LOCATION WHERE
(ZIP >= LRN.FROM_LOW_KEY_VALUE AND ZIP <= LRN.TO_LOW_KEY_VALUE)
OR (ZIP >= LRN.FROM_HIGH_KEY_VALUE AND ZIP <= LRN.TO_HIGH_KEY_VALUE))
when 7 >= LRN.FROM_KEY_NUM and 7 <= LRN.TO_KEY_NUM
THEN (SELECT CITY FROM LOCATION WHERE
CITY >= LRN.FROM_LOW_KEY_VALUE AND CITY <= LRN.TO_LOW_KEY_VALUE)
else 'NOTHING FOUND'
end

Wilder1626

ASKER

hi Geert_Gruwez

im more talking about the WHERE clause and this part of the SQL.

Lets only take this part of the of the code for now:

AND (   6 IN (LRN.FROM_KEY_NUM, LRN.TO_KEY_NUM) AND (EXISTS (SELECT ID FROM LOCATION WHERE ID IN (LRN.FROM_LOW_KEY_VALUE, LRN.TO_LOW_KEY_VALUE))
         )

Open in new window

In the above SQL, you see 6 IN. That will tell me that LRN.FROM_KEY_NUM and LRN.TO_KEY_NUM will have IDs in LRN.FROM_LOW_KEY_VALUE and LRN.TO_LOW_KEY_VALUE.

In LRN.FROM_LOW_KEY_VALUE and LRN.TO_LOW_KEY_VALUE, you will see the ID itself;
Ex: 16

Now in the Location table, if 16 exist: EXISTS (SELECT ID FROM LOCATION WHERE ID IN (LRN.FROM_LOW_KEY_VALUE, LRN.TO_LOW_KEY_VALUE)), i also need to validate if that ID city from the location table have the city MONCTON.

something like EXISTS (SELECT ID FROM LOCATION WHERE ID IN (LRN.FROM_LOW_KEY_VALUE, LRN.TO_LOW_KEY_VALUE)) and if the City from that ID in the location table = 'MONCTON'

hope that it make more sense.

Thanks again

ASKER CERTIFIED SOLUTION

Geert G

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Wilder1626

ASKER

OK.
I think i was able to do what i needed but still need to validate if my result is exactly what i need.

6 IN (LRN.FROM_KEY_NUM) AND (EXISTS (SELECT ID FROM LOCATION WHERE ID IN (LRN.FROM_LOW_KEY_VALUE) AND CITY IN ('MONCTON'))

Open in new window

I will let you know, based on your previous post if i still have an issue.

PortletPaul

The simplest way to fully describe a problem is to provide sample data
& provide "expected results", can you provide these?

{+edit, sorry}

Wilder1626

ASKER

Hi Geert_Gruwez

Thanks for your help. It was long but i was able to make it work based on your last post