How to word a parameter input query in Base using Mariadb to select a date range?

Wanderer · July 2, 2023, 10:09pm

Start with posting the query from sql-view here, so we know, wich query fails. Some of us also work with MariaDB/MySQL and may have a look.
.
Usually I would suggest to switch to direct mode for SQL first, if I read “query could not be parsed”, but as it seemed to work with HSQLDB, I’m not convinced it will help in this case.

rherbert · July 2, 2023, 10:28pm

Hi @Wanderer. Thank you for responding.

Two comments back queries with test data were sent in an odb file:

TimeQryTestV1.odb (213.1 KB)

@parsely, may I post your code directly here?

Richard

Wanderer · July 2, 2023, 10:32pm

I can read sql also on my mobile, testing an odb will have to wait until I have time with my desktop…
.
And I assumed there is more than one query, so I/we have to find the place with the error, while you already know where it occurs.

rherbert · July 2, 2023, 10:40pm

I will go with the sharing of code on this forum with the odb file permits sharing with a copy and paste. The two qeiries are:

“Basic Billing”

SELECT "tbl_Organization"."Organization_ID",
 "tbl_File"."File_ID", "tbl_Time"."ToDo_ID", "tbl_Time"."Date", "tbl_Time"."Start_Time",
 "tbl_Time"."Stop_Time",
 CASEWHEN( DATEDIFF( 'MINUTE', "Start_Time", "Stop_Time" ) > 0, DATEDIFF( 'MINUTE', "Start_Time", "Stop_Time" ), 1440 - DATEDIFF( 'MINUTE', "Stop_Time", "Start_Time" ) ) AS "Difference",
 CASEWHEN( DATEDIFF( 'MINUTE', "Start_Time", "Stop_Time" ) > 0, DATEDIFF( 'MINUTE', "Start_Time", "Stop_Time" ), 1440 - DATEDIFF( 'MINUTE', "Stop_Time", "Start_Time" ) ) * "Hourly Rate" / 60 AS "Invoice",
 RIGHT( CONCAT( '000', "File_ID" ), 3 )  AS "number",
 "tbl_File"."Project_Title",  "tbl_Individual"."Name",
 "tbl_Organization"."Name",  "tbl_Organization"."Address"
 FROM "tbl_Time", "tbl_Organization",  "tbl_ToDo", "tbl_Individual",  "tbl_File"
 WHERE "tbl_Time"."Organization_ID" = "tbl_Organization"."Organization_ID"
 AND "tbl_Time"."ToDo_ID" = "tbl_ToDo"."ToDo_ID"
 AND "tbl_ToDo"."Individual_ID" = "tbl_Individual"."Individual_ID"
 AND "tbl_ToDo"."File_ID" = "tbl_File"."File_ID"
 AND "tbl_Time"."Date" > :start
 AND "tbl_Time"."Date" < :stop

“Grouped Billing”

SELECT "tbl_Organization"."Organization_ID" AS "OrganizationID",
  MIN( "tbl_File"."File_ID" ) AS "FileID",
  COUNT( "tbl_ToDo"."ToDo_ID" ) AS "Count",
  SUM( CASEWHEN( DATEDIFF( 'MINUTE', "Start_Time", "Stop_Time" ) > 0, DATEDIFF( 'MINUTE', "Start_Time", "Stop_Time" ), 1440 - DATEDIFF( 'MINUTE', "Stop_Time", "Start_Time" ) ) ) AS "Minutes",
  SUM( CASEWHEN( DATEDIFF( 'MINUTE', "Start_Time", "Stop_Time" ) > 0, DATEDIFF( 'MINUTE', "Start_Time", "Stop_Time" ), 1440 - DATEDIFF( 'MINUTE', "Stop_Time", "Start_Time" ) ) * "Hourly Rate" / 60 ) AS "Invoice",
  MIN( CONCAT( RIGHT( CONCAT( '000', "Organization_ID" ), 3 ), RIGHT( CONCAT( '000', "File_ID" ), 3 ) ) ) AS "number",
  MIN( "tbl_File"."Project_Title" ) AS "ProjectTitle",
  MIN( "tbl_Organization"."Name" ) AS "NameOrganization",
  MIN( "tbl_Organization"."Address" ) AS "Address"
  FROM "tbl_Time", "tbl_Organization", "tbl_ToDo", "tbl_Individual", "tbl_File"
  WHERE "tbl_Time"."Organization_ID" = "tbl_Organization"."Organization_ID"
  AND "tbl_Time"."ToDo_ID" = "tbl_ToDo"."ToDo_ID"
  AND "tbl_ToDo"."Individual_ID" = "tbl_Individual"."Individual_ID"
  AND "tbl_ToDo"."File_ID" = "tbl_File"."File_ID"
  AND "tbl_Time"."Date" > :start
  AND "tbl_Time"."Date" < :stop
  GROUP BY "tbl_Organization"."Organization_ID"

