Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

HSQLDB Code

I'm stuck with the following problem: I have a table 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.

HSQLDB Code

I'm stuck with the following problem: I have a table 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.

HSQLDB Code

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.