Re: AW: DB2 SQL confusion

Birgitta Hauser wrote:

CRPence wrote:

A VALUES statement typed in a STRSQL statement input line, presents a report.? Wow. I did not know that. Does it
work to generate a multiple row report for multiple VALUES
separated by commas and a multiple column report with
comma separated values enclosed in parenthesis? <<SNIP>>

You mean something like this?
Values('ABC', 123), ('XXX', 456)

It works under release 6.1. The result is:
VALUES VALUES
ABC 123
XXX 456
With Release 6.1 VALUES can also be used to create something like
"temporary" tables filled with values.
For example:
With
x as (Select * From
(Values (100, Date('2009-01-31')),
(100, Date('2009-02-28')),
(101, Date('2009-03-31')),
(100, Date('2009-04-30')))
as Tab1 (MyNum, MyDate))
,Y as (Select * From
(Values (100, 'TEXT1'),
(101, 'TEXT2')) as Tab2 (MyNum, MyText))
Select MyNum, MyText, MyDate
From x join y Using(MyNum)

So the SELECT should be unnecessary in the CTE, just as it is on the [command line] statement input? Naming two tables for each CTE, at the least, seems redundant; TAB1 & TAB2 serve no obvious purpose, except syntactically possibly to give column names. So the question is, can the above SELECT with two CTEs be rewritten as?:

With
x (MyNum, MyDate) as
(Values (100, Date('2009-01-31')),
(100, Date('2009-02-28')),
(101, Date('2009-03-31')),
(100, Date('2009-04-30')))
,Y (MyNum, MyText) as
(Values (100, 'TEXT1'),
(101, 'TEXT2'))
Select MyNum, MyText, MyDate
From x join y Using(MyNum)

If so, is there anything valuable lost or gained by doing one versus another?

Regards, Chuck