Have a calculated field In a Query Hours Field *24.71 and it works fine would like to use an IF Statement
If “Date” =>12/06/22,“Hours”*22.50,“Hours”*24.71)
when i try to run the query says not found
Thanks
Please have a look at the documentation of the database your are using. If you will work with internal database: Base Guide.
For both internal databases will work
CASE WHEN expr1 THEN v1
[WHEN expr2 THEN v2]
[ELSE v4]
END
So:
CASE WHEN "Date" >= '2022-06-12' THEN "Hours"*22.5
ELSE "Hours"*24.71
END
Regards
Robert
getting this when i try to run query
Wrong data type: java.lang.IllegalArgumentException in statement [SELECT DISTINCT “Trips”.“Date”, “Routes”.“Route”, “Driver”.“Driver”, “Bus”.“BusNo#”, “Trips”.“Hours”, CASE WHEN “Date” >= ‘12/06/2022’ THEN “Hours” * 22.5 ELSE “Hours” * 24.71 END FROM “Trips”, “Routes”, “Driver”, “Bus” WHERE “Trips”.“RouteID” = “Routes”.“RouteID” AND “Trips”.“DriverID” = “Driver”.“DriverID” AND “Trips”.“BusID” = “Bus”.“ID” ORDER BY “Trips”.“Date” ASC]
is that »Date« really messformatted MM/DD/YY
Date in SQL: YYYY-MM-DD
No other format will be accepted.
if i put it like that it will run but only gives me the true statement for all entries
My date format is mm/dd/yyyy maybe it is confused
i have the date field formatted the same as the SQL still only goes to the true statement
it does work now. just odd that is the only format you can use in the sql
Actually more odd is the variety of date formats used around the world. YYYY-MM-DD is a standard used in databases. I do my best to use this format whenever posting here regardless of how I was taught.
@karolus : Indeed, I didn’t know it is format MM/DD/YY. Thought it is the same as in Germany, but with different delimiter: DD.MM.YY
I have been around Microsft Access and sql server and never run into this. not at all very good with Sql thats why installed Libre I like the challenge