Show column according to parameter-CodePudding

I'm trying to write a SQL script that shows a column based on a user parameter. I can't find an acceptable answer anywhere.

This is a simplified version of what I want to do (in OracleDB):

SELECT Table_car.purchase_date  AS PurchaseDate,
       Table_car.sale_date AS SaleDate,
       CASE WHEN :userinput <> 999 THEN (SELECT Table_car.car_brand FROM Table_car WHERE Table_car.car_brand = :userinput ) 
       ELSE (SELECT Table_car.car_brand FROM Table_car) 
       END AS CarBrand

FROM Table_car

The pseudo code of what I want could be

if(user_input = 999)
   show_all_values_of(Table_car.car_brand );
else
   show_only_some_values_of(Table_car.car_brand, user_input);

This is the expected result:

if user_input <> 999 (if is 1, for example)
| PurchaseDate | SaleDate   | CarBrand |
 -------------- ------------ ---------- 
|  03/12/2018  | 09/10/2021 |    1     |
----------------------------------------
|  13/06/2011  | 20/11/2021 |    1     |
----------------------------------------
|  22/01/2020  | 09/12/2021 |    1     |
----------------------------------------

else (if user input is 999)
| PurchaseDate | SaleDate   | CarBrand |
 -------------- ------------ ---------- 
|  03/12/2018  | 09/10/2021 |    1     |
----------------------------------------
|  13/06/2001  | 20/11/2021 |    5     |
----------------------------------------
|  22/01/2020  | 09/12/2021 |    1     |
----------------------------------------
|  03/12/2018  | 09/10/2021 |    3     |
----------------------------------------
|  13/06/2012  | 10/10/2020 |    9     |
----------------------------------------
|  22/01/2020  | 09/12/2020 |    2     |
----------------------------------------

CodePudding user response：

You can just put the logic into the where clause, with or; something like:

SELECT Table_car.purchase_date AS PurchaseDate,
       Table_car.sale_date AS SaleDate,
       Table_car.car_brand AS CarBrand
FROM   Table_car
WHERE  :userinput = 999 OR Table_car.car_brand = :userinput

If your column was nullable you might want additional logic to handle that in a particular way, but it doesn't look like this should be. And if you need other conditions in the where, remember to put parentheses around the logic above to make sure it's interpreted as you intend; i.e.

WHERE  (:userinput = 999 OR Table_car.car_brand = :userinput)
AND ... something else

As an aside, you might want to consider using table aliases to reduce the typing a bit.