Thanks for looking at this.
Richard

parsely · July 3, 2023, 4:31am

DATEDIFF and CONCAT are OK with MariaDB, I suspect CASEWHEN must be replaced by a simple IF as documented built-in-functions

The invoice sequence number is a critical item, it can be easily added in a spreadsheet and hard in SQL?. In hindsight the composition of invoices must then be based on the spreadsheet. insert field > more fields > database Tab > add datasource > select the spreadsheet > and so on
With this approach only the sum of all projects for one organisation can be presented and no specification line showing each project.

Wanderer · July 3, 2023, 5:22am

I would start there also, but expect the “traditional” command CASE WHEN … THEN … ELSE … END; as syntax.

CASE WHEN DATEDIFF( 'MINUTE', "Start_Time", "Stop_Time" ) > 0 THEN DATEDIFF( 'MINUTE', "Start_Time", "Stop_Time" ) ELSE 1440 - DATEDIFF( 'MINUTE', "Stop_Time", "Start_Time" )  END AS "Minutes"

Villeroy · July 3, 2023, 10:28am

Read up the documentation of your database engine. DATEDIFF is defined somewhat different: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_datediff
In My/MariaSQL uses a function TIMESTAMPDIFF similar to HSQL’s DATEDIFF.

rherbert · July 3, 2023, 11:48am

Thank you @parsely, @Wanderer, and @Villeroy.

I will begin getting through I note that I have come across quotation differences between HSQLDB and Mariadb that are likely old hat for you. Examples are:

With a find and replace
HSQLDB
UPDATE "tbl_reference"
 SET "Link" = REPLACE("Link", 'home/rherbert/Documents', 'mnt/CAID')
Mariadb
UPDATE tbl_reference
 SET Link = REPLACE(Link, 'home/rherbert/Documents', 'mnt/CAID')

and with a list box query
HSQLDB
SELECT "Category", "Category_ID"
 FROM "tbl_category" 
Mariadb
SELECT `Category`, `Category_ID`
 FROM `Library`.`tbl_category` `tbl_category`

I shall attempt to convert this and get back to everyone.

Thank you,
Richard

rherbert · July 3, 2023, 12:06pm

Thank you @Villeroy. It note that mariadb supports both DATEDIFF and TIMESTAMPDIFF, but this provides other routes to explore if necessary.

rherbert · July 3, 2023, 1:51pm

@Wanderer, you wrote:

CASE WHEN DATEDIFF( 'MINUTE', "Start_Time", "Stop_Time" ) > 0 THEN DATEDIFF( 'MINUTE', "Start_Time", "Stop_Time" ) ELSE 1440 - DATEDIFF( 'MINUTE', "Stop_Time", "Start_Time" )  END AS "Minutes"

I have observed that SQL in mariadb handles double quotations differently. In some instances they are dropped and in others replaced by " ` " = "grave accent’. How does that effect what you wrote and does it affect the “RIGHT( CONCAT …” line?

Thanks,
Richard

Wanderer · July 3, 2023, 4:01pm

Quotes are not related to the question of CASEWHEN()-function versus statement-syntax CASE WHEN … THEN …

On quoting check the link to a thread thread at stackoverflow; You can often drop quotes when you have simple column-names like Price, Name. You need double quotes with spaces or special characters and single quotes for all string values. Backticks I only use when I put SQL in Text-Strings in Basic or other programming languages.

rherbert · July 3, 2023, 4:25pm

Hi @parsely,

I adjusted quotations to reflect SQL with mariadb (not certain if I have all corrected) and I followed your direction and changed CASEWHEN to IF. What I tried was:

