Re: Big reason to avoid SYSDUMMY1

"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