Skip to content

SAP IQ: Using Views for Insert Without Columns Names

August 18, 2014

In this document I explain how to use views to insert values on base tables.

The problem

Let create the following table:

CREATE TABLE myFact (
myFID integer,
myFDate datetime,
myFValue1 varchar(20),
myFValue2 int,
myFValue3 int )
GO

And let insert values in that table with a simple INSERT

INSERT INTO myFact
VALUES (1, getdate(), ‘Fact Value 1’, 100, 1000);

1 record(s) affected
[Executed: 15/08/2014 3:27:03 PM] [Execution: 500ms]

Now let assume that we need modify the myFValue1 column to be varchar(40).

ALTER TABLE myFact
ALTER myFValue1 varchar(40);

>[Error] Script lines: 14-17 ————————
SQL Anywhere Error -1013024: ALTER TABLE MODIFY <column> <datatype> is not supported.
— (dblib/db_alter.cxx 411) _

Since SAP IQ does not allows modifying the data type of a column, the only option available is to create a new column with the new data type, copy the data from the original column to the new column, drop the original column and rename the new column as the original one.

ALTER TABLE myFact
ADD myNewColumn varchar(40) NULL

UPDATE myFact
SET myNewColumn = myFValue1

ALTER TABLE myFact
DROP myFValue1;

Since the columns are in different order, the insert without columns give an error.

INSERT INTO myFact
VALUES (2, getdate(), ‘Fact Value 2’, 200, 2000)

>[Error] Script lines: 45-49 ————————
SQL Anywhere Error -157: Cannot convert Fact Value 2 to a integer(10) (column myFValue2) _

[Executed: 18/08/2014 10:08:19 AM] [Execution: 0ms]

Even if inserting without columns names has been identified as a bad practice, we still find SQL code using it.

Imagine now that this is propagated in hundreds of store procedures that insert rows on hundreds of tables with hundreds of millions of rows each table. Maintaining changes in the structure of the tables will demand a colossal amount of time and resources.

The Solution

  1. Rename the base table.
  2. Create a view derived from the base table (this view must have the same name as the original table).
  3. Use the view for your insert.
  4. Restructure and recompile the view every time the base table is modified.

Let drop and recreate the myFact table.

drop table myFact
GO

CREATE TABLE myFact (
myFID integer
myFDate datetime,
myFValue1 varchar(20),
myFValue2 int,
myFValue3 int
)
GO

And, let insert a row in that table.

INSERT INTO myFact
VALUES (1, getdate(), ‘Fact Value 1’, 100, 1000)

Now, let modify the definition of the table as in the first part.

ALTER TABLE DBA.myFact
ADD myNewColumn varchar(40) NULL
GO
UPDATE myFact
SET myNewColumn = myFValue1
GO
ALTER TABLE myFact
DROP myFValue1
GO
ALTER TABLE myFact
RENAME myNewColumn TO myFValue1
GO

From or initial test, we know the insert without columns names won’t work. So let me test the proposed solution.

First, let rename the base table.

ALTER TABLE myFact

RENAME myFactBaseTable

GO

Now, let create a view derived from the base table. This view will have the name of the base table before been renamed and the columns will follow the same order as the initial definition of the base table.

CREATE VIEW myFact
AS
SELECT myFID, myFDate, myFValue1, myFValue2, myFValue3
FROM myFactBaseTable

And now, we can insert on the view.

INSERT INTO myFact
VALUES (2, getdate(), ‘Fact Value 2’, 200, 2000)

1 record(s) affected
[Executed: 18/08/2014 10:35:41 AM] [Execution: 30ms]

If we need to modify the structure of the base table again, we need to recompile the view.

ALTER VIEW myFact RECOMPILE
GO

Please note the following limitation:

“Inserts can be done into views, provided the SELECT statement defining the view has only one table in the FROM clause and does not contain a GROUP BY clause, an aggregate function, or involve a UNION operation.” [1]

[1] SAP IQ 16.0 SP08. Document Version: 1.0-2014-05-22 “Reference: Statement and Options”; SAP AG.

Advertisements

From → SAP IQ

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: