Inapplicable message? DB2 UDB v8.1.9 Linux

[Bob Stearns]

I am receiving what appears to me to be an inapplicable message from the
query below. The message is also below. I see no VALUES clause nor a set
operator (INTERSECT, DIFFERENCE, UNION) in the query. What am I missing?
SQL0415N The data types of corresponding columns are not compatible in a
fullselect that includes a set operator or in the multiple rows of a
VALUES clause of an INSERT or fullselect. SQLSTATE=42825
SELECT t7.BHID as cow_bhid,
t8.BHID as calf_bhid,
t2.herd_id as Cow_ID,
t7L.namex as Location,
case
when t8.sire_bhid IS NOT NULL
THEN t8.sire_bhid
when t1.BRED_TYPE='A'
then t3s.herd_id
when t1.BRED_TYPE='O'
then t4s.herd_id
when t1.BRED_TYPE='P'
then t5s.herd_id
when t1.BRED_TYPE='I'
then t6s.herd_id
when t1.BRED_TYPE='U'
then 'Unknown'
end as Sire_ID,
case
when t1.BRED_TYPE='I'
then t6d.herd_id
else t8.dam_id
end as Donor_ID,
case
when t1.BRED_TYPE='I'
then t6i.herd_id
else t8.calf_id
end as Embryo_ID,
t6i.bhid as Emb_BHID,
case t1.BRED_TYPE
when 'A'
then 'AI'
when 'O'
then 'Observed'
when 'P'
then 'Pasture'
when 'I'
then 'Embryo'
when 'U'
then 'Unknown'
else 'None'
end as Bred_Via,
char(t1.DATEX, USA) as Preg_Check_Date,
t1.CALLED_DAYS,
t1.BRED_DATE,
char(t1.datex - t1.called_days days, USA) as called_date_bred,
COALESCE(t8.sex,t1.called_sex) as sex,
char(due_date, USA) as due_date,
rowx
FROM IS3.Animals t7
left outer join table ( SELECT t8a.*,
t8b.herd_id as calf_id,
t8c.herd_id as sire_id,
case
when t8a.recip_bhid<>0
then t8d.herd_id
end as donor_id
FROM is3.animals t8a
left outer join is3.animals_priv t8b
on t8b.bhid=t8a.bhid
AND t8b.herd_owner_id=1
join is3.animals_priv t8c
on t8c.bhid=t8a.sire_bhid
AND t8c.herd_owner_id=1
join is3.animals_priv t8d
on t8d.bhid=t8a.dam_bhid
AND t8d.herd_owner_id=1
join is3.weaning t8e
on t8e.bhid=t8a.bhid
WHERE ( t8a.recip_bhid=t7.bhid
OR ( t8a.dam_bhid=t7.bhid
AND t8a.recip_bhid=0))
AND t8a.birth_date>current_date-300 days
AND t8e.weigh_date is null
)
t8
on 0=0
left outer join table ( SELECT t0.*,
CASE t0.bred_type
when 'I'
then t0.BRED_DATE + 275 days
when 'P'
then
case
when called_days is null
then t0.BRED_DATE + 283 days
else t0.datex - called_days days +
283 days
end
else t0.BRED_DATE + 283 days
END
AS due_date,
ROW_NUMBER() OVER( PARTITION BY t0.bhid
ORDER BY
CASE t0.bred_type
when 'I'
then t0.BRED_DATE + 275 days
when 'P'
then
case
when called_days is null
then t0.BRED_DATE + 283 days
else t0.datex - called_days days
+ 283 days
end
else t0.BRED_DATE + 283 days
END DESC NULLS LAST ) AS rowx
FROM is3.PREG_CHK as t0
WHERE t0.bhid=t7.bhid
AND t8.bhid IS NULL
)
t1
on T1.BHID=t7.BHID
join IS3.locations t7L
on T7.location=t7L.loc_id
left outer join IS3.Animals_priv t2
on T2.BHID=T7.BHID
AND t2.herd_owner_id=1
left outer join IS3.service_AI t3
on T1.BHID=T3.cow_BHID
AND t1.BRED_DATE = t3.datex
left outer join IS3.Animals_priv t3S
on T3.Bull_BHID=T3S.BHID
AND t3S.HERD_OWNER_ID = 1
left outer join IS3.service_Obs t4
on T1.BHID=T4.cow_BHID
AND t1.BRED_DATE = t4.datex
left outer join IS3.Animals_priv t4S
on T4.Bull_BHID=T4S.BHID
AND t4S.HERD_OWNER_ID = 1
left outer join IS3.service_Pasture t5
on T1.BHID=T5.cow_BHID
AND t1.BRED_DATE = t5.in_date
left outer join IS3.Animals_priv t5S
on T5.Bull_BHID=T5S.BHID
AND t5S.HERD_OWNER_ID = 1
LEFT OUTER JOIN TABLE( SELECT t61.bhid,
t62.SIRE_BHID,
t62.DAM_BHID,
t62.recip_bhid
FROM is3.et_implants t61
join is3.animals t62
on t61.bhid=t62.bhid
WHERE t62.recip_bhid=t1.bhid
AND t61.datex=t1.bred_date
)
t6e
on t6e.recip_bhid=t1.bhid
left outer join IS3.Animals_priv t6I
on T6e.BHID=T6I.BHID
AND t6I.HERD_OWNER_ID = 1
left outer join IS3.Animals_priv t6S
on T6e.Sire_BHID=T6S.BHID
AND t6S.HERD_OWNER_ID = 1
left outer join IS3.Animals_priv t6D
on T6e.Dam_BHID=T6D.BHID
AND t6D.HERD_OWNER_ID = 1
WHERE t7.bhid in( SELECT bhid
FROM is3.ANIMAL_SETS
WHERE SET_NAME='Junk_Cows'
AND USERID='jhough')
AND (rowx=1
OR rowx IS NULL)
ORDER BY Cow_ID

