Create View that includes a Volatile Table

Database

Create View that includes a Volatile Table

I'm running into some trouble with syntax:

I'm tring to create a view that uses the three statements to present a table.  The first statment creates a volatile table with data, the second runs a join query using the volatile table, and the third drops the table.  It works, but I'd like to create a view that does the same thing. 

Can somebody post the syntax for the view.  I've tried just adding :

REPLACE VIEW m308332.CostWithChildren AS

LOCKING ROW FOR ACCESS

to the top of the query, but I'm chasing syntax errors forever.  Can this even be done?

Here are the three statements:

CREATE MULTISET VOLATILE TABLE pc

AS (SELECT LineID AS ParentLineID,

InvNo AS ParentInv,

EAPO,

ChildInv

FROM m308332.InvLineItem

WHERE ChildInv <> '' AND ChildInv NOT = 'UNK')

WITH DATA

NO PRIMARY INDEX

ON COMMIT PRESERVE ROWS;

 

SELECT p.LineID,

p.InvNo,

p.EAPO,

p.ChildInv,

p.CostType,

p.Module,

p.PartNo,

p.Descr,

p.Qty,

p.UnitCost,

p.Fee,

CASE WHEN (p.LineID = pc.ParentLineID

AND p.InvNo = pc.ParentInv)

THEN 0

ELSE p.TotalCost

END TotalCost,

CASE WHEN (p.LineID = pc.ParentLineID

AND p.InvNo = pc.ParentInv)

THEN 1

ELSE 0

END ChildFlag,

p.Vendor,

p.ChildInv,

p.Ref,

p.RepairDesc,

p.NewUsed,

p.MRBNo,

p.Reason,

p.Sourc,

p.Stndrd,

p.CustBill

FROM m308332.InvLineItem p

LEFT OUTER JOIN pc

ON p.LineID = pc.ParentLineID

AND p.InvNo = pc.ParentInv;

DROP TABLE pc

2 REPLIES
Senior Supporter

Re: Create View that includes a Volatile Table

A view is a view and can not create volatile tables.

But why don't you use a derived table?

SELECT p.LineID,

p.InvNo,

p.EAPO,

p.ChildInv,

p.CostType,

p.Module,

p.PartNo,

p.Descr,

p.Qty,

p.UnitCost,

p.Fee,

CASE WHEN (p.LineID = pc.ParentLineID

AND p.InvNo = pc.ParentInv)

THEN 0

ELSE p.TotalCost

END TotalCost,

CASE WHEN (p.LineID = pc.ParentLineID

AND p.InvNo = pc.ParentInv)

THEN 1

ELSE 0

END ChildFlag,

p.Vendor,

p.ChildInv,

p.Ref,

p.RepairDesc,

p.NewUsed,

p.MRBNo,

p.Reason,

p.Sourc,

p.Stndrd,

p.CustBill

FROM m308332.InvLineItem p

LEFT OUTER JOIN
(SELECT LineID AS ParentLineID,

InvNo AS ParentInv,

EAPO,

ChildInv

FROM m308332.InvLineItem

WHERE ChildInv <> '' AND ChildInv NOT = 'UNK'
) pc

ON p.LineID = pc.ParentLineID

AND p.InvNo = pc.ParentInv;

Senior Supporter

Re: Create View that includes a Volatile Table

REPLACE VIEW m308332.CostWithChildren AS

LOCKING ROW FOR ACCESS

is missing...