SELECT `tbl_Organization`.`Organization_ID`,
 `tbl_File`.`File_ID`,
 `tbl_ToDo`.`ToDo_ID`,
 `tbl_Time`.`Date`,
 `tbl_Time`.`Start_Time`,
 `tbl_Time`.`Stop_Time`,
 IF( DATEDIFF( 'MINUTE', `Start_Time`, `Stop_Time` ) > 0, DATEDIFF( 'MINUTE', `Start_Time`, `Stop_Time` ), 1440 - DATEDIFF( 'MINUTE', `Stop_Time`, `Start_Time` ) ) `Difference`,
 IF( DATEDIFF( 'MINUTE', `Start_Time`, `Stop_Time` ) > 0, DATEDIFF( 'MINUTE', `Start_Time`, `Stop_Time` ), 1440 - DATEDIFF( 'MINUTE', `Stop_Time`, `Start_Time` ) ) * `Hourly Rate` / 60 `Invoice`,
 RIGHT( CONCAT( '000', `File_ID` ), 3 ) `Number`,
 `tbl_File`.`Project_Title`,
 `tbl_Individual`.`Name`,
 `tbl_Organization`.`Name`,
 `tbl_Organization`.`Address`,
 `tbl_Time`.`Date`
 FROM `CAID`.`tbl_ToDo` `tbl_ToDo`,
 `CAID`.`tbl_Individual` `tbl_Individual`,
 `CAID`.`tbl_Time` `tbl_Time`,
 `CAID`.`tbl_Organization` `tbl_Organization`,
 `CAID`.`tbl_File` `tbl_File`
 WHERE `tbl_ToDo`.`Individual_ID` = `tbl_Individual`.`Individual_ID`
 AND `tbl_Time`.`ToDo_ID` = `tbl_ToDo`.`ToDo_ID`
 AND `tbl_Time`.`Organization_ID` = `tbl_Organization`.`Organization_ID`
 AND `tbl_ToDo`.`File_ID` = `tbl_File`.`File_ID`
 AND `tbl_Time`.`Date` > :Start
 AND `tbl_Time`.`Date` < :Stop

The parsing error was resolved and there are not SQL syntax errors. I am able run the query and enter start/stop date parameters but then the following error occurs:

Screenshot_20230703_113447

I also put back CASEWHEN and got to the same point without an SQL error. I am uncertain what that indicates. I then experimented with some of @Wanderer 's suggestions but had consistent syntax errors.

What is missing? (I did put an Hourly Rate field in my ToDo table for the field you added to the test data set.)

Thank
Richard

parsely · July 3, 2023, 7:43pm

Hi Herbert,

It is certain that I can not be of assistance with things related to MariaDB, I put my hope to others reading the post

CASEWHEN is not supported by MariaDB, so use the IF(logical test, expressionTrue, expressionFalse) function.

You can click ‘more’ to check whether that gives you a hint of what is goiing on, as a last resort you can comment out all columns but one, to get rid of errors from complex expressions, and than rebuild the SQL. You can comment out text like this: /* this will be ignored by the SQL parser */ Beware of trailing/double commas in that case

rherbert · July 3, 2023, 8:39pm

Hi @parsely,

I understand your limit with mariadb. You have been a great help. I did not have a chance to thank you for the macro to export a query to LO Calc (.ods) - awesome. One question though on the macro, why create a Log table and use that with the button?

Thanks again,
Richard

rherbert · July 3, 2023, 9:38pm

Hi @Wanderer,

Thank you for the link on use of quotes with MySQL. I needed a resource to sort that out. As for CASE versus IF with my billing queries. As a beginner with SQL, it seemed to me that either could work. But, I shall default to your expertise and put my effort into CASE. There is CASE Operator and CASE Statement. after reading through descriptions, it seems CASE Operator is what I am looking for. I tried a number of combinations and received a syntax error for SQL.

Screenshot_20230703_181907

Combinations I tried, starting with your suggestions, are:

CASE WHEN DATEDIFF( 'MINUTE', "Start_Time", "Stop_Time" ) > 0 THEN DATEDIFF( 'MINUTE', "Start_Time", "Stop_Time" ) ELSE 1440 – DATEDIFF( 'MINUTE', "Stop_Time", "Start_Time" ) END AS "Difference"

CASE WHEN DATEDIFF( 'MINUTE', `Start_Time`, `Stop_Time` ) > 0 THEN DATEDIFF( 'MINUTE', `Start_Time`, `Stop_Time` ) ELSE 1440 – DATEDIFF( 'MINUTE', `Stop_Time`, `Start_Time` ) END AS `Difference`

CASE `Difference` WHEN DATEDIFF( 'MINUTE', `Start_Time`, `Stop_Time` ) > 0 THEN DATEDIFF( 'MINUTE', `Start_Time`, `Stop_Time` ) ELSE 1440 – DATEDIFF( 'MINUTE', `Stop_Time`, `Start_Time` ) END

CASE WHEN DATEDIFF( 'MINUTE', `Start_Time`, `Stop_Time` ) > 0 THEN DATEDIFF( 'MINUTE', `Start_Time`, `Stop_Time` ) ThEN 1440 – DATEDIFF( 'MINUTE', `Stop_Time`, `Start_Time` ) END AS `Difference`