[Serge Rielau]

Bob Stearns wrote:

I am receiving what appears to me to be an inapplicable message from the
query below. The message is also below. I see no VALUES clause nor a set
operator (INTERSECT, DIFFERENCE, UNION) in the query. What am I missing?
SQL0415N The data types of corresponding columns are not compatible in a
fullselect that includes a set operator or in the multiple rows of a
VALUES clause of an INSERT or fullselect. SQLSTATE=42825
SELECT t7.BHID as cow_bhid,
t8.BHID as calf_bhid,
t2.herd_id as Cow_ID,
t7L.namex as Location,
case
when t8.sire_bhid IS NOT NULL
THEN t8.sire_bhid
when t1.BRED_TYPE='A'
then t3s.herd_id
when t1.BRED_TYPE='O'
then t4s.herd_id
when t1.BRED_TYPE='P'
then t5s.herd_id
when t1.BRED_TYPE='I'
then t6s.herd_id
when t1.BRED_TYPE='U'
then 'Unknown'
end as Sire_ID,
case
when t1.BRED_TYPE='I'
then t6d.herd_id
else t8.dam_id
end as Donor_ID,
case
when t1.BRED_TYPE='I'
then t6i.herd_id
else t8.calf_id
end as Embryo_ID,
t6i.bhid as Emb_BHID,
case t1.BRED_TYPE
when 'A'
then 'AI'
when 'O'
then 'Observed'
when 'P'
then 'Pasture'
when 'I'
then 'Embryo'
when 'U'
then 'Unknown'
else 'None'
end as Bred_Via,
char(t1.DATEX, USA) as Preg_Check_Date,
t1.CALLED_DAYS,
t1.BRED_DATE,
char(t1.datex - t1.called_days days, USA) as called_date_bred,
COALESCE(t8.sex,t1.called_sex) as sex,
char(due_date, USA) as due_date,
rowx
FROM IS3.Animals t7
left outer join table ( SELECT t8a.*,
t8b.herd_id as calf_id,
t8c.herd_id as sire_id,
case
when t8a.recip_bhid<>0
then t8d.herd_id
end as donor_id
FROM is3.animals t8a
left outer join is3.animals_priv t8b
on t8b.bhid=t8a.bhid
AND t8b.herd_owner_id=1
join is3.animals_priv t8c
on t8c.bhid=t8a.sire_bhid
AND t8c.herd_owner_id=1
join is3.animals_priv t8d
on t8d.bhid=t8a.dam_bhid
AND t8d.herd_owner_id=1
join is3.weaning t8e
on t8e.bhid=t8a.bhid
WHERE ( t8a.recip_bhid=t7.bhid
OR ( t8a.dam_bhid=t7.bhid
AND t8a.recip_bhid=0))
AND t8a.birth_date>current_date-300
days
AND t8e.weigh_date is null
)
t8
on 0=0
left outer join table ( SELECT t0.*,
CASE t0.bred_type
when 'I'
then t0.BRED_DATE + 275 days
when 'P'
then
case
when called_days is null
then t0.BRED_DATE + 283 days
else t0.datex - called_days
days +
283 days
end
else t0.BRED_DATE + 283 days
END
AS due_date,
ROW_NUMBER() OVER( PARTITION BY t0.bhid
ORDER BY
CASE t0.bred_type
when 'I'
then t0.BRED_DATE + 275 days
when 'P'
then
case
when called_days is null
then t0.BRED_DATE + 283
days
else t0.datex -
called_days days
+ 283 days
end
else t0.BRED_DATE + 283 days
END DESC NULLS LAST ) AS rowx
FROM is3.PREG_CHK as t0
WHERE t0.bhid=t7.bhid
AND t8.bhid IS NULL
)
t1
on T1.BHID=t7.BHID
join IS3.locations t7L
on T7.location=t7L.loc_id
left outer join IS3.Animals_priv t2
on T2.BHID=T7.BHID
AND t2.herd_owner_id=1
left outer join IS3.service_AI t3
on T1.BHID=T3.cow_BHID
AND t1.BRED_DATE = t3.datex
left outer join IS3.Animals_priv t3S
on T3.Bull_BHID=T3S.BHID
AND t3S.HERD_OWNER_ID = 1
left outer join IS3.service_Obs t4
on T1.BHID=T4.cow_BHID
AND t1.BRED_DATE = t4.datex
left outer join IS3.Animals_priv t4S
on T4.Bull_BHID=T4S.BHID
AND t4S.HERD_OWNER_ID = 1
left outer join IS3.service_Pasture t5
on T1.BHID=T5.cow_BHID
AND t1.BRED_DATE = t5.in_date
left outer join IS3.Animals_priv t5S
on T5.Bull_BHID=T5S.BHID
AND t5S.HERD_OWNER_ID = 1
LEFT OUTER JOIN TABLE( SELECT t61.bhid,
t62.SIRE_BHID,
t62.DAM_BHID,
t62.recip_bhid
FROM is3.et_implants t61
join is3.animals t62
on t61.bhid=t62.bhid
WHERE t62.recip_bhid=t1.bhid
AND t61.datex=t1.bred_date
)
t6e
on t6e.recip_bhid=t1.bhid
left outer join IS3.Animals_priv t6I
on T6e.BHID=T6I.BHID
AND t6I.HERD_OWNER_ID = 1
left outer join IS3.Animals_priv t6S
on T6e.Sire_BHID=T6S.BHID
AND t6S.HERD_OWNER_ID = 1
left outer join IS3.Animals_priv t6D
on T6e.Dam_BHID=T6D.BHID
AND t6D.HERD_OWNER_ID = 1
WHERE t7.bhid in( SELECT bhid
FROM is3.ANIMAL_SETS
WHERE SET_NAME='Junk_Cows'
AND USERID='jhough')
AND (rowx=1
OR rowx IS NULL)
ORDER BY Cow_ID

