There are different ways how to use VALUES and the way how Nils used it is
one of them.
Also the VALUES Statement can be used interactively in Run SQL Script and
STRSQL just to perform a SQL statement without accessing a table or view.
For embedded SQL there is also a VALUES ... INTO statement which can be used
as alternate for SELECT ... INTO.
How ever contrary to SELECT ... INTO a VALUES ... INTO statement can be
prepared and executed dynamically.
When using a SELECT ... FROM SYSDUMMY Statement for executing a function or
displaying a special register, the query optimizer is activated and a FULL
OPTIMIZATION is performed.
When using a VALUES statement, the function or special register is directly
called.
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!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Rob
Berendt
Sent: Montag, 12. Juli 2021 13:33
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: Big reason to avoid SYSDUMMY1
Neils,
I think you are confusing the VALUES statement with the VALUES clause.
https://www.ibm.com/docs/en/i/7.4?topic=statements-values
Then again, maybe I'm unable to see the tree in that forest of a sql
statement example.
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail
to: 7310 Innovation Blvd, Suite 104
Ft. Wayne, IN 46818
Ship to: 7310 Innovation Blvd, Dock 9C
Ft. Wayne, IN 46818
http://www.dekko.com
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Niels
Liisberg
Sent: Monday, July 12, 2021 5:15 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Big reason to avoid SYSDUMMY1
CAUTION: This email originated from outside of the organization. Do not
click links or open attachments unless you recognize the sender and know the
content is safe.
"Values" are great !!
I use it to pivot turn column into row and construct a virtual table on the
fly. They can be used with lateral joins and in CTE:
Here an example where I do a lateral join where i pivot turns a repeating
set into rows:
with rater (vkkunr , maaned , rate , forfald) as (
select vkkunr, b.*
from vkmstp
*join lateral ( values (01, vkrt1, vkfd1) , (02, vkrt2, vkfd2), (03,
vkrt3, vkfd3) , (04, vkrt4, vkfd4), (05, vkrt5, vkfd5) , (06,
vkrt6, vkfd6), (07, vkrt7, vkfd7) , (08, vkrt8, vkfd8), (09,
vkrt9, vkfd9) , (10, vkrt10, vkfd10), (11, vkrt11, vkfd11) , (12, vkrt12,
vkfd12) ) b on 1=1*
)
Select vkkunr, rate, maaned,
case
when maaned < month(current date)
then date( current date - dayofyear(current date) days + 1 year +
maaned month + forfald days)
else date( current date - dayofyear(current date) days + 0 year +
maaned month + forfald days)
end forfdat
from rater
And a CTE where my ranking is a virtual table
-- List prefered column translation
with myrank ( points , lang , sys) as ( values
(100, 2 , 1406),
(90 , 2 , 1402),
(80 , 2 , 1401),
(70 , 2 , 1400),
(60 , 0 , 1406),
(50 , 0 , 1402),
(40 , 0 , 1401),
(30 , 0 , 1400)
)
Select column_name, column_heading , snake_case (text) suggested_name ,
b.*
from syscolumns a
left join lateral (
Select points , b.*
from text b
left join myrank
on language = lang
and system = sys
where textname = column_name
order by ifnull(points , 0) desc
limit 1
) b on 1=1
On Sun, Jul 11, 2021 at 8:20 PM Rob Berendt <rob@xxxxxxxxx> wrote:
I don't understand how it's a lousy implementation.
VALUES knows there is not inherently any database operation. Therefore
there are no opens, closes, fetches, etc. While "select... from
sysibm.sysdummy1" is a file just like every other file on the system.
Just
a common dummy they came up with.
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 7310 Innovation Blvd, Suite 104
Ft. Wayne, IN 46818
Ship to: 7310 Innovation Blvd, Dock 9C
Ft. Wayne, IN 46818
http://www.dekko.com
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of D*B
Sent: Saturday, July 10, 2021 5:57 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Big reason to avoid SYSDUMMY1
CAUTION: This email originated from outside of the organization. Do not
click links or open attachments unless you recognize the sender and know
the content is safe.
<rob>
Just read this blog on why you should use the VALUES statement instead of
SYSDUMMY1.
Basically this
VALUES CURRENT TIMESTAMP INTO :hv1
Will blow the doors off of
SELECT CURRENT TIMESTAMP INTO :hv1 FROM sysibm.sysdummy1
</rob>
... seems to be a lousy implementation!
D*B
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
