The snippets assume that a filed "Status" of type "short" shall be inserted. In this Example, the field "Status" of type "short" is supposed to be inserted as new column 6 and the values for the new column are received by a sub query within the SELECT clause. The sample uses the Adventure Works database. Additionally, this example supposes that the view is being replicated into the table "dwh.foo". Please be thorough and careful when implementing such operations!
CREATE VIEW "views.aw" AS
SELECT
"salesorderheader.SalesOrderID",
"salesorderheader.CustomerID",
"salesorderheader.SalesPersonID",
"salesorderheader.TerritoryID",
"salesorderheader.PurchaseOrderNumber",
"salesorderheader.CurrencyCode"
FROM
"adv.salesorderheader"
--Create optimization and batch job that replicates to dwh.foo...
BEGIN
DECLARE STRING str = '(';
LOOP ON(SELECT NameInSource, DataType FROM "SYS.Columns" WHERE SchemaName = 'dwh' AND TableName = 'foo' ORDER BY Position OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY) as cur
BEGIN
str = str ||cur.NameInSource || ' ' || cur.DataType||',';
END
str = str || '"status" short';
LOOP ON(SELECT NameInSource, DataType FROM "SYS.Columns" WHERE SchemaName = 'dwh' AND TableName = 'foo' ORDER BY Position OFFSET 5 ROWS) as cur
BEGIN
str = str || ', ' ||cur.NameInSource || ' ' || cur.DataType;
END
str = str || ')';
EXECUTE IMMEDIATE 'CREATE TABLE "dwh.bar"'||str;
END;;
INSERT INTO dwh.bar SELECT "a.SalesOrderID",
"a.CustomerID",
"a.SalesPersonID",
"a.TerritoryID",
"a.PurchaseOrderNumber",
CAST((SELECT "b.Status" FROM "adv.salesorderheader" as b WHERE b.SalesOrderID = a.SalesOrderID) as short) as "Status",
"a.CurrencyCode"
FROM "dwh.foo" as a;;
DROP TABLE "dwh.foo";;
SELECT a.* INTO "dwh.foo" from "dwh.bar" as a;;
DROP TABLE "dwh.bar";;
ALTER VIEW "views.aw"
AS
SELECT
"salesorderheader.SalesOrderID",
"salesorderheader.CustomerID",
"salesorderheader.SalesPersonID",
"salesorderheader.TerritoryID",
"salesorderheader.PurchaseOrderNumber",
"salesorderheader.Status",
"salesorderheader.CurrencyCode"
FROM
"adv.salesorderheader";;
Comments
0 comments
Please sign in to leave a comment.