Ask Your Question
1

HSQLDB Code [closed]

asked 2017-11-22 13:06:08 +0100

johnh009 gravatar image

updated 2017-11-22 16:03:13 +0100

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.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-10-21 13:05:06.478088

Comments

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!

johnh009 gravatar imagejohnh009 ( 2017-11-22 13:12:22 +0100 )edit

To format code, indent by 4 spaces.

Jim K gravatar imageJim K ( 2017-11-22 20:51:41 +0100 )edit

1 Answer

Sort by » oldest newest most voted
1

answered 2017-11-22 19:19:13 +0100

Ratslinger gravatar image

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:

image description

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

edit flag offensive delete link more

Comments

Brilliant. Thanks a shed load. Ticked :-)

johnh009 gravatar imagejohnh009 ( 2017-11-22 19:55:44 +0100 )edit

Question Tools

1 follower

Stats

Asked: 2017-11-22 13:06:08 +0100

Seen: 60 times

Last updated: Nov 22 '17