PRSQL9111011: GP Fault Using Variable Assignment with INSERT

ID Number: Q78632

1.11

OS/2

Summary:

Problem ID: PRSQL9111011

SYMPTOMS

Attempting to run the following statement GP faults the server:

INSERT table

SELECT @rid=@rid+1, @KEY1=MIN(pub_id), @KEY2=MIN(title_id)

FROM myview WHERE title_id>@KEY2

CAUSE

Assigning values to local variables and attempting to insert them

into a table in the same statement is not allowed with

Transact-SQL.

WORKAROUND

The query should be broken up into two statements, as follows:

SELECT @rid=@rid+1, @KEY1=MIN(pub_id), @KEY2=MIN(title_id)

WHERE title_id>@KEY2

INSERT table values(@rid,@key1,@key2)

STATUS

Microsoft has confirmed this to be a problem in SQL Server version

1.11. This problem does not occur in SQL Server version 4.2.

Additional reference words: GP-fault