[Solved] Recurring query problem

Creating tables and queries
Post Reply
gkick
Posts: 324
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

[Solved] Recurring query problem

Post by gkick »

How to use a calculated value in a secondary calculation, e,g

client, invoice date,term, duedate, overdue
the duedate is calculated via dateadd of invoice date and term
the overdue would be the calculation of currentdate-duedate, however queries do not like to use calculated values in a second calculation.

What would be a practical work around?
Thks
Last edited by gkick on Fri Jan 17, 2020 2:14 am, edited 1 time in total.
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: Recurring query problem

Post by chrisb »

based upon the info provided this is not difficult.
you have not declared the unit size of "Term" so i have opted for months.
 Edit: 16 Jan 2020 17:13 added 'Datediff()' to calculate time (days) overdue. 

Code: Select all

select "Client", "InvoiceDate", "Term" "Term(months)",
"InvoiceDate" + "Term" month "DueDate",
case
	when "InvoiceDate" + "Term" month < current_date then true
	else false
end "Overdue"
,
case
	when "InvoiceDate" + "Term" month < current_date then
		datediff('DD', "InvoiceDate" + "Term" month, current_date)
end "DaysLate"
from "tMyTablName"
--to show only those clients who are overdue enable where clause
--where "InvoiceDate" + "Term" month < current_date
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
gkick
Posts: 324
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: Recurring query problem

Post by gkick »

Hi Chris, thank you and sorry I need to clarify:

invoicedate is a datefield

term is type integer (number of days, 14, 30 or 90 etc)

duedate is calculated like DATEADD( 'day', "term", "adate" ) produces a 5 digit integer

next I want to calculate the number of days between CURDATE() and the duedate using datediff - which tells me the field duedate is unknown

Its more of a general thing, never managed to have a calculation based on another calculated field in the same query before

Thanks
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: Recurring query problem

Post by chrisb »

i think that you did not notice the edit in my first post.
the code below is the same as above with "Term" month replaced by "term" day & table field names replaced by your field names.

Code: Select all

select "client", "invoice date", "term" "term(days)",
"invoice date" + "term" day "DueDate",
case
   when "invoice date" + "term" day < current_date then true
   else false
end "Overdue",
case
   when "invoice date" + "term" day < current_date then
      datediff('DD', "invoice date" + "term" day, current_date)
end "DaysLate"
from "tMyTableName"
--to show only those clients who are overdue enable the where clause below
--where "invoice date" + "term" day < current_date
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
gkick
Posts: 324
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: Recurring query problem

Post by gkick »

well, still end up with a syntax error message, possibly because of the "term" "term(days)" bit which I do not understand,
"term" is an existing field, "term(days)" I guess is an alias ?
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: Recurring query problem

Post by chrisb »

yes "term(days)" is an alias.
did you activate the 'SQL' icon on the toolbar.
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
gkick
Posts: 324
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: Recurring query problem

Post by gkick »

yes running in sql direct mode, ahh maybe its because after the select its "term" day without brackets, will try and come back
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: Recurring query problem

Post by chrisb »

i just tested the code i posted with hsqldb 2.5.0 there are no errors.
the code "invoice date" + "term" day "DueDate" could be penned as DATEADD('dd', "term", "invoice date") AS "DueDate"

below is the table i created for the test. it contains 3 fields as indicated in your initial post plus a primary key.
1) paste the create block into menu:tools>SQL & hit execute.
2) repeat 1 with the insert block.
3) close window
4) hit table icon in base main window.
5) menu:View>Refresh Tables.
6) copy & paste the the code from my second post into the query window.
7) replace from "tMyTableName" with from "tOverdue".
8) activate SQL icon.
9) execute query.
if this works & it will then you will need to post the code you used which generated the syntax error.

Code: Select all

create table "tOverdue"(
id int generated by default as identity (start with 1) primary key,
"client" varchar(5) not null,
"invoice date" date not null,
"term" smallint not null
);

insert into "tOverdue" values
(default,'Jack','2019-11-19',14),
(default,'Jill','2019-11-05',30),
(default,'Jim','2019-11-01',90),
(default,'Jen','2019-11-03',90);

open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
gkick
Posts: 324
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: Recurring query problem

Post by gkick »

My humble apologies, was clicking the wrooong button, I owe you a pint or 2 of guiness, I guess that works with other datatypes as well. Thats great
Now I need to get this

Code: Select all

SELECT "order_id","orderdate", "terms" "terms(days)",
"orderdate" + "terms" day "DueDate",
case
   when "orderdate" + "terms" day < current_date then true
   else false
end "Overdue",
case
   when "orderdate" + "terms" day < current_date then
      datediff('DD', "orderdate" + "terms" day, current_date)
end "DaysLate" FROM "orders"
into that

Code: Select all

SELECT DISTINCT "orders"."order_id", "customer"."customer_name", "qrySubTotalBT"."SubTotal:", "qryTotalDiscountByOrderID"."Discount", "SubTotal:" + "Discount" "Total", "orders"."orderdate", "terms"."terms", DATEDIFF( 'dd', "orderdate", CURDATE( ) ), "orders"."datepaid", "orders"."received", "SubTotal:" + "Discount" - "received" "balance" FROM { oj "orders" "orders" LEFT OUTER JOIN "customer" "customer" ON "orders"."customer_id" = "customer"."customer_id" }, "qryTotalDiscountByOrderID" "qryTotalDiscountByOrderID", "qrySubTotalBT" "qrySubTotalBT", "terms" "terms" WHERE "qryTotalDiscountByOrderID"."order_id" = "qrySubTotalBT"."order_id" AND "qrySubTotalBT"."order_id" = "orders"."order_id" AND "orders"."terms" = "terms"."terms_id"
I opened a can of worms here
Anyhow thanks a lot and thank you for your patience
cherio
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: [Solved] Recurring query problem

Post by chrisb »

we seem to have drifted away from the gist of the topic.
the points to remember are:
1) sql forbids the use of variables.
2) in relation to this topic we imitate a variable by repeating the formula(calculation) on which that variable would be based.

i have inserted the case when structures into your code.
in order to allow use of the parser i have replaced "orderdate" + "terms" day with dateadd('dd', "terms", "orderdate").
i hope it works but obviously am unable test it.

Code: Select all

SELECT DISTINCT 
"orders"."order_id", "customer"."customer_name", "qrySubTotalBT"."SubTotal:", 
"qryTotalDiscountByOrderID"."Discount", 
"SubTotal:" + "Discount" "Total", 
"orders"."orderdate", "terms"."terms", 
DATEDIFF( 'dd', "orderdate", CURDATE( ) ), 
"orders"."datepaid", 
"orders"."received", 
"SubTotal:" + "Discount" - "received" "balance",

dateadd('dd', "terms"."terms", "orders"."orderdate") "DueDate",
case
   when dateadd('dd', "terms"."terms", "orders"."orderdate") < current_date then true
   else false
end "Overdue",
case
   when dateadd('dd', "terms"."terms", "orders"."orderdate") < current_date then
      datediff('dd', dateadd('dd', "terms"."terms", "orders"."orderdate"), current_date)
end "DaysLate"

from "orders"
left join "customer"
ON "orders"."customer_id" = "customer"."customer_id"

join "qryTotalDiscountByOrderID"
on "qryTotalDiscountByOrderID"."order_id" = "orders"."order_id"

join "qrySubTotalBT"
on "qrySubTotalBT"."order_id" = "orders"."order_id"

join "terms"
on "orders"."terms" = "terms"."terms_id"
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
Post Reply