Solved

Oracle - Were clause issue

Posted on 2013-11-13
7
278 Views
Last Modified: 2013-11-25
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

0
Comment
Question by:Wilder1626
  • 4
  • 2
7 Comments
 
LVL 11

Author Comment

by:Wilder1626
Comment Utility
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.
0
 
LVL 36

Expert Comment

by:Geert Gruwez
Comment Utility
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
0
 
LVL 11

Author Comment

by:Wilder1626
Comment Utility
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
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 36

Accepted Solution

by:
Geert Gruwez earned 500 total points
Comment Utility
nope still doesn't make sense
i was talking about the where too

what are you giving the query as input parameters and what are you expecting as output ?
forget the query syntax, that's not helping in the problem

> are you giving an id to the query and expecting a city returned ?


on a side note:
the x in (a, b) clause
will only test x for 2 values,  not the range of values

case when 3 in (2, 4) then 'True' else 'False' end
will give 'False' as result

case when 3 >= 2 and 3 <= 4 then 'True' else 'False' end
will give 'True' as result
0
 
LVL 11

Author Comment

by:Wilder1626
Comment Utility
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.
0
 
LVL 48

Expert Comment

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

{+edit, sorry}
0
 
LVL 11

Author Closing Comment

by:Wilder1626
Comment Utility
Hi Geert_Gruwez

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

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

763 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

10 Experts available now in Live!

Get 1:1 Help Now