Query syntax - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Query syntax

Author

Topic

Magua1
Starting Member

15 Posts

Posted - 2014-12-03 : 07:18:56

Hi

The code below works as intended i.e.get the dup records with 2 matching fields:
SELECT s.ssd_scjc, s.ssd_sces, qty
FROM sits..srs_ssd_dqi s
INNER JOIN (SELECT ssd_scjc, ssd_sces, COUNT(*) AS qty
FROM sits..srs_ssd_dqi s
GROUP BY ssd_scjc, ssd_sces
HAVING COUNT(*) > 1) AS Dups
ON s.ssd_scjc = Dups.ssd_scjc and s.ssd_sces = Dups.ssd_sces

However, when I use it as part of a larger query, I get
'Msg 156, Level 15, State 1, Line 25
Incorrect syntax near the keyword 'WHERE'.'

I just can't see what's wrong!

Larger query below:
SELECT DISTINCT
Y.Audit_Year AS Report_Year,
'6012' AS Report_ID,
ISNULL(srs_sce.sce_scjc,'') AS Student_Code,
ISNULL(srs_sce.sce_seq2,'') AS SCE_Sequence,
'Current doctoral students where there are ' + LTRIM(STR(Qty)) + ' SSD records per SCE sequence number.'
AS field_value

FROM
sits..srs_sce INNER JOIN sits..srs_crs ON srs_sce.sce_crsc = srs_crs.crs_code

INNER JOIN staging..DQI_Audit_Years as Y on Y.Audit_Year = srs_sce.sce_ayrc
INNER JOIN

--******* Get the dups
(SELECT s.ssd_scjc, s.ssd_sces, qty
FROM sits..srs_ssd_dqi s
JOIN (SELECT t.ssd_scjc, t.ssd_sces, COUNT(*) AS Qty
FROM sits..srs_ssd_dqi t
GROUP BY t.ssd_scjc, t.ssd_sces
HAVING COUNT(*) > 1) AS Dups
ON t.ssd_scjc = Dups.ssd_scjc and t.ssd_sces = Dups.ssd_sces)
WHERE

srs_sce.sce_stac NOT IN ('IV', 'N') AND -- SCE Status
LEFT(srs_sce.sce_crsc, 2) IN('10', '11', '12')

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-03 : 09:20:45

When you format the code so you can actually read it:


SELECT DISTINCT Y.Audit_Year AS Report_Year
     , '6012' AS Report_ID
     , ISNULL(srs_sce.sce_scjc, '') AS Student_Code
     , ISNULL(srs_sce.sce_seq2, '') AS SCE_Sequence
     , 'Current doctoral students where there are ' + LTRIM(STR(Qty)) + 
     ' SSD records per SCE sequence number.' AS field_value
FROM sits..srs_sce
INNER JOIN sits..srs_crs ON srs_sce.sce_crsc = srs_crs.crs_code
INNER JOIN staging..DQI_Audit_Years AS Y ON Y.Audit_Year = srs_sce.sce_ayrc
INNER JOIN
     --******* Get the dups 
     (
     SELECT s.ssd_scjc
          , s.ssd_sces
          , qty
     FROM sits..srs_ssd_dqi s
     INNER JOIN (
          SELECT t.ssd_scjc
               , t.ssd_sces
               , COUNT(*) AS Qty
          FROM sits..srs_ssd_dqi t
          GROUP BY t.ssd_scjc
               , t.ssd_sces
          HAVING COUNT(*) > 1
          ) AS Dups ON t.ssd_scjc = Dups.ssd_scjc
          AND t.ssd_sces = Dups.ssd_sces
     )

-- missing alias for the subquery and missing ON clause

WHERE srs_sce.sce_stac NOT IN ('IV', 'N')
     AND -- SCE Status
     LEFT(srs_sce.sce_crsc, 2) IN ('10', '11', '12')

the error is obvious.

Magua1
Starting Member

15 Posts

Posted - 2014-12-03 : 10:15:08

Thanks gbritton

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources