quote:

---

Originally posted by AdamWest

I have a 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 temp table
I think then there would be an easy way to get around this problem.

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) )

---

SELECT * INTO #tmpTable
(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) 
SELECT * FROM #tmpTable;