HSQL code logic

[johnh009]

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:

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

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:

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.

[Ratslinger]

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?

[librebel]

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"

[johnh009]

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

[Ratslinger]

Hello,

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"
WHERE (CURRENT_DATE > "Overdue")
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.

[Ratslinger]

Note I forgot the last line:

ORDER BY "Dog", "Past Due"

Just place at the end of the statement.

[johnh009]

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.