AW: DB2 SQL confusion

Hi,

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? And handle type merging\promotion?

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)

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von CRPence
Gesendet: Tuesday, 12. May 2009 20:30
An: rpg400-l@xxxxxxxxxxxx
Betreff: Re: DB2 SQL confusion

rob@xxxxxxxxx wrote:

Charles explained sysdummy1 pretty good. Often used in
STRSQL to test certain functions like:
select current_date from sysibm/sysdummy1

Now, it's been deprecated by the VALUES statement.
VALUES CURRENT_DATE
....+....1
VALUES
2009-05-12

You can even do a
VALUES CURRENT_DATE INTO :HostVar

Which may make more sense than a dummy read of a file (like
sysibm/sysdummy1) to get a value. <<SNIP>>

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? And handle type merging\promotion?

For examples:

a>
VALUES ('Row 1')
,VALUES ('Row 2')

b>
VALUES (rand(), current time)

c>
VALUES (rand(), current time)
,VALUES (rand(), 'Just a string')

Regards, Chuck