with matrix1 as
(select distinct
productA,
customerid,
purchaseA
from dbA),
matrix2 as
(select distinct
productB,
purchaseid,
purchaseB
from dbB),
productscores as
(select distinct
A.productA,
B.productB,
A.purchaseA,
B.purchaseB,
A.customerid,
B.purchaseid
from matrix1 A
left join matrix2 B
on A.customerid=B.purchaseid)
create or replace table FormattedMatrix as
select distinct
A.productA,
A.productB,
A.customerid,
A.purchaseA + A.purchaseB as TotalPurchase,
B.mastertransaction
from productscores A
inner join mastertable B
on B.mastertransaction= A.customerid
i want to create temporary tables - matrix1, matrix2 which are used for the temp table productscores, and then to create a table called FormattedMatrix based of this. I believe this is possible, however i keep obtaining errors such as
'unexpected 'CREATE'. syntax error unexpected 'AS'.
or
'unexpected ;' before the create table.
or
'productscores does not exist'.
The reason for creating FormattedMatrix in this way, and not just in one step, is because there is a lot more calculations which are performed in the creation of matrix1, matrix2 and productscores, i have just cut it down for readability.
I am new to sql, so am looking for advice on how to create new tables using 'create or replace' based on temporary tables, and if anyone knows of any resources to help me learn syntax for these type of situations. I find myself just randomly adding commas or indentation to try get things to run, and would like more structure to my learning. Thanks