Re: DB2 SQL confusion

[CRPence <CRPbottle@xxxxxxxxx>]

  Bummer.  If it were parsed as an effective UNION, then the 
promotion of data types would work.  If as items in an INSERT the 
SQL properly maps.  Ah well.

  The VALUES is really just a [one row] TABLE function in that 
context, so I guess implicitly running it as a SELECT makes sense. 
They are nice for CTE with some data that can be hard-coded.  That 
is why I asked about the syntax.  Just a sample I happen to have 
handy which makes their use in multiples [dimensioned]:

   with
     dRange (Ten, pwrTen, low, hi) as
      ( values(10, 1, 10*10**01, 10*10**02-1)
       ,values(10, 1, 10*10**02, 10*10**05-1)
       ,values(10, 1, 10*10**05, 10*10**08-1)
       ,values(10, 1, 10*10**08, 10*10**11-1)
      )
    select * from dRange

  In that case I get a temporary table from literals local to my 
program, persisting only for that statement.  That is a case of four 
columns and four rows.  I guess I do not recall specifically having 
to CAST, but maybe I have never mixed types, only used the same type 
which required promotion [to larger; e.g. decimal scale] within the 
type as in the above CTE.  Perhaps you want to try a CTE with the 
varied data types [and feedback? :-) ]

  Sad really, how interesting conversations about SQL have so often 
occurred on the wrong list for so many years.  Many people that will 
never use RPG miss all the fun.  Too bad so few strive to keep on 
topic.  I had given up starting my replies with 'Not sure what this 
has to do with RPG'.

Regards, Chuck

rob@xxxxxxxxx wrote:
> It's a little hairy on the data type, but yes it can
> generate a multiline report.
>
> VALUES 'A','B'
>   A 
>   B 
>
> VALUES 'A',4
> ++++++++++++++
>             4 
>
> VALUES '3',4
>             3
>             4
>
> But reading your email showed me that if I put parenthesis around the 
> columns I can get a multicolumn report:
>
> VALUES ('A',4)
>   A                 4
>
> You can mix multicolumn and multirow, but you do have to respect data 
> types in the multirow
> VALUES (rand(), current time)
>   , (rand(), 'Just a string')
> 1.8839075899533067E-001  15:14:09
> 1.9336527603991821E-001  ++++++++