SQL error in Invantive Control for Excel

Question

This is for Invantive Control for Excel.

What could be causing an error at the second CREATE TABLE?

CREATE TABLE niveau1@inmemorystorage
AS
SELECT ID
,      Code
,      AddressLine1
,      Name
,      Parent
FROM   Accounts
WHERE  ID=$P{P_ACCOUNTID}

CREATE TABLE niveau2@inmemorystorage
AS
SELECT ID
,      Code
,      AddressLine1
,      Name
,      Parent
FROM   Accounts
WHERE  Parent IN (SELECT ID FROM niveau1@inmemorystorage)

CREATE TABLE niveau3@inmemorystorage
AS
SELECT ID
,      Code 
,      AddressLine1
,      Name
,      Parent
FROM   Accounts
WHERE  Parent IN (SELECT ID FROM niveau2@inmemorystorage)

SELECT * FROM niveau1@inmemorystorage
UNION ALL
SELECT * FROM niveau2@inmemorystorage
UNION ALL
SELECT * FROM niveau3@inmemorystorage

Hi , Welcome to SO . This seems to be your first questions . I suggest you to go through the SO guidelines on formatting the question. Always Wrap your code inside wrap tag . i did it for you now mate.Happy coding ! — Raja Gopal, Oct 14 '16 at 04:40
Please note that the construct `where ... in ( select ... )` currently causes the part after the IN to be evaluated on every row in the from. Invantive SQL currently makes no difference between correlated and uncorrelated subqueries. For performance reasons, you might want to rewrite it into a join instead of a in (). — Guido Leenders, Oct 14 '16 at 06:31
The error message is: Syntax error on line 11, column 0 'CREATE': mismatched input 'CREATE' expecting {, '*', GROUP, INTERSECT, MINUS_C, ORDER, UNION, AND, OR, '-', '+', '/', CONCAT_OP} — Leo H, Oct 16 '16 at 13:25
@LeoH The error 'Syntax error on line 11' with 'create' signals that you have sent both create table niveau1 as well as niveau2 to the SQL engine in one batch. You can batch SQL statements, but you need to split with ';' instead of whitespaces. Or remove the whitespaces between the two create table statements. Does that fix it for you? — Guido Leenders, Oct 17 '16 at 21:14
No, adding ; to the statements gives me an error "unknown character ;". — Leo H, Oct 25 '16 at 04:38

Guido Leenders · Accepted Answer · 2016-10-14T06:55:16.060

You have probably received an error: "The identifier 'Accounts' is ambiguous.". You can add the identifier where necessary.

The working SQL for Exact Online accounts is shown below. Please note the use of create or replace table instead of create table, the privileges don't change (when there are any) but when necessary the table is dropped first.

CREATE OR REPLACE TABLE niveau1@inmemorystorage
AS
SELECT act.ID
,      act.Code
,      act.AddressLine1
,      act.Name
,      act.Parent
FROM   exactonlinerest..Accounts act


CREATE OR REPLACE TABLE niveau2@inmemorystorage
AS
SELECT act.ID
,      act.Code
,      act.AddressLine1
,      act.Name
,      act.Parent
FROM   exactonlinerest..Accounts act
join   niveau1@inmemorystorage n1
on     n1.id = act.Parent

CREATE OR REPLACE TABLE niveau3@inmemorystorage
AS
SELECT act.ID
,      act.Code 
,      act.AddressLine1
,      act.Name
,      act.Parent
FROM   exactonlinerest..Accounts act
join   niveau2@inmemorystorage n2
on     n2.id = act.Parent

SELECT * FROM niveau1@inmemorystorage
UNION ALL
SELECT * FROM niveau2@inmemorystorage
UNION ALL
SELECT * FROM niveau3@inmemorystorage

The answer on the deleted next question is actually the best one. With this syntax I get good results. — Leo H, Oct 25 '16 at 04:44

SQL error in Invantive Control for Excel

1 Answers1