Search code examples
sqlhsqldb

HSQL - Adding a new column in the query based on an IF condition

I need to generate a column, where in each row it checks if the Bestand is less than, equal or greater than Minbestand. If so the new column called Bestandsbewertung needs to give out Mangel, Mindestbestand or Überfluß respectively.

SELECT IF(Bestand < Minbestand, 'Mangel', IF(Bestand = MinBestand, 'Mindestbestand', 'Überstand')) AS Bestandsbewertung, Teile.*
FROM Teile

I tried this code but HSQL says it doesn't know IF.

This is what the Table looks like if it helps

Solution

  • It seems HSQL doesn't accept IF. After some trial and error I came to this solution to the problem.

    SELECT Teile.*,
       CASE Bestand WHEN < MinBestand THEN 'Mangel'
                    WHEN = MinBestand THEN 'Mindestbestand'
                    WHEN > MinBestand THEN 'Überfluß' END AS Bestandsbewertung
FROM Teile

    It seems to work without any issues.