0

Please could you advise? Can we use if else condition in where clause of oracle?

I should execute such example:

We should select all files with name "Car". And if there is no 'car', i should select "Motorbike".

in other words, it should work like this: IF(name = 'Car') then smth related to Car else if(name = 'Car' does not exist) then smth related to Motorbike.

We have multiple tables: One table is related to above one is .

A, B, C, ..., Vehicle.

in query, I want to do like this:

SELECT a.(some field), b.(some field), c.(some field), vehicle.(some field)
FROM a, b, c, ..., vehicle
WHERE 
if vehicle = 'CAR' THEN a.(some field) = vehicle.(some field)
else if vehicle = 'CAR' does not exist, then a.(some field) = vehicle(MOTOR).(some field)


Table Vehicle (idV, idAll, nameVehicle, vehicleProductionDate):


1. 1, 1ABC, Car, 26-Apr-15
2. 2, 1ABC, Car, 26-Apr-16
3. 3, 1ABC, Car, 26-Apr-17
4. 4, 1ABC, Bike, 26-Apr-18
5. 5, 1ABC, Motorbike, 26-Apr-18

6. 6, 2ABC, Bike, 26-Apr-15
7. 7, 2ABC, Bike, 26-Apr-16
8. 8, 2ABC, Bike, 26-Apr-17
9. 9, 2ABC, Motorbike, 26-Apr-15
10. 10, 2ABC, Motorbike, 26-Apr-17
11. 11, 2ABC, Motorbike, 26-May-16


Table Step (idS, idAll, nameStep, logStepDate):

 1. 1, 1ABC, Testing, 25-Apr-17
 2. 2, 2ABC, Logging, 25-Apr-17

Table Company: (idC, idAll, nameCompany):

 1. 1, 1ABC, Jeep
 2. 2, 2ABC, Ford

And my final result, when executing, should contain all 3 tables data:
1, 1ABC, Jeep, Testing, 25-Apr-17, Car, 26-Apr-16
2, 2ABC, Ford, Logging, 25-Apr-17, Motorbike, 26-May-16

If you see there, log date should be equal or greater than vehicle Production Date and if there are several vehicles which are less, then we should take the latest one of less vehicles.

Nureke
  • 11
  • 4
  • So, you want to select all files named "Car" ***OR*** "Motorbike"? Can you provide an example or two? – default locale Apr 25 '18 at 11:41
  • What's the problem with this question, by the way: https://stackoverflow.com/questions/49999102/how-can-i-put-if-else-condition-in-oracle-query ? – default locale Apr 25 '18 at 11:44
  • They are both similar questions, but there, i am asking, is it possible to get these data by IF-ELSE Statement in Where clause. – Nureke Apr 25 '18 at 11:48
  • I want "Car" if there are both Car and Motorbike, and if there is no CAR, but there is Motorbike, i should select Motorbike in that case – Nureke Apr 25 '18 at 11:50
  • Please, post examples, as you did in the earlier question. You also need to make it clear how exactly this question is different from the previous one. Try to take one of the answers there, apply it to your sample here (cars and motorbikes), then explain what exactly you want to improve. – default locale Apr 25 '18 at 11:57
  • It's still unclear, I'm afraid. What do `vehicle(MOTOR)` and `does not exist` mean in this context? Can you create a sample of input data and the expected result? Simple example with three tables will be enough. – default locale Apr 25 '18 at 14:06
  • could you look through my question again – Nureke Apr 26 '18 at 04:40
  • Well, this is a complicated query with multiple issues and you need to concentrate on one. You can try to split it into steps: 1) join all three tables (`inner join`); 2) filter out vehicles produced after log date (`where`); 3) group by company (`group by`); 4) find the latest production date (`max`); 5) find the name of the latest vehicle ([link](https://stackoverflow.com/questions/8898020/oracle-taking-the-record-with-the-max-date)). Which one of these steps is giving you problems? What all of this has to do with the original question (if, else, cars, motorbikes)? – default locale Apr 26 '18 at 04:57
  • Yes, as you see there, if record has Car, it takes car, but if there is no, it takes motorbike, how can i execute this? for this clause, i thought if-else will help, and if oracle has something like *contains* function as other programming languages, it would be easy to execute. – Nureke Apr 26 '18 at 05:07
  • What do you mean `if there is no`? There is a motorbike and it just happens to be the latest vehicle for Ford: `11. 11, 2ABC, Motorbike, 26-May-16`. As far as I can see, there is no need to treat cars and motorbikes differently. What you need is just to select the vehicle(-s) with the latest date. There're multiple ways to do it, check this question: https://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column – default locale Apr 26 '18 at 05:11
  • This is not latest, as you see. it is for year 2016. It should look not to the final one. It should look to the name, it should take with the name CAR, otherwise it should take record with the name MOTORBIKE. – Nureke Apr 26 '18 at 05:21
  • Let's go through this step-by-step. 1. First, we select all the vehicles for Ford (`2ABC`): records from 6 to 11. 2. Then we select vehicles for which production date is earlier than or equal to `logStepDate` which is 25.04.2017: records 6,7,9 and 11. 3. Then we select the vehicle with the latest date and this is the record #11 (motorbike, 26.05.2016). What am I missing here? – default locale Apr 26 '18 at 05:32

0 Answers0