Re: View to create a PF?

[CRPence <CRPbottle@xxxxxxxxx>]

  The /default job CCSID/ was implemented specifically to ensure a 
non-65535 CCSID would be assigned in such scenarios [i.e. a column is 
assigned a CCSID other than *HEX for a /create/ request].  Yet, as the 
example shows [per DSPFFD given later], that effect does not seem to 
hold [consistently] for derived /query/ fields; mostly those involving 
literals, IIRC.  The effect was IMO, a defect.  Yet IIRC, there was no 
intention to change [IMO correct] that /issue/ for the CCSID of derived 
columns in a CREATE VIEW when CCSID(*HEX) is in effect for the job.

  The desired effect [the derived columns getting a non-hex CCSID] can 
be achieved easily just by setting the job CCSID to an explicit EBCDIC 
CCSID [i.e. other than *HEX] to span the CREATE.  The effect can be made 
explicitly instead, by use of the CAST function to assign a CCSID, thus 
avoiding the [IMO incorrect] default of 65535; as described in other 
posts.  But since every user profile should [long since before the prior 
decade] already have an explicit CCSID, the easiest resolution is to 
perform a request to CHGPRF CCSID(appropriate_value) for which that 
appropriate CCSID value will be established for all future jobs started 
by that user profile.  If a desirable CCSID is set in the User 
Profile(s), there is effectively no need to any specific value for the 
QCCSID system value; i.e. it can remain 65535 [aka *HEX].

Regards, Chuck

On 23 Aug 2013 05:35, Hoteltravelfundotcom wrote:
> I have a Logical view which works fine but I cannot display the data
> in the Report tool because its CCSID is HEX. If I could create it to
> PF then I could CHGPF on the CCSID (which CHGLF does not have.)
>
> Can this be done?
> This is the code:
>
> CREATE VIEW astlib.acbalmpk AS
> (
>   (SELECT
>      LMLTPC
>    , COALESCE(IRLOC1,'') as IRLOC1
>    , COALESCE(IRLOC2,'') as IRLOC2
>    , COALESCE(IRLOC3,'') as IRLOC3
>    , IRPRT#
>    , IRQOH#
>    , IRWHS#,
>    , '' as IEPRT#
>    , '.00' as IEQOH#
>    , '' as IELOC1
>    , '' as IELOC2
>    , '' as IELOC3
>    , '' as IERIDC
>    , '' as IEWHS#
>    FROM
>      ( SELECT LMLTPC, LMLOC1, LMLOC2, LMLOC3
>        FROM ASTDTA.ICLOCMLM
>        WHERE LMLTPC IN ('PAL', 'RAK' )
>      ) t1
>    left outer join
>      ( SELECT IRLOC1, IRLOC2, IRLOC3, IRPRT#, IRQOH#, IRWHS#
>        FROM ASTDTA.ICBLDTIR
>      ) t2
>    On LMLOC1=IRLOC1 AND LMLOC2=IRLOC2 AND LMLOC3=IRLOC3
>   )
>  UNION ALL
>   (SELECT
>      ' ' as LMLTPC
>    , ' ' as IRLOC1
>    , ' ' as IRLOC2
>    , ' ' as IRLOC3
>    , '' as IRPRT#
>    , '.00' as IRQOH#
>    , '' as IRWHS#
>    , IEPRT#
>    , IEQOH#
>    , IELOC1
>    , IELOC2
>    , IELOC3
>    , IERIDC
>    , IEWHS#
>    FROM ASTDTA.ICBALMIE
>   )
> )