Selecting values into scalars/tables
Here’s just a small snippet of code that might save you a bit of a headache as your try to work out the correct syntax for SELECT… INTO… when it comes to scalar variables and tables.
As you’ll note, the syntax isn’t the same nor is it interchangeable.
Cheers,
Jody
DROP TABLE “EXTAB”;
CREATE COLUMN TABLE “EXTAB” (“FIELD” INTEGER);
CREATE PROCEDURE “EXPROC” AS
“EXFIELD” INTEGER;
BEGIN
— following syntax doesn’t work
— SELECT 5 INTO “EXTAB” FROM DUMMY;
— SELECT 5 FROM DUMMY INTO “EXFIELD”;
SELECT 5 INTO “EXFIELD” FROM DUMMY;
SELECT 5 FROM DUMMY INTO “EXTAB”;
END;
Hey Jody,
worth mentioning here is that
SELECT ... INTO <table> is actually short for
INSERT into <table> (SELECT ... FROM DUMMY);
So it persists the data in the table - something one may not expect from a SELECT statement.
Thus, I'd discourage the usage of SELECT INTO <table> for the sake of comprehensible code.
BTW: did you find this form documented anywhere? I didn't...
- Lars
Hi Lars,
It's syntax I'm familiar with from previous work so never really looked through documentation, but don't see it listed in the HANA SQL guide.
Cheers,
Jody