CODEWITHSUNDEEP
sqlsql-server
Gabriel
Gabriel

Reputation: 53

SQL ERROR : Msg 102, Level 15, State 1, Line 4 Incorrect syntax near ','

I am trying to run this code:

SELECT 
    FIN AS 'LIN',
    CUSIP, 
    Borrower_Name, 
    Alias,
    DS_Maturity, 
    Spread, 
    Facility, 
    Facility_Size, 
    Log_date
FROM 
    [Main].[FacilityInformation]
WHERE 
    CUSIP IN ('00485GAC2', 'N1603LAD9')
    OR (YEAR(DS_Maturity) = (2019, 2024)
    AND ((Borrower_Name LIKE 'Acosta Inc , Bright Bidco BV%'
          OR Alias LIKE 'Acosta 9/14 (18.61) Non-Extended Cov-Lite, Lumileds 2/18 Cov-Lite%')))

But I got this error:

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ','

Between 2019 and 2024

I tried to modify this piece of code, with or without quotes and parenthesis, none of this worked. Thank you for the help.

Upvotes: 0

Views: 7266

Answers (6)

GMB
GMB

Reputation: 222402

Here:

YEAR(DS_Maturity) = ('2019', '2024')

This is not valid syntax, you probably want the IN operator instead. Also, YEAR() return a number, not a string

YEAR(DS_Maturity) IN (2019, 2024)

Here is a new version of your query. Formating and indenting make it easier to understand the query and spot problems:

SELECT 
    FIN AS [LIN], 
    CUSIP, 
    Borrower_Name, 
    Alias,
    DS_Maturity, 
    Spread, 
    Facility, 
    Facility_Size, 
    Log_date
FROM [Main].[FacilityInformation]
WHERE 
    CUSIP IN ('00485GAC2','N1603LAD9') 
    OR (

        YEAR(DS_Maturity) = (2019, 2024)  
        AND (
            Borrower_Name LIKE 'Acosta Inc , Bright Bidco BV%' 
            OR Alias LIKE 'Acosta 9/14 (18.61) Non-Extended Cov-Lite, Lumileds 2/18 Cov-Lite%'
        )
    )

Upvotes: 1

Himanshu
Himanshu

Reputation: 3970

Replace the = with IN as you are having two values i.e. more than one with =

Upvotes: 0

antihacker 2009
antihacker 2009

Reputation: 41

Function YEAR returns int, so you need to write

YEAR(DS_Maturity) IN (2019, 2024)

or like this

YEAR(DS_Maturity) BETWEEN 2019 AND 2024

Upvotes: 4

pncsoares
pncsoares

Reputation: 63

Do you want to use the BETWEEN Sql function?

SELECT FIN AS 'LIN',
       CUSIP,
       Borrower_Name,
       Alias,
       DS_Maturity,
       Spread,
       Facility,
       Facility_Size,
       Log_date
  FROM [Main].[FacilityInformation]
 WHERE CUSIP IN ('00485GAC2', 'N1603LAD9')
    OR (YEAR(DS_Maturity) BETWEEN 2019 AND 2024
        AND ((Borrower_Name LIKE 'Acosta Inc , Bright Bidco BV%'
             OR Alias LIKE 'Acosta 9/14 (18.61) Non-Extended Cov-Lite, Lumileds 2/18 Cov-Lite%')))

Upvotes: 1

Thom A
Thom A

Reputation: 95554

Formatting your SQL with WhiteSpace and linebreaks makes for much easier reading. If you do that you get something like this:

SELECT FIN AS LIN,
       CUSIP,
       Borrower_Name,
       Alias,
       DS_Maturity,
       Spread,
       Facility,
       Facility_Size,
       Log_date
FROM [Main].[FacilityInformation]
WHERE CUSIP = ('00485GAC2', 'N1603LAD9')
   OR (YEAR(DS_Maturity) = ('2019','2024')
   AND ((Borrower_Name LIKE 'Acosta Inc , Bright Bidco BV%'
      OR Alias LIKE 'Acosta 9/14 (18.61) Non-Extended Cov-Lite, Lumileds 2/18 Cov-Lite%')));

Running that, you get the error:

Msg 102, Level 15, State 1, Line 12 Incorrect syntax near ','.

And line 12 is:

   OR (YEAR(DS_Maturity) = ('2019','2024')

You should be able to clearly see the problem is now (it should be IN not =).

Personally, however, I recommend against using YEAR in the WHERE as it makes the query non-SARGable. Instead use proper date and Boolean logic:

   OR ((DS_Maturity >= '20190101' AND DS_Maturity < '20200101')
   OR  ((DS_Maturity >= '20240101' AND DS_Maturity < '20250101')...)

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You have = where you should have in. But the types are also wrong. I would suggest:

SELECT FIN AS 'LIN', CUSIP, Borrower_Name, Alias,
       DS_Maturity, Spread, Facility, Facility_Size, 
       Log_date
FROM [Main].[FacilityInformation]
WHERE CUSIP IN ('00485GAC2', 'N1603LAD9') or 
      (YEAR(DS_Maturity) IN (2019, 2024) AND
       (Borrower_Name LIKE 'Acosta Inc , Bright Bidco BV%' OR
        Alias LIKE 'Acosta 9/14 (18.61) Non-Extended Cov-Lite, Lumileds 2/18 Cov-Lite%'
       )
      )

If you intend for that to be a range, you should either include all values in the range or use BETWEEN.

Upvotes: 1

Related Questions