CASE `Difference` WHEN  DATEDIFF( 'MINUTE', `Start_Time`, `Stop_Time` ) > 0 THEN DATEDIFF( 'MINUTE', `Start_Time`, `Stop_Time` ) THEN 1440 – DATEDIFF( 'MINUTE', `Stop_Time`, `Start_Time` ) END

There is no doubt I am doing something wrong, but what?

Richard

cpb · July 3, 2023, 11:39pm

@rherbert,
’
are you saying that your actual query is failing when executed in the query design window?
if so try this:

SELECT
	`tbl_Organization`.`Organization_ID`, 
	MIN( `tbl_File`.`File_ID` ) `FileID`, 
	COUNT( `tbl_ToDo`.`ToDo_ID` ) `Count`,
	SUM
	( 
		CASE
			WHEN `Stop_Time` > `Start_Time` then
				cast((time_to_sec(`Stop_Time`) - time_to_sec(`Start_Time`)) / 60 as int)
			else
				1440 - cast((time_to_sec(`Start_Time`) - time_to_sec(`Stop_Time`)) / 60 as int)
		end
	)
	AS `Minutes`,
	SUM
	( 
		CASE
			WHEN `Stop_Time` > `Start_Time` then
				(time_to_sec(`Stop_Time`) - time_to_sec(`Start_Time`)) / 60
			else
				1440 - (time_to_sec(`Start_Time`) - time_to_sec(`Stop_Time`)) / 60
		end
		* `Hourly Rate` / 60 
	) AS `Invoice`,
	MIN( CONCAT( RIGHT( CONCAT( '000', `tbl_Organization`.`Organization_ID` ), 3 ), RIGHT( CONCAT( '000', `tbl_File`.`File_ID` ), 3 ) ) ) AS `number`, 
	MIN( `tbl_File`.`Project_Title` ) `ProjectTitle`, 
	MIN( `tbl_Organization`.`Name` ) `NameOrganization`, 
	MIN( `tbl_Organization`.`Address` ) `Address`

FROM 
	`tbl_Time`, 
	`tbl_Organization`,
	`tbl_ToDo`, 
	`tbl_Individual`,
	`tbl_File` 
WHERE 
	`tbl_Time`.`Organization_ID` = `tbl_Organization`.`Organization_ID` 
	AND 
	`tbl_Time`.`ToDo_ID` = `tbl_ToDo`.`ToDo_ID` 
	AND 
	`tbl_ToDo`.`Individual_ID` = `tbl_Individual`.`Individual_ID` 
	AND 
	`tbl_ToDo`.`File_ID` = `tbl_File`.`File_ID` 
	AND 
	`tbl_Time`.`Date` > :start
	AND 
	`tbl_Time`.`Date` < :stop
GROUP BY 
	`tbl_Organization`.`Organization_ID` 
ORDER BY 
	`tbl_Organization`.`Organization_ID`

rherbert · July 4, 2023, 2:44am

Thanks for your reply @cpb.

I copy and pasted your code into LO BASE after selecting Create Query in SQL Mode. I adjusted for the name of my Mariadb database. Other changes that occurred were done so by LO Base after I saved the query and it works!!!

Here is what the code looks like after saving:

SELECT
 `tbl_Organization`.`Organization_ID`,
 MIN( `tbl_File`.`File_ID` ) `FileID`,
 COUNT( `tbl_ToDo`.`ToDo_ID` ) `Count`,
 SUM( CASE WHEN `Stop_Time` > `Start_Time` THEN CAST( ( time_to_sec( `Stop_Time` ) - time_to_sec( `Start_Time` ) ) / 60 AS INTEGER ) ELSE 1440 - CAST( ( time_to_sec( `Start_Time` ) - time_to_sec( `Stop_Time` ) ) / 60 AS INTEGER ) END ) `Minutes`,
 SUM( CASE WHEN `Stop_Time` > `Start_Time` THEN ( time_to_sec( `Stop_Time` ) - time_to_sec( `Start_Time` ) ) / 60 ELSE 1440 - ( time_to_sec( `Start_Time` ) - time_to_sec( `Stop_Time` ) ) / 60 END * `Hourly_Rate` / 60 ) `Invoice`,
 MIN( CONCAT( RIGHT( CONCAT( '000', `tbl_Organization`.`Organization_ID` ), 3 ), RIGHT( CONCAT( '000', `tbl_File`.`File_ID` ), 3 ) ) ) `number`,
 MIN( `tbl_File`.`Project_Title` ) `ProjectTitle`,
 MIN( `tbl_Organization`.`Name` ) `NameOrganization`,
 MIN( `tbl_Organization`.`Address` ) `Address`
 FROM
 `CAID`.`tbl_Time` `tbl_Time`,
 `CAID`.`tbl_Organization` `tbl_Organization`,
 `CAID`.`tbl_ToDo` `tbl_ToDo`,
 `CAID`.`tbl_Individual` `tbl_Individual`,
 `CAID`.`tbl_File` `tbl_File`
 WHERE
 `tbl_Time`.`Organization_ID` = `tbl_Organization`.`Organization_ID`
 AND `tbl_Time`.`ToDo_ID` = `tbl_ToDo`.`ToDo_ID`
 AND `tbl_ToDo`.`Individual_ID` = `tbl_Individual`.`Individual_ID`
 AND `tbl_ToDo`.`File_ID` = `tbl_File`.`File_ID`
 AND `tbl_Time`.`Date` > :start AND `tbl_Time`.`Date` < :stop
 GROUP BY `tbl_Organization`.`Organization_ID`