Divide and conquer.....

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Knut Stolze]

Serge Rielau wrote:

Bob Stearns wrote:

I am receiving what appears to me to be an inapplicable message from the
query below. The message is also below. I see no VALUES clause nor a set
operator (INTERSECT, DIFFERENCE, UNION) in the query. What am I missing?
SQL0415N The data types of corresponding columns are not compatible in a
fullselect that includes a set operator or in the multiple rows of a
VALUES clause of an INSERT or fullselect. SQLSTATE=42825
SELECT t7.BHID as cow_bhid,
t8.BHID as calf_bhid,
t2.herd_id as Cow_ID,
t7L.namex as Location,
case
when t8.sire_bhid IS NOT NULL
THEN t8.sire_bhid
when t1.BRED_TYPE='A'
then t3s.herd_id
when t1.BRED_TYPE='O'
then t4s.herd_id
when t1.BRED_TYPE='P'
then t5s.herd_id
when t1.BRED_TYPE='I'
then t6s.herd_id
when t1.BRED_TYPE='U'
then 'Unknown'
end as Sire_ID,

[...]
Divide and conquer.....

Definitively!

I would guess it could be in one of the CASE expressions.

Oh, and a suggestion to the OP. Expressions like this:

case
when called_days is null
then t0.BRED_DATE + 283 days
else t0.datex - called_days days + 283 days
end

