Re: DB2 SQL confusion

[CRPence <CRPbottle@xxxxxxxxx>]

  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