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
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;
Oracle DatabaseSQL
Last Comment
Wilder1626
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
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
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:
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
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))
)
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.
I will let you know, based on your previous post if i still have an issue.
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'))
I will let you know, based on your previous post if i still have an issue.
The simplest way to fully describe a problem is to provide sample data
& provide "expected results", can you provide these?
{+edit, sorry}
& provide "expected results", can you provide these?
{+edit, sorry}
ASKER
Hi Geert_Gruwez
Thanks for your help. It was long but i was able to make it work based on your last post
Thanks for your help. It was long but i was able to make it work based on your last post
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.