can be rephrased to:

COALESCE(t0.datex - called_days days + 283 days, t0.BRED_DATE + 283 days)

Note that the datetime expression involving "called_days" will evaluate to
NULL if called_days is NULL.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Tonkuma]

I guess some of t8.sire_bhid, t3s.herd_id, etc. in the following
expression(The last row is " then 'Unknown' ") are not CHARACTER type.

case
when t8.sire_bhid IS NOT NULL
THEN t8.sire_bhid
when t1.BRED_TYPE='A'
then t3s.herd_id
when t1.BRED_TYPE='O'
then t4s.herd_id
when t1.BRED_TYPE='P'
then t5s.herd_id
when t1.BRED_TYPE='I'
then t6s.herd_id
when t1.BRED_TYPE='U'
then 'Unknown'
end as Sire_ID

One reason I guessed so, is that you treat sometime xxxx_bhid or
xxxx_id as number.
For example:
......
t8a.recip_bhid<>0
......
t8a.recip_bhid=0
......
t8d.herd_owner_id=1

[Tonkuma]

Or simply change 'Unknown' to number or NULL.

case
when t8.sire_bhid IS NOT NULL
THEN t8.sire_bhid
when t1.BRED_TYPE='A'
then t3s.herd_id
when t1.BRED_TYPE='O'
then t4s.herd_id
when t1.BRED_TYPE='P'
then t5s.herd_id
when t1.BRED_TYPE='I'
then t6s.herd_id
when t1.BRED_TYPE='U'
then NULL
end as Sire_ID

[Tonkuma]

Or change 'Unknown' to number or NULL.

case
when t8.sire_bhid IS NOT NULL
THEN t8.sire_bhid
when t1.BRED_TYPE='A'
then t3s.herd_id
when t1.BRED_TYPE='O'
then t4s.herd_id
when t1.BRED_TYPE='P'
then t5s.herd_id
when t1.BRED_TYPE='I'
then t6s.herd_id
when t1.BRED_TYPE='U'
then NULL
end as Sire_ID

[Bob Stearns]

Knut Stolze wrote:

Serge Rielau wrote:

Bob Stearns wrote:

I am receiving what appears to me to be an inapplicable message from the
query below. The message is also below. I see no VALUES clause nor a set
operator (INTERSECT, DIFFERENCE, UNION) in the query. What am I missing?
SQL0415N The data types of corresponding columns are not compatible in a
fullselect that includes a set operator or in the multiple rows of a
VALUES clause of an INSERT or fullselect. SQLSTATE=42825
SELECT t7.BHID as cow_bhid,
t8.BHID as calf_bhid,
t2.herd_id as Cow_ID,
t7L.namex as Location,
case
when t8.sire_bhid IS NOT NULL
THEN t8.sire_bhid
when t1.BRED_TYPE='A'
then t3s.herd_id
when t1.BRED_TYPE='O'
then t4s.herd_id
when t1.BRED_TYPE='P'
then t5s.herd_id
when t1.BRED_TYPE='I'
then t6s.herd_id
when t1.BRED_TYPE='U'
then 'Unknown'
end as Sire_ID,

