0

When I try to create a view, I get the error:

Msg 156, Level 15, State 1, Procedure viewFangst2021, Line 9 [Batch Start Line 445]
Incorrect syntax near the keyword 'SELECT'

I do not understand what is wrong with the code. The same thing happens if I am going to create a procedure.

Could it have something to do with settings or such in SQL? for this happens regardless of whether I create a view

CREATE VIEW viewFangst2021 
AS
    SELECT 
        FISK.FiskeNr, FISK.Fiskeslag, 
        COUNT(*) AS Antall, 
        ROUND(AVG(Vekt), 1) AS [Gjennomsnittsvekt (kg)] 
    FROM 
        FANGST, FISK 
    WHERE 
        FANGST.FiskeNr = FISK.FiskeNr 
        AND YEAR (Dato) = 2021 
    GROUP BY 
        FISK.FiskeNr, FISK.Fiskeslag 

    SELECT * 
    FROM viewFangst2021
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sander Kaupang
  • 19
  • 1
  • 7
  • 1
    You cant have 2 separate SELECT statements in a view, that's what the error is telling you – shree.pat18 May 26 '22 at 09:46
  • 4
    [Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**30 years!!** ago) and its use is discouraged – marc_s May 26 '22 at 09:48
  • 3
    You are trying to select from the view in the same batch as where you create the view. This is not allowed. If this in SSMS put a `GO` between the statements so they are in separate batches – Martin Smith May 26 '22 at 10:27
  • 3
    I recommend against syntax like `YEAR (Dato) = 2021 ` in the `WHERE` as well; this isn't SARGable. Use proper date boundaries: `Dato >= '20210101' AND Dato < '20220101'` – Thom A May 26 '22 at 10:39

0 Answers0