CODEWITHSUNDEEP
sqloracle-databaseselectsubquerycase
zeppelin
zeppelin

Reputation: 451

NESTED SELECT QUERY WITH CASE

Please have a look at the query.
The issue I am facing is this:
The FROM_LOCATION,TO_LOCATION in THEN clause needs to be the value from B_TARIFFS, However when I prefix it with B_TARIFFS.FROM_LOCATION and B_TARIFFS.TO_LOCATION , I get an error that ORA-00904: Invalid identifier.. If it is available in ELSE clause then why cant I use it in the THEN clause. What can I do to get my desired result.
The result I want from the query is this: Get from_location,to_location, tariff (based on case from another table). I am expecting that the Outer query will pass its FROM_LOCATION,TO_LOCATION to the inner select queries in CASE ELSE THEN, and based on that a tariff value would be returned which will be the third column of the outer query. What can I do to achieve this. 

select FROM_LOCATION, TO_LOCATION,

        case when (
            select  tariff A_tariff where A_tariff.FROM_LOCATION = B_TARIFFS.FROM_LOCATION AND A_tariff.TO_LOCATION = 
            B_TARIFFS.TO_LOCATION AND TO_DATE(sysdate,'YYYY-MM-DD')   between TO_DATE(from_date,'YYYY-MM-DD') and TO_DATE(to_date,'YYYY-MM-DD') 
            and rownum = 1
        ) = ''  
        then  

        (SELECT TARIFF FROM (
        SELECT * FROM A_TARIFF WHERE TRIM(A_TARIFF.FROM_LOCATION) = TRIM(**FROM_LOCATION**) AND 
        TRIM(A_TARIFF.TO_LOCATION) = TRIM(**TO_LOCATION**) ORDER BY A_TARIFF.FROM_DATE DESC 
        ) WHERE ROWNUM = 1) 

        else 

        (select  tariff from A_tariff where A_tariff.FROM_LOCATION = B_TARIFFS.FROM_LOCATION AND A_tariff.TO_LOCATION = 
        B_TARIFFS.TO_LOCATION AND TO_DATE(sysdate,'YYYY-MM-DD')   between TO_DATE(from_date,'YYYY-MM-DD') and TO_DATE(to_date,'YYYY-MM-DD') 
        and rownum = 1) end tariff

        FROM B_TARIFFS
        WHERE FROM_LOCATION LIKE '%ABC%' AND AREA_CODE = 'X' ORDER BY TARIFFS;

Upvotes: 0

Views: 2827

Answers (2)

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

As mentioned by @alexandergs the cause of the error is too deep nesting of subquery. But you have bigger problems than that. In your when clause you make decision depending on some random row, because there is condition rownum=1 without ordering. The same is in else clause. Also it is not clear what this when (select ...) = '' means - I suspect that you want to show something not empty, but current construcion does not provide this. You have to rebuild your query to something like here:

select b.from_location, b.to_location, a.tariff, b.tariffs, 
    row_number() over (partition by b.from_location, b.to_location 
                           order by a.from_date desc) rn
  from b_tariffs b
  left join a_tariff a 
         on a.from_location = b.from_location and a.to_location = b.to_location
         and trunc(sysdate) between trunc(a.from_date) and trunc(a.to_date) 
  where b.from_location like '%ABC%' and b.area_code = 'X' order by b.tariffs;

... eventually add some more code for row_number() - so it sorts rows depending on your needs and take rows with RN = 1.

Upvotes: 1

alexandergs
alexandergs

Reputation: 192

Your problem is in the double nesting.

Why don't you change:

    (SELECT TARIFF FROM (
    SELECT * FROM A_TARIFF WHERE TRIM(A_TARIFF.FROM_LOCATION) = TRIM(**FROM_LOCATION**) AND 
    TRIM(A_TARIFF.TO_LOCATION) = TRIM(**TO_LOCATION**) ORDER BY A_TARIFF.FROM_DATE DESC 
    ) WHERE ROWNUM = 1)

with

    SELECT TARIFF FROM A_TARIFF WHERE TRIM(A_TARIFF.FROM_LOCATION) = TRIM(B_TARIFFS.FROM_LOCATION) AND 
    TRIM(A_TARIFF.TO_LOCATION) = TRIM(B_TARIFFS.TO_LOCATION) WHERE ROWNUM = 1
    ORDER BY A_TARIFF.FROM_DATE DESC

Upvotes: 1

Related Questions