Re: SQL create view statement too long or complex

Michael:

(My apologies for the "Mike", some brain wires got crossed when I wrote my
prior post)

This works for me:

CREATE VIEW DEVLIB/V_InternetProfiles
AS (SELECT
PROFILEID,
substr(profileid, 1, locate(' ', Profileid)) as iNID_Model,

(case when locate('RG_INET_VLAN = 800, ', 'ATTRIBUTES') > 0
then 'FALSE'
when locate('RG_INET_VLAN = 600, ', 'ATTRIBUTES') > 0
then 'TRUE'
else 'N/A'
end) as Is_Dynamic,

(case when locate('INET_DOWN_RATE = ', 'ATTRIBUTES') = 0
then 'N/A'
else substr('ATTRIBUTES',
locate('INET_DOWN_RATE = ', 'ATTRIBUTES') + 17,
locate(', ', 'ATTRIBUTES',
locate('INET_DOWN_RATE = ', 'ATTRIBUTES') + 1)
- (locate('INET_DOWN_RATE = ', 'ATTRIBUTES') + 17))
end) as Speed_Down,

(case when locate('INET_UP_RATE = ', 'ATTRIBUTES') = 0
then 'N/A'
else substr('ATTRIBUTES',
locate('INET_UP_RATE = ', 'ATTRIBUTES') + 15,
locate(', ', 'ATTRIBUTES',
locate('INET_UP_RATE = ', 'ATTRIBUTES') + 1)
- (locate('INET_UP_RATE = ', 'ATTRIBUTES') + 15))
end) as Speed_Up

FROM DATALIB/epprofiles
WHERE 'ATTRIBUTES' like '%profileType = RG Internet%'
);

HTH,

Luis

Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries