|
midrange.com
Thank you. Now I'll have to see if business rules allow me to only accept
half of the union statement. Wait! I bet I can use the CASE statement to
CAST to the correct value. I'll take a look at that.
Rob Berendt
--
"They that can give up essential liberty to obtain a little temporary
safety deserve neither liberty nor safety."
Benjamin Franklin
"R. Bruce Hoffman,
Jr." To: <midrange-l@midrange.com>
<rbruceh@attglobal. cc:
net> Fax to:
Sent by: Subject: Re: Is UNION
supported on a VIEW in V5R1?
midrange-l-admin@mi
drange.com
08/13/2002 03:15 PM
Please respond to
midrange-l
nope, V5R2
===========================================================
R. Bruce Hoffman, Jr.
-- IBM Certified Specialist - iSeries Administrator
-- IBM Certified Specialist - RPG IV Developer
"There is a crack in everything,
that's how the light gets in.
- Leonard Cohen
----- Original Message -----
From: <rob@dekko.com>
To: <midrange-l@midrange.com>
Sent: Tuesday, August 13, 2002 4:04 PM
Subject: Is UNION supported on a VIEW in V5R1?
> This is a multipart message in MIME format.
> --
> [ Picked text/plain from multipart/alternative ]
> I am running a RUNSQLSTM and seem to be having a problem.
>
> 5722SS1 V5R1M0 010525 Run SQL Statements IIML88
> 08/13/02 14:57:19 Page 1
> Source file...............QTEMP/QSQLSESS
> Member....................IIML88
> Commit....................*NONE
> Naming....................*SYS
> Generation level..........10
> Date format...............*JOB
> Date separator............*JOB
> Time format...............*HMS
> Time separator ...........*JOB
> Default collection........*NONE
> IBM SQL flagging..........*NOFLAG
> ANS flagging..............*NONE
> Decimal point.............*JOB
> Sort sequence.............*JOB
> Language ID...............*JOB
> Printer file..............*LIBL/QSYSPRT
> Source file CCSID.........37
> Job CCSID.................65535
> Statement processing......*RUN
> Allow copy of data........*OPTIMIZE
> Allow blocking............*ALLREAD
> Source member changed on 08/13/02 14:37:45
> 5722SS1 V5R1M0 010525 Run SQL Statements IIML88
> 08/13/02 14:57:19 Page 2
> Record *...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6
> ...+... 7 ...+... 8 SEQNBR Last change
> 1 CREATE VIEW IIML88
> 2 AS (
> 3 SELECT IID, IPROD, IDESC, IADJ, IRCT, IISS, IOPB, IYISS, IYTDU,
> 4 IYSLS, IMSLS, ICLAS, IUMS, IUMP, IUMCN, IITYP, ILEAD,
> IVEND,
> 5 IVEN2, ICUSA, IPRDA, IMIN, ILDTE, ILIST, ISCST, IACST,
> IFCST,
> 6 ILEVL, IONOD, IDISC, INSEQ, ITSEQ, ICYC, IDRAW, IMTDU,
> IMRP,
> 7 IACT, IORDP, IYRCT, ILOTS, IYADJ, IMCOS, IYCOS, IMUST,
> IORDC,
> 8 IAVEU, IDSCE, IWGHT, IABBT, IABC, IPFDV, IPACK, ILABL,
> IDRF,
> 9 ICLNG, ICWID, IPITM, IBUYC, IFENO, IUMR, IUMRC, IFII,
> TAXC1,
> 10 SAFLG, IIOQ, IBTCH, IYUSE, ICYCF, IPERS, ISTYL, IPODY,
> ITFDY,
> 11 IFRFG, ISACT, IUMAT, IVULI, IVULP, INCCFG, IWHS, ISOFL,
> ILCC,
> 12 IJIT, IUNCN, IMAXR, IMINR, IPCTK, IGTEC, IMAXP, IMTDP,
> IYTDP,
> 13 ITGQY, IARRT, IMXIN, IDMTF, IDMC1, IDMC2, IMBHD, IDLTR,
> 14 IMQDY, IMBWT, IMPOT, IMUSC, IMUMB, IMUMA, IPURC, IMSUM,
> 15 IBUSY, IDSCR, IMPSD, ILOC, IMCOM, IGLNO, IFCI, IMDCRT,
> ICOND,
> 16 IREF01, IREF02, IREF03, IREF04, IREF05,
> 17 CAST(iref05 as dec(7,2)) as length,
> 18 IORIGN, IMCOMC, IMFRMC, IMREVL, IMFORM, IMCCTL, IMBNDP,
> 19 IMBNDC, IMMNDT, IMBWIP, IMPMTH, IMCMTH, IMQLED, IMALOT,
> 20 IMNOT1, IMNOT2, IMMFGR, IMVFRY, IMSPEC, IMTLVL, IMQLE2,
> 21 IMSSPC, IMASTB, IMCNTR, IMPKD1, IMPKD2, IMHZRD, IMCSTC,
> 22 IMCORP, IMQCTL, IMCQTY, IDSALW
> 23 FROM IIM
> 24 WHERE iref05<>' '
> 25 AND SUBSTR(IREF05,1,1) NOT BETWEEN 'A' AND 'Z'
> 26 UNION
> 27 SELECT IID, IPROD, IDESC, IADJ, IRCT, IISS, IOPB, IYISS, IYTDU,
> 28 IYSLS, IMSLS, ICLAS, IUMS, IUMP, IUMCN, IITYP, ILEAD,
> IVEND,
> 29 IVEN2, ICUSA, IPRDA, IMIN, ILDTE, ILIST, ISCST, IACST,
> IFCST,
> 30 ILEVL, IONOD, IDISC, INSEQ, ITSEQ, ICYC, IDRAW, IMTDU,
> IMRP,
> 31 IACT, IORDP, IYRCT, ILOTS, IYADJ, IMCOS, IYCOS, IMUST,
> IORDC,
> 32 IAVEU, IDSCE, IWGHT, IABBT, IABC, IPFDV, IPACK, ILABL,
> IDRF,
> 33 ICLNG, ICWID, IPITM, IBUYC, IFENO, IUMR, IUMRC, IFII,
> TAXC1,
> 34 SAFLG, IIOQ, IBTCH, IYUSE, ICYCF, IPERS, ISTYL, IPODY,
> ITFDY,
> 35 IFRFG, ISACT, IUMAT, IVULI, IVULP, INCCFG, IWHS, ISOFL,
> ILCC,
> 36 IJIT, IUNCN, IMAXR, IMINR, IPCTK, IGTEC, IMAXP, IMTDP,
> IYTDP,
> 37 ITGQY, IARRT, IMXIN, IDMTF, IDMC1, IDMC2, IMBHD, IDLTR,
> 38 IMQDY, IMBWT, IMPOT, IMUSC, IMUMB, IMUMA, IPURC, IMSUM,
> 39 IBUSY, IDSCR, IMPSD, ILOC, IMCOM, IGLNO, IFCI, IMDCRT,
> ICOND,
> 40 IREF01, IREF02, IREF03, IREF04, IREF05,
> 41 0 AS length,
> 42 IORIGN, IMCOMC, IMFRMC, IMREVL, IMFORM, IMCCTL, IMBNDP,
> 43 IMBNDC, IMMNDT, IMBWIP, IMPMTH, IMCMTH, IMQLED, IMALOT,
> 44 IMNOT1, IMNOT2, IMMFGR, IMVFRY, IMSPEC, IMTLVL, IMQLE2,
> 45 IMSSPC, IMASTB, IMCNTR, IMPKD1, IMPKD2, IMHZRD, IMCSTC,
> 46 IMCORP, IMQCTL, IMCQTY, IDSALW
> 47 FROM IIM
> 48 WHERE IREF05=' '
> 49 OR SUBSTR(IREF05,1,1) BETWEEN 'A' AND 'Z'
> 50 );
> * * * * * E N D O F S O U R C E * *
*
> * *
> 5722SS1 V5R1M0 010525 Run SQL Statements IIML88
> 08/13/02 14:57:19 Page 3
> Record *...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6
> ...+... 7 ...+... 8 SEQNBR Last change
> MSG ID SEV RECORD TEXT
> SQL0154 30 1 Position 1 UNION and UNION ALL for CREATE VIEW not
> valid.
> Message Summary
> Total Info Warning Error Severe Terminal
> 1 0 0 0 1 0
> 30 level severity errors found in source
> * * * * * E N D O F L I S T I N G * *
> * * *
>
> Rob Berendt
> --
> "They that can give up essential liberty to obtain a little temporary
> safety deserve neither liberty nor safety."
> Benjamin Franklin
> _______________________________________________
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email: MIDRANGE-L@midrange.com
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
> or email: MIDRANGE-L-request@midrange.com
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
