CODEWITHSUNDEEP
sqloracle-databasecasewhere-clausewindow-functions
Jett
Jett

Reputation: 880

How to use a case statement on rownum from a select count in oracle sql?

I'm a bit new to SQL and have a question concerning a case statement in the where clause based on the count(*).

I have a table called CARS, and I am querying for specific Manufacturers and dates in which the car was manufactured on.

From there, let's say I am randomly checking on some cars to ensure that the data is correct so I'd like to first randomly sort the result and then pick out 'x' amount of rows based on how many results I get back (the more results from the query, the more I randomly check)

In my query below, I first get the specific cars from the CARS table, get the count of how many rows that result is, and then I'd like to do a case statement saying: If there are 'x' many cars in the result, here are the first 'y' rows.

select count(*) over() as carcount,
    vehicles.*
from (
        select *
        from CARS
        where MANUFACTURER IN (
                'BMW',
                'Ford',
                'Volkswagen',
                'Toyota',
                'Saab',
                'Porsche',
                'Hyundai',
                'Alfa Romeo'
            )
            and MANUFACTURED_DATE >= sysdate
            and MANUFACTURED_DATE <= (SYSDATE + 30)
            and MANUFACTURED_DATE is not null
        ORDER BY DBMS_RANDOM.RANDOM
    ) vehicles
where rownum < (
        CASE
            WHEN carcount = 1 THEN 1
            WHEN carcount = 2 THEN 2
            WHEN carcount >= 3 AND carcount <= 4 THEN 2
            WHEN carcount >= 5 AND carcount <= 14 THEN 4
            WHEN carcount >= 15 AND carcount <= 52 THEN 15
            WHEN carcount >= 53 AND carcount <= 365 THEN 25
            WHEN carcount > 365 THEN 30
            ELSE 0
        END
    );

When I run my query, I am getting the error:

ORA-00904: "carcount": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:

I am not quite sure why carcount is not a valid identifier and how I can get the first 'x' rows in my random result. I would appreciate any help/advice - thank you in advance!

Upvotes: 0

Views: 1199

Answers (1)

GMB
GMB

Reputation: 222502

carcount should be computed in the subquery for the query to just work. I would take one step further and use row_number() rather than rownum:

select *
from (
    select 
        c.*, 
        row_number() over(order by dbms_random.random) rn,
        count(*) over() cnt
    from cars c
    where 
        manufacturer in (
            'bmw',
            'ford',
            'volkswagen',
            'toyota',
            'saab',
            'porsche',
            'hyundai',
            'alfa romeo'
        )
        and manufactured_date >= sysdate
        and manufactured_date <= sysdate + 30
) c
where rn <= case
    when cnt >  365 then 30
    when cnt >= 53  then 4
    when cnt >= 15  then 15
    when cnt >= 5   then 4
    when cnt >= 2   then 2
    else 1
end

Side notes:

  • condition and manufactured_date is not null in the subquery is superfluous - you have inequality predicates on this column already, which filter out null values

  • the case expression can be simplified by ordering the conditions with the high values first - there also are some some redundant conditions, and the target number of rows in the second branch seems unfairly low

  • it seems like you want <= rather than < in the inequality condition on the row number (otherwise, your query would, for example, filter out the results when the subquery returns just 1 row)

Upvotes: 1

Related Questions