[...]

Divide and conquer.....

Definitively!

I would guess it could be in one of the CASE expressions.

Oh, and a suggestion to the OP. Expressions like this:

case
when called_days is null
then t0.BRED_DATE + 283 days
else t0.datex - called_days days + 283 days
end

can be rephrased to:

COALESCE(t0.datex - called_days days + 283 days, t0.BRED_DATE + 283 days)

Note that the datetime expression involving "called_days" will evaluate to
NULL if called_days is NULL.

Thanks for the suggestion. It was a CASE statement with mismatched THEN
results.

My original objection still stands though: the error neither involved
VALUES nor set operations. The message gives no clue where or what the
problem is.

[Serge Rielau]

Bob Stearns wrote:

Knut Stolze wrote:

Serge Rielau wrote:

Bob Stearns wrote:

I am receiving what appears to me to be an inapplicable message from
the
query below. The message is also below. I see no VALUES clause nor a
set
operator (INTERSECT, DIFFERENCE, UNION) in the query. What am I
missing?
SQL0415N The data types of corresponding columns are not compatible
in a
fullselect that includes a set operator or in the multiple rows of a
VALUES clause of an INSERT or fullselect. SQLSTATE=42825
SELECT t7.BHID as cow_bhid,
t8.BHID as calf_bhid,
t2.herd_id as Cow_ID,
t7L.namex as Location,
case
when t8.sire_bhid IS NOT NULL
THEN t8.sire_bhid
when t1.BRED_TYPE='A'
then t3s.herd_id
when t1.BRED_TYPE='O'
then t4s.herd_id
when t1.BRED_TYPE='P'
then t5s.herd_id
when t1.BRED_TYPE='I'
then t6s.herd_id
when t1.BRED_TYPE='U'
then 'Unknown'
end as Sire_ID,

[...]

Divide and conquer.....

Definitively!

I would guess it could be in one of the CASE expressions.

Oh, and a suggestion to the OP. Expressions like this:

case
when called_days is null
then t0.BRED_DATE + 283 days
else t0.datex - called_days days + 283 days
end

can be rephrased to:

COALESCE(t0.datex - called_days days + 283 days, t0.BRED_DATE + 283 days)

Note that the datetime expression involving "called_days" will
evaluate to
NULL if called_days is NULL.

Thanks for the suggestion. It was a CASE statement with mismatched THEN
results.

My original objection still stands though: the error neither involved
VALUES nor set operations. The message gives no clue where or what the
problem is.

You should have gotten this:
db2 => values case when 1=1 then 1 when 1=1 then 'b' end;
SQL0581N The data types of the result-expressions of a CASE expression
are not compatible. SQLSTATE=42804

If you would be so kind to send me a repro by email.
I'll take a peek and see to it that it gets fixed.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Knut Stolze]

Serge Rielau wrote:

You should have gotten this:
db2 => values case when 1=1 then 1 when 1=1 then 'b' end;
SQL0581N The data types of the result-expressions of a CASE expression
are not compatible. SQLSTATE=42804

If you would be so kind to send me a repro by email.
I'll take a peek and see to it that it gets fixed.

Serge, does DB2 do (some) rewrite before it verifies the data types? In
that case I could understand why the error message of the OP was returned.
(Which should not be an excuse for the message not being helpful.)

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Serge Rielau]

Knut Stolze wrote:

Serge Rielau wrote:

You should have gotten this:
db2 => values case when 1=1 then 1 when 1=1 then 'b' end;
SQL0581N The data types of the result-expressions of a CASE expression
are not compatible. SQLSTATE=42804

If you would be so kind to send me a repro by email.
I'll take a peek and see to it that it gets fixed.

Serge, does DB2 do (some) rewrite before it verifies the data types? In
that case I could understand why the error message of the OP was returned.
(Which should not be an excuse for the message not being helpful.)

No, superficially type mismatches in CASE _expression_ should be caught
during parsing, that's why I want a repro.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab