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

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

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

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

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

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

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.

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

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

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