How to dynamically modify query?

hedrickjc · December 29, 2022, 8:50pm

Windows 11
LibreOffice 7.42.3
HSQLDB Embedded

I have created a database to manage and track devices over five years. I have devices that get replaced every five years and every year. In my table, I have deviceID, Device, yearToReplace, Cost, & Reoccuring (plus other fields, but they are not important to my problem). I have created a query to pull data based on a start date, an end date, and if the Reoccurring field is true. That is working. I then created a Report referencing that Query. I have everything working except that I would like the reoccurring to be included in each year, so the total is correct. That is where I am stuck. Does anyone have an idea?

I am adding screenshot of my report, report design, and query, but as a new user, I am only allowed one media device, so I have to combine them.

RobertG · December 30, 2022, 8:30am

All rows with reocurring = TRUE have to be linked to every year. So there are rows with the same content, only different in “YearToReplace”.

A possible solution with internal HSQLDB:

Create a table “tbl_years” with only one field “year” (SMALLINT).
Create a lot of years in Calc and copy this values to the table - or write the values directly to the table.
Create a table “tbl_filter” (primary key “ID” boolean, “Start_Year” (SMALLINT), “End_Year” (SMALLINT") - this could also be used for the query you created, so no parameter must be added.
Fill first row of “tbl_filter”: Choose ID = TRUE and also the values for “Start_Year” and “End_Year”.
Create a query for “tblDevices”, which only shows the devices where “Reccorring” is TRUE. Don’t use “tblDevices”.* All fields have to be chosen (by double-click on the names), because the field for “YearToReplace” has to be set separately.
Add tables “tbl_years” and “tbl_filter” to the created query. Condition should be WHERE "tbl_years"."Year" BETWEEN "tbl_filter"."Start_Year" AND "tbl_filter"."End_Year"
Add field “tbl_years” to the fields, which should be shown in the query. Set an alias AS "YearToReplace".

Now you should get the reoccurring rows with all years, which have been chosen by “tbl_filter”.
You could use this query together with the existing query, which shouldn’t show the reoccurring rows any more. You could combine both by using UNION. I prefer to create a view of this, because HSQLDB will work faster with such a query than the ReportBuilder.

hedrickjc · December 30, 2022, 4:44pm

RobertG, thank you. I created the tables and queries, and both were working. I run into an error when I combine the queries with union. If I run the query normally, I get the error “The given command is not a SELECT statement.” If I run the query with SQL direct, I get the error "Wrong data type: java.lang.IllegalArgumentException in statement [SELECT “tblDevices”.* FROM “tblDevices”, “tblYearToReplace”, “tbl_filter1” Here is my SQL statement:

SELECT "tblDevices".* FROM "tblDevices", "tblYearToReplace", "tbl_filter1" 

WHERE ("tblDevices"."ToBeSurplused" = FALSE AND "tblDevices"."DateSurplused" IS NULL AND "tblDevices"."YearToReplace" BETWEEN "tbl_filter1"."StartYear" AND "tbl_filter1"."EndYear") ORDER BY "tblDevices"."Department" ASC 

UNION

SELECT "tblDevices"."ID", "tblDevices"."DeviceName", "tblDevices"."User", "tblDevices"."DeviceType", "tblDevices"."Description", "tblDevices"."OperatingSystem", "tblDevices"."Department", "tblDevices"."DatePurchased", "tblDevices"."EstimatedCost", "tblDevices"."SerialNumber", "tblDevices"."ToBeSurplused", "tblDevices"."DateSurplused", "tblDevices"."Reoccuring", "tblYearToReplace"."Years" AS "YearsToReplace" FROM "tblDevices", "tblYearToReplace", "tbl_filter1" 

WHERE ("tblDevices"."Reoccuring" = TRUE AND "tblYearToReplace"."Years" BETWEEN "tbl_filter1"."StartYear" AND "tbl_filter1"."EndYear")

RobertG · December 30, 2022, 5:04pm

You have to set the code to direct SQL.

After creating this query: Create a view of it. Sorting and grouping won’t run well in ReportBuilder if a query runs only in direct SQL.

Try this code, will have the fields in the same order:

SELECT "tblDevices"."ID", "tblDevices"."DeviceName", "tblDevices"."User", "tblDevices"."DeviceType", "tblDevices"."Description", "tblDevices"."OperatingSystem", "tblDevices"."Department", "tblDevices"."DatePurchased", "tblDevices"."EstimatedCost", "tblDevices"."SerialNumber", "tblDevices"."ToBeSurplused", "tblDevices"."DateSurplused", "tblDevices"."Reoccuring", "tblDevices"."YearsToReplace" 
FROM "tblDevices","tbl_filter1" 

WHERE ("tblDevices"."ToBeSurplused" = FALSE AND "tblDevices"."DateSurplused" IS NULL AND "tblDevices"."YearToReplace" BETWEEN "tbl_filter1"."StartYear" AND "tbl_filter1"."EndYear") ORDER BY "tblDevices"."Department" ASC 

UNION

SELECT "tblDevices"."ID", "tblDevices"."DeviceName", "tblDevices"."User", "tblDevices"."DeviceType", "tblDevices"."Description", "tblDevices"."OperatingSystem", "tblDevices"."Department", "tblDevices"."DatePurchased", "tblDevices"."EstimatedCost", "tblDevices"."SerialNumber", "tblDevices"."ToBeSurplused", "tblDevices"."DateSurplused", "tblDevices"."Reoccuring", "tblYearToReplace"."Years" AS "YearsToReplace" 
FROM "tblDevices", "tblYearToReplace", "tbl_filter1" 

WHERE ("tblDevices"."Reoccuring" = TRUE AND "tblYearToReplace"."Years" BETWEEN "tbl_filter1"."StartYear" AND "tbl_filter1"."EndYear")

hedrickjc · December 30, 2022, 5:31pm

RobertG, thank you so much. That solve my issue.