Base cannot find a column

[BournvilleBob]

LibreOfiice 7.4, Windows

I am trying to build a query that uses the CASE WHEN function to check a transaction date and sets the appropriate financial year

SELECT “ID”,
CASE
WHEN “Date” < “31/10/22” THEN ‘2022’
WHEN “Date” < “31/10/23” THEN ‘2023’
END AS “FinYear”,
“Date”, “Paid in”, “Paid out”
FROM “EventTransactions”

Base can save the query, which I take to mean that syntax is correct but when I test the query I get the following alarm

Column not found: 31/10/22 in statement [SELECT “ID”, CASE WHEN “Date” < “31/10/22” THEN ‘2022’ WHEN “Date” < “31/10/23” THEN ‘2023’ END AS “FinYear”, “Date”, “Paid in”, “Paid out” FROM “EventTransactions”]

I have tried also a CASEWHEN (exp,result1, result2) and that bombs out too.

What am I doing wrong.

Many thanks

Robert

[Villeroy]

SELECT "ID",
CASE
WHEN "Date" < '2022-10-22' THEN '2022'
WHEN "Date" < '2023-10-31' THEN '2023'
END AS "FinYear",
"Date", "Paid in", "Paid out"
FROM "EventTransactions"

[cpb]

@BournvilleBob,
.
Villeroy has shown what it is you are doing wrong.
.
it looks like you use HSQLDB?
.
Just for your information:
it’s unnecessary to use WHEN more than once no matter how many different years you may have.
as “FinYear” is defined as 31 Oct (last day of month) this code is all that you need.
.
EDIT: replaced the curly double quotes that you used to enclose “FinYear”.

	case
		when month("Date") <= 10 then
			year("Date")
		else
			year("Date") + 1
	end
	"FinYear",

in the UK the tax year runs from 06 April to 05 April.
we can find the tax year like this (both day and month are required).

	case
		when month("Date") <= 4 and day("Date") <= 5 then
			year("Date") -1
		else
			year("Date")
	end
	"TaxYr",

[BournvilleBob]

Hello Villeroy,

I tried the format you show first as I read that that is native date format in SQL but it didn’t work.
I am at work now, I will try again tonight using ‘copy & paste’

[BournvilleBob]

Thank you cpb for the code - I will study it later on.

I am using Base with the embedded HSQLDB but I really want to learn ‘native’ SQL rather than the GUI as the Base Guide suggests that there are limitations to the GUI. I must say that I find structuring SQL statements quite challenging.

The database isn’t intended for use with UK tax year but the financial year of a small club. For accounts reporting purposes I need to show financial activity between Nov 1st and Oct 1st.

There will be addtional problems when I come to recording membership payments as we accept renewals up until January 31st and still count the payment as the previous financial year. I am no accountant.

Thanks again

Robert

[Villeroy]

@BournvilleBob

I am using Base with the embedded HSQLDB but I really want to learn ‘native’ SQL rather than the GUI as the Base Guide suggests that there are limitations to the GUI.

The Base syntax is “native SQL”, however you must not expect that the GUI (design mode or wizard) is able to cover more than “native baby SQL” which is no more than you can learn within a lazy afternoon. It helps to compile a list of column names from a few joined tables. Anything else should be continued in SQL view or in a dedicated code editor.

[BournvilleBob]

Good evening Villeroy,
I was going in the wrong direction! I thought it was the format of the date that was at fault but in truth it was double quotes around the date when it should have been single quotes.

I get very confused with quotation marks in SQL. Some books I read say that quotes are not required, in Base there are a mixture of double quotes and single quotes and from what I have read, single quotes denote a string and double quotes are for an exact string. I haven’t found the section in the Base Guide (V7.4) that gives a clear explanation of quotes and their importance (and they obviously are very important).

[BournvilleBob]

Hello cpb,

I have studied you code and got it to work. How elegant! Thank you, I have learnt a new method, one I hadn’t thought about (but something I have done before in another like whilst programming VBA macros for Excel).

I shall now tackle the condition for the membership renewals.

Thanks again

Robert

[Villeroy]

SELECT COL1, COL2, COL3 FROM TABLE1 //upper case names, no SQL keywords in names
SELECT "INTEGER" FROM "SELECT" // SQL keywords are possible when quoted

Since the column names and the table names can be renamed freely in the user interface, you can use short upper case names.