Ask Your Question

HSQL code logic

asked 2017-12-15 19:50:32 +0200

johnh009 gravatar image

updated 2017-12-15 19:52:15 +0200

Can someone, with more knowledge of HSQL then I, please give me a clue with the following:

I'm trying to produce a query that displays overdue treatment (mainly vaccinations) for the named dogs, from the table below:

image description

The following query attempts to find the animals that are overdue, but fetches record that are not over due.

image description

With the ' AND CURRENT_DATE > "dt"."due_date" ' line active (not commented out), while the records with a 'due_date' of later than today are not retrieved (good), an older record with the next latest date are retrieved, like so:

image description

What I want are the latest records where the 'due_date' is less than today. I don't seem to be able to get my pea-like round the logic.

edit retag flag offensive close merge delete


Hello, Quick question (maybe more later). You're table sample shows a mane of dt_gash but your SQL shows dt (alias of dog_treatment). Is this a problem? Using correct table? Which name is correct?

Ratslinger gravatar imageRatslinger ( 2017-12-15 23:05:23 +0200 )edit

Try a nested SELECT, e.g.

SELECT "Dog", MAX("Next Due") AS "Overdue" FROM ( SELECT CURRENT_DATE AS "Today's Date", "dt"."due_date" AS "Next Due", "d"."name" AS "Dog"  FROM "dt" JOIN "d" ON "dt"."dog_id" = "d"."dog_id" WHERE CURRENT_DATE > "Next Due" ) GROUP BY "Dog"
librebel gravatar imagelibrebel ( 2017-12-15 23:34:52 +0200 )edit

Yes, sorry, my oversight. 'dt_gash' is the query name used to extract the relevant columns of the table 'dog_treatment'.

johnh009 gravatar imagejohnh009 ( 2017-12-16 17:33:28 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2017-12-16 03:59:41 +0200

Ratslinger gravatar image

updated 2017-12-16 04:10:08 +0200


First here is the SQL statement which worked on my end:

SELECT CURRENT_DATE AS "Today's Date", TO_CHAR("Overdue",'MM/DD/YY') AS "Past Due",
       "treatment_type" || '   ' || "treatment_subtype" AS "Treatment",
       RIGHT (100000 + CAST("dog_id" AS VARCHAR(4)),4) || '   ' || "name" AS "Dog"
FROM "dog" "d"
  JOIN (SELECT "dog_id", "treatment_type", "treatment_subtype",
               MAX("due_date") "Overdue"
        FROM "dt"
        GROUP BY "dog_id", "treatment_type",
                 "treatment_subtype") A ON "d"."dog_id" = "A"."dog_id"
AND   "name" IN ('Bali','Dana','Fred','Pep','Pedro','Stumpy')

The only item which may error is the table name dt. I used this as there is confusion on the treatment table name.

Now the what & why. So the first thing actually done is to get valid 'Treatment' records. This was a main concern until I realized your thinking was askew. What you want here is the record with the highest date (the MAX select) and not only grouped by 'dog_id' but also by 'treatment_type' and 'treatment_subtype'. Now once this base is established, join it to the 'dog' table and make the selections (WHERE conditions) from there.

The other item of note is the date. The MAX portion leaves it as an absolute date & the TO_CHAR puts it in a viewable format.

If this answers your question please tick the ✔ (upper left area of answer). It helps others to know there was an accepted answer.

edit flag offensive delete link more


Note I forgot the last line:

ORDER BY "Dog", "Past Due"

Just place at the end of the statement.

Ratslinger gravatar imageRatslinger ( 2017-12-16 05:24:30 +0200 )edit

Yes, that works exactly as required. Thank you.

I had toyed with a sub-select, but within the 'WHERE' clause. I would never have thought of creating one within the 'JOIN' clause. In all my 'googling' of the problem, I did not come across any suggestions along those lines.

Thanks again.

johnh009 gravatar imagejohnh009 ( 2017-12-16 19:25:37 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-12-15 19:50:32 +0200

Seen: 53 times

Last updated: Dec 16 '17