[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Oracle - Were clause issue

Posted on 2013-11-13
7
Medium Priority
?
288 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 38

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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 38

Accepted Solution

by:
Geert Gruwez earned 2000 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 49

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

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Question has a verified solution.

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

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

591 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