Please be aware that the SAP SQL Anywhere Forum will be shut down on August 29th, 2024 when all it's content will be migrated to the SAP Community.

I have the following script in ISQL:

CREATE TABLE Temp (Col1 VARCHAR(10), Col2 VARCHAR(10));
INSERT INTO Temp VALUES('AAA', 'BBB');
BEGIN
  DECLARE r_Temp Temp%ROWTYPE;
  DECLARE v_Dummy VARCHAR(10);

  SELECT MAX(Col1) INTO r_Temp.Col1 FROM Temp;

  SELECT r_Temp.Col1, v_Dummy;
END;

This gives me the error message "User ID 'r_Temp' does not exist". Changing it a little to

CREATE TABLE Temp (Col1 VARCHAR(10), Col2 VARCHAR(10));
INSERT INTO Temp VALUES('AAA', 'BBB');
BEGIN
  DECLARE r_Temp Temp%ROWTYPE;
  DECLARE v_Dummy VARCHAR(10);

  SELECT MAX(Col1), MAX(Col2) INTO r_Temp.Col1, v_Dummy FROM Temp;

  SELECT r_Temp.Col1, v_Dummy;
END;

works like expected. More experiments give me the impression that a single ROWTYPE-Variable in the INTO clause triggers this problem.

Is this a parser bug or do I overlook anything? Is there a better workaround than using a dummy variable?

I'm using "SQL Anywhere Network Server Version 17.0.11.7672"

asked 02 Jul, 06:04

tedfroehlich's gravatar image

tedfroehlich
40571124
accept rate: 18%

edited 02 Jul, 06:08


Does it work with "SELECT ... INTO VARIABLE r_Temp.Col1"?

permanent link

answered 02 Jul, 10:53

Volker%20Barth's gravatar image

Volker Barth
40.5k365556827
accept rate: 34%

converted 03 Jul, 02:19

Thanks, that fixes the problem!

Is this intended behavior or a workaround? From the docs I'd assume that the INTO VARIABLE clause is intended for putting a result into a row variable, as in "SELECT * INTO VARIABLE r_Temp FROM Temp", but r_Temp.Col1 IMHO is not a row variable but a simple variable...

(02 Jul, 11:30) tedfroehlich
Replies hidden

I'm not sure whether this is intended behaviour or not - in my understanding, the basic "INTO clause" (i.e. without specifying LOCAL TEMPORARY TABLE | TABLE | VARIABLE) seems to expect a two-part name only when the select list contains one element and the destination is a new table:

To create a permanent table with one column, the table name must be specified as owner.table.

Apparently the database engine interprets your row variable as such a table destination... Of course it could/should know that you are using a row variable.

(03 Jul, 02:27) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×15
×6
×5

question asked: 02 Jul, 06:04

question was seen: 125 times

last updated: 03 Jul, 02:27