Solved

Oracle - Were clause issue

Posted on 2013-11-13
7
283 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

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 shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

737 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