Ask Your Question
1

HSQLDB Code

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

johnh009 gravatar image

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

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 close merge delete

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 +0200 )edit

To format code, indent by 4 spaces.

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

1 Answer

Sort by » oldest newest most voted
1

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

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 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 43 times

Last updated: Nov 22 '17