Posted by Dia on 11/30/06 09:41

Hi there,

I struggle to get this going i would like to insert data into 2 tmp
tables in a view.
If i run the code on it's own it works perfectly until i want to create

a view it complains about the INSERT


this is my code


Create view dbo.vew_Switches
As


INSERT INTO tmpIns
Select
Distinct


BIV.DATE,
BIV.ID,
CA.NAME,
BIV.IND,
BIV.AMOUNT,
BIV.UNITS,
BIV.INAME,
MB.NO


from Cars BIV


LEFT JOIN MountainBikes MB
ON MB.ID = BIV.ID
AND MB.CLASS = BIV.CLASS
AND MB.NUMBER = BIV.NUMBER
AND MB.DATE = BIV.DATE


LEFT JOIN Caterpillars CA
ON CA.ID = MB.NO


where BIV.CLASS = 'SWCH'
and BIV.IND = 'IN'
AND BIV.UNITS = 0
AND BIV.AMOUNT <> 0
ORDER BY BIV.DATE ASC


------ Step 2 -------Into tmpOuts


Insert Into tmpOuts ---- All Switches In ----


Select
Distinct


BIV.DATE,
BIV.ID,
CA.NAME,
BIV.IND,
BIV.AMOUNT,
BIV.UNITS,
BIV.NAME,
MB.NO


from Cars BIV


LEFT JOIN Mountainbikes MB
ON MB.ID = BIV._ID
AND MB.CLASS = BIV.CLASS
AND MB.NUMBER = BIV.NUMBER
AND MB.DATE = BIV.DATE


LEFT JOIN Caterpillars CA
ON CA.ID = MB.NO


where BIV.CLASS = 'SWCH'
and BIV.IND = 'OUT'
AND BIV.UNITS = 0
AND BIV.AMOUNT <> 0
ORDER BY BIV.DATE ASC


----------------------Step 3 ----------------


Select


Distinct
ins.DATE,
ins.ID,
ins.NAME ,
insIND,
ins.AMOUNT/100 as AmountIn,
outs.IND,
outs.AMOUNT/100 as AmountOut,
outs.NAME



>From tmpIns ins


Join tmpOuts outs
ON OUTS.ID = INS.ID
where outs.NAME = ins.NAME
and outs.NO = ins.NO

----- truncate step ----
Truncate table tmpIns
Truncate table tmpOuts

[Back to original message]