CODEWITHSUNDEEP
data-warehouse
John Doe
John Doe

Reputation: 3

Cant seem to find the Syntax error

Can somebody tell me what I am doing wrong here, I am getting incorrect syntax near where. Whats wrong with the statement? 

INSERT INTO dbo.a1
    select x1 as id, x2 as enc_id, x3 as dev,x4 as mang, x4 as sre, x5 as phase,x6, x7, x8
    from
    (select *,
    row_number() over(partition by x2 order by x8)  as rank
    from ccsm.n9 where xx=1 and xx_pat=1 and xx_encz='Tesz')
     where rank=1;

Upvotes: 0

Views: 32

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522181

You never told us what version of SQL you are using, but one possibility is that your derived table needs an alias:

INSERT INTO dbo.a1
SELECT t.x1, t.x2, t.x3, t.x4, t.x4, t.x5, t.x6, t.x7, t.x8
FROM
(
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY x2 ORDER BY x8) AS rank
    FROM ccsm.n9
    WHERE xx = 1 and xx_pat = 1 and xx_encz = 'Tesz'
) t
WHERE t.rank = 1;

I know that Oracle enforces the derived table alias rule, and possibly other databases as well. Note that the aliases you had in the SELECT statement serve no purpose because the values are just being inserted. If you wish to insert x4 twice then just repeat it twice.

Upvotes: 1

Related Questions