0

so I have this function in mysql syntax(on a mysql DB) that I'm trying to run on Oracle and can't seem to find the similar function in Oracle for MYSQL "IF".

SELECT 
table1.PROJECT_ID
max(if(Table2.Typ = 'progress', 'progress',NULL)) AS 'progress',
max(if(Table2.Typ = 'acquired', 'acquired',NULL)) AS 'acquired'
FROM Table1
LEFT JOIN Table2 ON Table2.Name = Table1.Item_Description
GROUP BY PROJECT_ID;
DiskoSuperStar
  • 35
  • 5

1 Answers1

0

Use a CASE expression (which is in the ANSI standard):

SELECT table1.PROJECT_ID,
       max(CASE WHEN Table2.Typ = 'progress' THEN 'progress' ELSE NULL END) AS progress,
       max(CASE WHEN Table2.Typ = 'acquired' THEN 'acquired' ELSE NULL END) AS acquired
FROM   Table1
       LEFT JOIN Table2 ON Table2.Name = Table1.Item_Description
GROUP BY PROJECT_ID;
MT0
  • 143,790
  • 11
  • 59
  • 117
  • That pretty much solves it. Thanks alot! :) – DiskoSuperStar Jun 10 '22 at 08:29
  • Any idea why the query doesn't work if I remove max function? – DiskoSuperStar Jun 10 '22 at 08:56
  • @DiskoSuperStar Because you have a `GROUP BY` clause and every column you refer to in the `SELECT` either needs to be in the `GROUP BY` clause or wrapped in an aggregation function. – MT0 Jun 10 '22 at 08:58
  • Makes sense, but this is a very weird way to get the results using the max function. I believe I will have to rewrite this query. – DiskoSuperStar Jun 10 '22 at 11:41
  • @DiskoSuperStar You have not provided a [MRE] with the `CREATE TABLE` and `INSERT` statements or details of the expected output so all we can answer is the question you have asked "What is the equivalent function in Oracle for IF function in MySQL?". If you have a follow-up question then I suggest that you [ask a new question](https://stackoverflow.com/questions/ask) and give all the necessary details. – MT0 Jun 10 '22 at 11:46