Solved

Oracle - Were clause issue

Posted on 2013-11-13
7
280 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
ID: 39646679
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 37

Expert Comment

by:Geert Gruwez
ID: 39647139
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
ID: 39647224
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 37

Accepted Solution

by:
Geert Gruwez earned 500 total points
ID: 39647246
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
ID: 39647289
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
ID: 39649970
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
ID: 39674256
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
mySql Syntax 7 44
SQL Error in WHERE Clause 5 42
Split one comma delimited string in a column into 7 (SQL server 2008) 13 69
Repeat query 13 25
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

863 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

19 Experts available now in Live!

Get 1:1 Help Now