Thank you. Do you have insight on the other query as well?

Richard

cpb · July 4, 2023, 7:57am

@rherbert,
’
the alterations are identical for both queries, use of CASE and disuse of DATEDIFF.
I don’t use MariaDB but a quick look at the manual was all it took.
both queries are the work of parsely, I simply applied the corrections required by MariaDB.
.
parameter queries must use the Base parser which severely limits your coding options.
I would suggest using a form and filter table as a superior alternative.
.
billing:

SELECT 
	`tbl_Organization`.`Organization_ID`,
	`tbl_File`.`File_ID`, `tbl_Time`.`ToDo_ID`, `tbl_Time`.`Date`, `tbl_Time`.`Start_Time`,
	`tbl_Time`.`Stop_Time`,
	case
		when `Stop_Time` > `Start_Time` then
			cast((time_to_sec(`Stop_Time`) - time_to_sec(`Start_Time`)) / 60 as int)
		else
			1440 - cast((time_to_sec(`Start_Time`) - time_to_sec(`Stop_Time`)) / 60 as int)
	end
	as `Difference`,
	case
		when `Stop_Time` > `Start_Time` then
			(time_to_sec(`Stop_Time`) - time_to_sec(`Start_Time`)) / 60
		else
			1440 - (time_to_sec(`Start_Time`) - time_to_sec(`Stop_Time`)) / 60
	end
	* `Hourly Rate` / 60 
	as `Invoice`,
	RIGHT( CONCAT( '000', `tbl_File`.`File_ID` ), 3 )  as `number`,
	`tbl_File`.`Project_Title`,  
	`tbl_Individual`.`Name`,
	`tbl_Organization`.`Name`,  
	`tbl_Organization`.`Address`
FROM 
	`CAID`.`tbl_Time` `tbl_Time`,
	`CAID`.`tbl_Organization` `tbl_Organization`,
	`CAID`.`tbl_ToDo` `tbl_ToDo`,
	`CAID`.`tbl_Individual` `tbl_Individual`,
	`CAID`.`tbl_File` `tbl_File`
WHERE 
	`tbl_Time`.`Organization_ID` = `tbl_Organization`.`Organization_ID`
	AND `tbl_Time`.`ToDo_ID` = `tbl_ToDo`.`ToDo_ID`
	AND `tbl_ToDo`.`Individual_ID` = `tbl_Individual`.`Individual_ID`
	AND `tbl_ToDo`.`File_ID` = `tbl_File`.`File_ID`
	AND `tbl_Time`.`Date` > :start
	AND `tbl_Time`.`Date` < :stop
ORDER BY 
	`tbl_Organization`.`Organization_ID`

parsely · July 4, 2023, 10:15am

Some remarks

It is intended as a future expansion. If you log dates you know what has been used in the past, and you can add a new record for a new billing period. A macro selects the two first records of the tblLog (sorted on date DESC) and through string manipulation inserts the dates in the query SQL string. It saves you from entering lenghty dates
The advice from Villeroy in another post is to always use full length dates in the ISO format: 2023-07-18. Than you might already be prepared for the input SAGE expects.

SELECT ROWNUM ( ) OVER ( ORDER BY "OrganizationID_ID", "ToDo_ID" ) + 21309 
/* last invoice number in SAGE */, "S".* FROM ( <your query here> ) AS "S"

to assign a unique number to each row, in view of an invoice number SAGE expects.

the invoice number can be concatenated with organizationID and ToDoID and used as a filename: in a folder sorted on filename you can see all invoices sent per organisation per todo

parsely · July 4, 2023, 11:06am

addendum to the Python code given in the first reply: the variable code IMPORTMODE is from an import

from com.sun.star.sheet.DataImportMode import SQL as IMPORTMODE