Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version


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).