HSQLDB Code

[johnh009]

I’m stuck with the following problem:
I have a table (‘dog_status’) that looks like this:

dog_id person_id status date_from date_to reserved_for prov_month
1 0 adopted 19/12/11
2 1 adopted 31/03/16
3 0 adopted 30/11/07
10 1 adopted 10/03/06 11/10/17
13 1 fostered 06/11/17 0
14 0 adopted 07/11/17
14 1 fostered 06/11/17 07/11/17 0 Nov
16 3 adopted 21/06/15

I’ve constructed a query (reproduced below) so that the ‘id’ columns are decode via their respective parent tables (‘dog’ & ‘person’), so that the out put is more meaningful as it will subsequently to be used as the basis for a form. The ‘reserved_for’ column also links back to the ‘person’ table (‘person_id’).

SELECT RIGHT( 100000 + CAST( “ds”.“dog_id” AS VARCHAR ( 4 ) ), 4 ) || ’ ’ || “d”.“name” AS “Dog”,
RIGHT( 100000 + CAST( “ds”.“person_id” AS VARCHAR ( 4 ) ), 4 ) || ’ ’ || “p”.“surname” || ’ ’ || “p”.“1st_name” AS “Person”,
“ds”.“status” AS “Status”,
“ds”.“date_from” AS “Date From”,
“ds”.“date_to” AS “Date To”,
( SELECT CASE WHEN “ds”.“reserved_for” != “ds”.“person_id”
AND “ds”.“reserved_for” IS NOT NULL
THEN RIGHT( 100000 + CAST( “ds”.“reserved_for” AS VARCHAR ( 4 ) ), 4 ) || ’ ’ || “p1”.“surname” || ’ ’ || “p1”.“1st_name”
ELSE ‘Test’ END
FROM “dog_status” “ds”
JOIN “person” “p1” ON “ds”.“reserved_for” = “p1”.“person_id”
WHERE “ds”.“dog_id” = “d”.“dog_id” ) AS “Reserved For”,
“ds”.“prev_month” AS “Prov Dep Date”
FROM “dog_status” “ds”
JOIN “dog” “d” ON “ds”.“dog_id” = “d”.“dog_id”
JOIN “person” “p” ON “ds”.“person_id” = “p”.“person_id”
WHERE “d”.“name” = :Dog_Name
ORDER BY “ds”.“date_from”

My problem is that where there is an entry in column ‘reserved_for’ and where there exists more that 1 row, as in ‘dog_id’ 14, the decoded person details appear in both rows. I’ve phaffed around with this for several days without any joy. If anyone can give me a clue, it would be very much appreciated.

[johnh009]

Sorry, the formatting of the table (‘dog_status’) contents and the query have all gone to pot. There must a way to maintain the original formatting and therefore readability that has obviously escaped me!

[jimk]

To format code, indent by 4 spaces.

[Ratslinger]

Hello,

The problem is in your SELECT CASE WHEN... portion of the statement. Instead it is best as a LEFT JOIN onto the record. Here is what I have tested with:

SELECT RIGHT (100000 + CAST("ds"."dog_id" AS VARCHAR(4)),4) || ' ' || "d"."name" AS "Dog",
       RIGHT (100000 + CAST("ds"."person_id" AS VARCHAR(4)),4) || ' ' || "p"."surname" || ' ' || "p"."1st_name" AS "Person",
       "ds"."status" AS "Status",
       "ds"."date_from" AS "Date From",
       "ds"."date_to" AS "Date To",
       CASE WHEN "ds"."reserved_for" IS NULL THEN ''
         ELSE RIGHT (100000 + CAST("ds"."reserved_for" AS VARCHAR(4)),4) || ' ' || "p1"."surname" || ' ' || "p1"."1st_name"
         END AS "Reserved For",
       "ds"."prev_month" AS "Prov Dep Date"
FROM "dog_status" "ds"
  JOIN "dog" "d" ON "ds"."dog_id" = "d"."dog_id"
  JOIN "person" "p" ON "ds"."person_id" = "p"."person_id"
  LEFT JOIN "person" "p1" ON "ds"."reserved_for" = "p1"."person_id"
WHERE "d"."name" = :Dog_Name
ORDER BY "ds"."date_from"

Please note I have replaced ‘Test’ with a NULL result which I believe is what you may want. Result is:

If this answers your question please click on the (upper left area of answer).

[johnh009]

Brilliant. Thanks a shed load. Ticked