incorrect syntax near - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

incorrect syntax near

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-10-18 : 14:13:27

Could someone tell me why I'm getting incorrect syntax near it's referring to all of these lines:

SELECT fo, (count(cossn), 0) AS [TotPenFODDS], 0 AS [Pend250], 0 AS [Pend300], 0 AS [Pend351],0 AS [Pend400]

Here'd the entire query:

SELECT     fo, (count(cossn), 0) AS [TotPenFODDS], 0 AS [Pend250], 0 AS [Pend300], 0 AS [Pend351],0 AS [Pend400]
FROM         T16pendall
WHERE      MFT_POSN1_CD='b' OR MFT_POSN1_CD='d' OR (MFT_POSN1_CD= 'a' AND
AGED_ALIEN_RSW = 'y')
GROUP BY fo
UNION
SELECT      fo, (count(cossn), 0) AS [TotPenFODDS], 0 AS [Pend250], 0 AS [Pend300], 0 AS [Pend351], 0 AS [Pend400]
FROM         T16pendall
WHERE      (MFT_POSN1_CD = 'b' OR
                         MFT_POSN1_CD = 'd') AND (DATEDIFF(day, FLG_CDT, GETDATE()) > 250) AND (DATEDIFF(day, FLG_CDT, GETDATE()) < 299) OR
                         (MFT_POSN1_CD = 'a') AND (DATEDIFF(day, FLG_CDT, GETDATE()) > 250) AND (AGED_ALIEN_RSW = 'y') AND (DATEDIFF(day, FLG_CDT, GETDATE()) < 299
GROUP BY fo
UNION
SELECT      fo, (count(cossn), 0) AS [TotPenFODDS], 0 AS [Pend250], 0 AS [Pend300], 0 AS [Pend351],0 AS [Pend400]
FROM        T16pendall
WHERE        (MFT_POSN1_CD = 'b' OR
                         MFT_POSN1_CD = 'd') AND (DATEDIFF(day, FLG_CDT, GETDATE()) > 300) AND (DATEDIFF(day, FLG_CDT, GETDATE()) < 350) OR
                         (MFT_POSN1_CD = 'a') AND (DATEDIFF(day, FLG_CDT, GETDATE()) > 300) AND (AGED_ALIEN_RSW = 'y') AND (DATEDIFF(day, FLG_CDT, GETDATE()) < 350
 
GROUP BY fo
UNION
SELECT      fo, (count(cossn), 0) AS [TotPenFODDS], 0 AS [Pend250], 0 AS [Pend300], 0 AS [Pend351],0 AS [Pend400]
FROM        T16pendall
WHERE        (MFT_POSN1_CD = 'b' OR
                         MFT_POSN1_CD = 'd') AND (DATEDIFF(day, FLG_CDT, GETDATE()) > 351) AND (DATEDIFF(day, FLG_CDT, GETDATE()) < 400) OR
                         (MFT_POSN1_CD = 'a') AND (DATEDIFF(day, FLG_CDT, GETDATE()) > 351) AND (AGED_ALIEN_RSW = 'y') AND (DATEDIFF(day, FLG_CDT, GETDATE()) < 400)
 GROUP BY fo
UNION
SELECT      fo, (count(cossn), 0) AS [TotPenFODDS], 0 AS [Pend250], 0 AS [Pend300], 0 AS [Pend351],0 AS [Pend400]
FROM         T16pendall
WHERE        (MFT_POSN1_CD = 'b' OR
                         MFT_POSN1_CD = 'd') AND (DATEDIFF(day, FLG_CDT, GETDATE()) > 400) OR
                         (MFT_POSN1_CD = 'a') AND (DATEDIFF(day, FLG_CDT, GETDATE()) > 400) AND (AGED_ALIEN_RSW = 'y')
GROUP BY fo

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-18 : 14:22:17

This isn't correct: (count(cossn), 0)

I am not sure what you are trying to do, but it needs to be corrected.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-10-18 : 14:51:35

Thanks I got rid of that and it worked but not the way I wanted it too.

I'm trying to get the counts for the following fields:

TotPenFODDS,
Pend250 (over 250 - 299 Days)
Pend300 (over 300 - 350 Days)
Pend351 (over 351 - 400 Days)
Pend400 (over 400 Days)

This works fine since I got rid of the (count(cossn), 0)


SELECT     fo, count(cossn) AS [TotPenFODDS], 0 AS [Pend250], 0 AS [Pend300], 0 AS [Pend351],0 AS [Pend400]
FROM         T16pendall
WHERE      MFT_POSN1_CD='b' OR MFT_POSN1_CD='d' OR (MFT_POSN1_CD= 'a' AND
AGED_ALIEN_RSW = 'y')
GROUP BY fo
UNION
SELECT      fo, count(cossn) AS [TotPenFODDS], 0 AS [Pend250], 0 AS [Pend300], 0 AS [Pend351], 0 AS [Pend400]
FROM         T16pendall
WHERE      (MFT_POSN1_CD = 'b' OR
                MFT_POSN1_CD = 'd') AND (DATEDIFF(day, FLG_CDT, GETDATE()) > 250) AND (DATEDIFF(day, FLG_CDT, GETDATE()) < 299) OR
               (MFT_POSN1_CD = 'a') AND (DATEDIFF(day, FLG_CDT, GETDATE()) > 250) AND (AGED_ALIEN_RSW = 'y') AND (DATEDIFF(day, FLG_CDT, GETDATE()) < 299)
GROUP BY fo
UNION
SELECT      fo, count(cossn) AS [TotPenFODDS], 0 AS [Pend250], 0 AS [Pend300], 0 AS [Pend351],0 AS [Pend400]
FROM        T16pendall
WHERE        (MFT_POSN1_CD = 'b' OR
                         MFT_POSN1_CD = 'd') AND (DATEDIFF(day, FLG_CDT, GETDATE()) > 300) AND (DATEDIFF(day, FLG_CDT, GETDATE()) < 350) OR
                         (MFT_POSN1_CD = 'a') AND (DATEDIFF(day, FLG_CDT, GETDATE()) > 300) AND (AGED_ALIEN_RSW = 'y') AND (DATEDIFF(day, FLG_CDT, GETDATE()) < 350)
 
GROUP BY fo
UNION
SELECT      fo, count(cossn) AS [TotPenFODDS], 0 AS [Pend250], 0 AS [Pend300], 0 AS [Pend351],0 AS [Pend400]
FROM        T16pendall
WHERE        (MFT_POSN1_CD = 'b' OR
                         MFT_POSN1_CD = 'd') AND (DATEDIFF(day, FLG_CDT, GETDATE()) > 351) AND (DATEDIFF(day, FLG_CDT, GETDATE()) < 400) OR
                         (MFT_POSN1_CD = 'a') AND (DATEDIFF(day, FLG_CDT, GETDATE()) > 351) AND (AGED_ALIEN_RSW = 'y') AND (DATEDIFF(day, FLG_CDT, GETDATE()) < 400)
 GROUP BY fo
UNION
SELECT      fo, count(cossn) AS TotPenFODDS, 0 AS Pend250, 0 AS Pend300, 0 AS Pend351,0 AS Pend400
FROM         T16pendall
WHERE        (MFT_POSN1_CD = 'b' OR
              MFT_POSN1_CD = 'd') AND (DATEDIFF(day, FLG_CDT, GETDATE()) > 400) OR
              (MFT_POSN1_CD = 'a') AND (DATEDIFF(day, FLG_CDT, GETDATE()) > 400) AND (AGED_ALIEN_RSW = 'y')
GROUP BY fo


But it's giving me this, it's not giving me any numbers in the other fields.  Is my query wrong?
FO   TotPenFODDS  Pend250  Pend300 Pend351 Pend400
B19	397	    0	    0	   0	   0
704	341	    0	    0	   0	   0
317	210	    0	    0	   0	   0
K82	4	    0	    0	   0	   0
399	643	    0	    0	   0	   0
521	140	    0	    0	   0	   0
856	1713	    0	    0	   0	   0
918	587	    0	    0	   0	   0
293	643	    0	    0	   0	   0
887	1380	    0	    0	   0	   0

Here's the T16Pendall Table and some data to go into it:

CREATE TABLE [dbo].[T16pendall](
[COSSN] [char](6) NOT NULL,
[AGED_ALIEN_RSW] [char](1) NOT NULL,
[MFT_POSN1_CD] [char](1) NOT NULL,
[FO] [varchar](3) NOT NULL,
[FLG_CDT] [datetime] NULL
) ON [PRIMARY]

select '458962', 'Y', 'D', '200', '3/15/1993' union all

select '454442', 'Y', 'D', 'A38', '9/13/1996' union all

select '569962', 'Y', 'D', '250', '12/21/1999' union all

select '895962', 'Y', 'D', 'C07', '7/10/2002' union all

select '999962', 'Y', 'D', '199', '2/3/2003' union all

select '452262', 'Y', 'D', '265', '1/22/2004' union all

select '458962', 'Y', 'D', '200', '5/5/2004' union all

select '458962', 'Y', 'D', '858', '6/8/2004' union all

select '458962', 'Y', 'D', '334', '7/19/2004' union all

select '458962', 'Y', 'D', '200', '4/8/2010'

Thanks!

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-10-18 : 15:25:54

I think you will find this skeleton will run MUCH faster and be easier to read and maintain:

SELECT fo, COUNT(cossn) AS pend_fodds_tot,
SUM (CASE WHEN (DATEDIFF(DAY, flg_cdt, CURRENT_TIMESTAMP) BETWEEN 251
AND 298
THEN 1 ELSE 0 END) AS pend_250,
SUM (CASE WHEN (DATEDIFF(DAY, flg_cdt, CURRENT_TIMESTAMP) BETWEEN 301
AND 349
THEN 1 ELSE 0 END) AS pend_300,

SUM (CASE WHEN (DATEDIFF(DAY, flg_cdt, CURRENT_TIMESTAMP) BETWEEN 350
AND 399
THEN 1 ELSE 0 END) AS pend_351,
SUM (CASE WHEN (DATEDIFF(DAY, flg_cdt, CURRENT_TIMESTAMP) > 400
THEN 1 ELSE 0 END) AS pend_400
FROM T16_Pend_All
WHERE (mft_posn1_cd IN ('b' , 'd')
OR (mft_posn1_cd = 'a' AND aged_alien_rsw = 'y')
GROUP BY fo;

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-10-18 : 15:28:27

Got a bit further...

I got the first two queries to work:

Select a.fo, Totpenfodds, Pend250
From
(SELECT fo, count(cossn) AS [TotPenFODDS]
FROM T16pendall
WHERE MFT_POSN1_CD='b' OR MFT_POSN1_CD='d' OR (MFT_POSN1_CD= 'a' AND
AGED_ALIEN_RSW = 'y')
GROUP BY fo
)a

inner join

(SELECT fo, count(cossn) AS [Pend250]
FROM T16pendall
WHERE (MFT_POSN1_CD = 'b' OR
MFT_POSN1_CD = 'd') AND (DATEDIFF(day, FLG_CDT, GETDATE()) > 250) AND (DATEDIFF(day, FLG_CDT, GETDATE()) < 299) OR
(MFT_POSN1_CD = 'a') AND (DATEDIFF(day, FLG_CDT, GETDATE()) > 250) AND (AGED_ALIEN_RSW = 'y') AND (DATEDIFF(day, FLG_CDT, GETDATE()) < 299)
GROUP BY fo
)b
on a.fo = b.fo

It gave me this which is what I want.


FO     Totpenfodds  Pend250
B19	397	       3
704	341	       1
317	210	       1
399	643	       10
521	140	       2
856	1713	       2
918	587	       1
293	643	       9
887	1380	      53
913	2153	       6
A33	402	      13
743	382	       3

When I tried to add the other fields (Pend300, Pend351, and Pend400 I am getting incorrect syntax near 'FO' referring to the last line.

Select a.fo, Totpenfodds, Pend250, Pend300, Pend351, Pend400
From
(SELECT fo, count(cossn) AS [TotPenFODDS]
FROM T16pendall
WHERE MFT_POSN1_CD='b' OR MFT_POSN1_CD='d' OR (MFT_POSN1_CD= 'a' AND
AGED_ALIEN_RSW = 'y')
GROUP BY fo
)a
inner join
(SELECT fo, count(cossn) AS [Pend250]
FROM T16pendall
WHERE (MFT_POSN1_CD = 'b' OR
MFT_POSN1_CD = 'd') AND (DATEDIFF(day, FLG_CDT, GETDATE()) > 250) AND (DATEDIFF(day, FLG_CDT, GETDATE()) < 299) OR
(MFT_POSN1_CD = 'a') AND (DATEDIFF(day, FLG_CDT, GETDATE()) > 250) AND (AGED_ALIEN_RSW = 'y') AND (DATEDIFF(day, FLG_CDT, GETDATE()) < 299)
GROUP BY fo
)b
on a.fo = b.fo
inner join
(SELECT fo, count(cossn) AS [Pend300]
FROM T16pendall
WHERE (MFT_POSN1_CD = 'b' OR
MFT_POSN1_CD = 'd') AND (DATEDIFF(day, FLG_CDT, GETDATE()) > 300) AND (DATEDIFF(day, FLG_CDT, GETDATE()) < 350) OR
(MFT_POSN1_CD = 'a') AND (DATEDIFF(day, FLG_CDT, GETDATE()) > 300) AND (AGED_ALIEN_RSW = 'y') AND (DATEDIFF(day, FLG_CDT, GETDATE()) < 350)

GROUP BY fo
)c
inner join
(SELECT fo, count(cossn) AS [Pend351]
FROM T16pendall
WHERE (MFT_POSN1_CD = 'b' OR
MFT_POSN1_CD = 'd') AND (DATEDIFF(day, FLG_CDT, GETDATE()) > 351) AND (DATEDIFF(day, FLG_CDT, GETDATE()) < 400) OR
(MFT_POSN1_CD = 'a') AND (DATEDIFF(day, FLG_CDT, GETDATE()) > 351) AND (AGED_ALIEN_RSW = 'y') AND (DATEDIFF(day, FLG_CDT, GETDATE()) < 400)
GROUP BY fo
)d
on c.FO = d.FO
inner join
(SELECT fo, count(cossn) Pend400
FROM T16pendall
WHERE (MFT_POSN1_CD = 'b' OR
MFT_POSN1_CD = 'd') AND (DATEDIFF(day, FLG_CDT, GETDATE()) > 400) OR
(MFT_POSN1_CD = 'a') AND (DATEDIFF(day, FLG_CDT, GETDATE()) > 400) AND (AGED_ALIEN_RSW = 'y')
GROUP BY fo
)e
on c.FO = d.FO

What am I doing wrong?

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-10-18 : 15:31:21

Thanks jcelko. I tried that but I got incorrect syntax near keyword 'Then'

I do like your format as it is cleaner.

Please help

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-18 : 15:31:50

You are missing an ON condition for the second inner join. And your last one doesn't appear to be using the right aliases. Your last derived table is aliased as E and yet you aren't using it in the ON.

Please post your queries with formatting such as indentation. You can preserve formatting by adding code tags when you post it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-10-18 : 15:40:14

THANK YOU TARA that worked!!!

Is this way okay to indent?


Select a.fo, Totpenfodds, Pend250, Pend300, Pend351, Pend400
From
  (SELECT fo, count(cossn) AS [TotPenFODDS]
    FROM  T16pendall
    WHERE MFT_POSN1_CD='b' OR MFT_POSN1_CD='d' OR 
   (MFT_POSN1_CD= 'a'  AND AGED_ALIEN_RSW = 'y')
   GROUP BY fo
   )a
inner join

    (SELECT fo, count(cossn) AS [Pend250]
    FROM T16pendall
    WHERE (MFT_POSN1_CD = 'b' OR MFT_POSN1_CD = 'd') AND (DATEDIFF
    (day, FLG_CDT, GETDATE()) > 250) AND (DATEDIFF(day, FLG_CDT,
    GETDATE()) < 299) OR (MFT_POSN1_CD = 'a') AND (DATEDIFF(day,
    FLG_CDT, GETDATE()) > 250) AND (AGED_ALIEN_RSW = 'y') AND 
   (DATEDIFF(day, FLG_CDT, GETDATE()) < 299)
    GROUP BY fo
    )b
on a.fo = b.fo

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-18 : 15:41:45

Yes that's how you preserve the formatting. It makes it much easier for us to scan large queries like yours.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-10-18 : 18:21:17

okay thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources