Re: DB2 SQL confusion

Smacking head while typing.... <ugh.skip> That is the problem for not having an SQL DB2 in front of me; I was writing into NotePad, so no syntax checking :-) Ah yes. The VALUES is an effective TABLE function; end of story. I should *not* have added /for one row/ nor a new VALUES for each additional row as I had incorrectly written. I should have known immediately when I added [for one row] to my text, because as I was doing so, my mind was screaming for verification before posting, but I was too lazy ;-) I had added that text to reflect my improper coding, since it did not make sense without some adjustment; the proper adjustment was of course to remove extraneous "values". :-( </ugh.skip>

It is the new list element [optionally in parentheses, for single values] which is separated by a comma, that defines a new row within the one VALUES() statement. I still did not look it up 8^Q but I stand by that one :-)

My memory of what promotes is wrong, or what I have been able to accomplish [without an explicit CAST()] in a UNION are wrong. The numeric & string case does not surprise me since if promotion was to numeric then the sting would have to be valid digits, sign, and decimal separator. Same for DATE and the string having to be a valid date. Hmmm... no matter, I have not even seen a system in ages.

Regards, Chuck

rob@xxxxxxxxx wrote:

values(10, 1, 10*10**01, 10*10**02-1) ,(10, 1, 10*10**02, 10*10**05-1)
,(10, 1, 10*10**05, 10*10**08-1)
,(10, 1, 10*10**08, 10*10**11-1)

<<SNIP failing example>>

UNION's are not happy with different data types.

select 'ALPHA', 'BRAVO' FROM SYSIBM/SYSDUMMY1 UNION ALL SELECT 49.5,CURRENT DATE FROM SYSIBM/SYSDUMMY1

Constant value Constant value
+++++ ++++++++++ 49.5 2009-05-12