Yeah, now I have to go find any of MY old code that may still be using sysdummy1!
-Tom Stieger
IT Manager
California Fine Wire
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Monday, August 26, 2013 11:25 AM
To: Midrange Systems Technical Discussion
Subject: RE: SQL views missing after Cumulative PTF's applied
Beautiful! I was thinking it was harder than that.
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com
From: Tom E Stieger <testieger@xxxxxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>,
Date: 08/26/2013 02:02 PM
Subject: RE: SQL views missing after Cumulative PTF's applied
Sent by: midrange-l-bounces@xxxxxxxxxxxx
This should work:
CREATE VIEW mylib.companyv1 (compid, compdesc)
AS (
Values(0,'Unknown/unmaintained')
UNION ALL
SELECT cocoid AS compid, codesc AS compdesc FROM mylib.company ) ;
-Tom Stieger
IT Manager
California Fine Wire
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [
mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Robert Clay
Sent: Monday, August 26, 2013 10:43 AM
To: midrange-l@xxxxxxxxxxxx; midrange-l@xxxxxxxxxxxx
Subject: Re: SQL views missing after Cumulative PTF's applied
V6.1.0 LPAR
And...viola! Give that man a prize.
Object . . . . . . . : SYSDUMMY1 Attribute . . . . . : PF
Library . . . . . : SYSIBM Owner . . . . . . . : QSYS
Library ASP device . : *SYSBAS Library ASP group . : *SYSBAS
Type . . . . . . . . : *FILE Primary group . . . : *NONE
Creation information:
Creation date/time . . . . . . . . . : 08/26/13 00:01:01
Created by user . . . . . . . . . . : QSYS
System created on . . . . . . . . . : S101F63M
Object domain . . . . . . . . . . . : *SYSTEM
So, syntactically, how would I make the change to using the VALUES clause
with a UNION ALL?
CREATE VIEW mylib.companyv1 AS (
SELECT 0 AS compid, 'Unknown/unmaintained' AS compdesc FROM
sysibm.sysdummy1
UNION ALL
SELECT cocoid AS compid, codesc AS compdesc FROM mylib.company ) ;
Everything that I've tried comes up with syntax errors.
Thanks for the help!
Robert
"Contrariwise, if it was so, it might be; and if it were so, it would be;
but as it isn't, it ain't. That's logic."--Tweedledee
On 08/26/2013 1:14 PM, rob@xxxxxxxxx wrote:
Ok, I know we were told "don't ask" so I won't. Instead, I'll assume.
Much of the use for SYSDUMMY1 has vanished. I would suspect it's only
there for compatibility reasons. There are two different statements
which should be able to replace most, if not all, of the use of
SYSDUMMY1. They are SET and VALUES.
For example, I can replace:
select days('2013-09-30') - days(current date) from sysibm.sysdummy1
with values days('2013-09-30') - days(current date) Either will tell
me the number of days until V5R4 expires - 55. I will turn 52 when it
is 52 days until V5R4 expires. (Guess that means I'll finally be
playing with a full deck.)
Perhaps, based on Charles statement, if you can replace your usage of
sysdummy1 your issues may go away.
Guess when I put my last cume on?
Object . . . . . . . : SYSDUMMY1
Library . . . . . : SYSIBM
Library ASP device . : *SYSBAS
Type . . . . . . . . : *FILE
Creation information:
Creation date/time . . . . . . . . . : 06/08/13 08:00:20
Created by user . . . . . . . . . . : QSYS
Rob Berendt
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
