Does anybody know whats wrong with this query?

[jjlbmcnews]

Hi,

I'm trying to run this query but it keeps complaining about
Line 90 incorrect syntax near 'IN'. I can't see whats wrong, can anybody else?

CREATE PROCEDURE [dbo].[HgGetDistributionListjjl] (@Date varchar(11), @Sector varchar(8000),
@Time varchar(200), @Country int, @Language int, @PubType VARCHAR(1000)) AS

IF @Country = 0 SET @Country = NULL
IF @Language = 0 SET @Language = NULL
IF @PubType = '' SET @PubType = NULL

/*
Create the Main SQL SELECT Statement
*/
SELECT
p.iProfileID as BriefID,
p.chProfileKey as BriefCode,
p.vchName as BriefName,
c.vchName as ClientName,
SectorName =
CASE
WHEN l.vchSector IS NULL THEN c.vchSector
ELSE l.vchSector
END,
r.bSortRequired as SortRequired,
r.bSummaryRequired as SummaryRequired,
MIN(SUBSTRING(pd.vchTime,28,5)) as MorningTime,
MIN(SUBSTRING(pd.vchTime,40,5)) as LunchTime,
MIN(SUBSTRING(pd.vchTime,46,5)) as AfternoonTime,
MIN(SUBSTRING(pd.vchTime,28,5)) + ' ' + MIN(SUBSTRING(pd.vchTime,40,5)) + ' ' +
MIN(SUBSTRING(pd.vchTime,46,5)) as Time,
SUM(pd.bNoCuts) as NoCuts,
s.Status,
s.Num
FROM
[dbo].[Profile] p
INNER JOIN
[dbo].[Client] c ON p.iClientID = c.iClientID
INNER JOIN
[dbo].[ProfileDist] pd ON pd.iProfileID = p.iProfileID
LEFT JOIN
[dbo].[Processing] r ON r.iProcessingID = p.iProcessingID
LEFT JOIN
[dbo].[Logo] l ON l.iLogoID = p.iLogoID
LEFT JOIN
(
/*
Standard Status Count for REJECTED, '', SORTED, DISTRIBUTED articles
*/
SELECT Account AS 'BriefCode', Custom1 AS 'Status', COUNT(*) AS 'Num'
FROM [dbo].[Distribution] AS D
LEFT JOIN [dbo].[ReadingSource] AS R ON D.PubID = R.iReadingSourceID
WHERE (Custom4 = ' ' OR Custom4 like @Date + '%')
AND r.iCountryID = COALESCE(@Country, r.iCountryID)
AND r.iLanguageID = COALESCE(@Language, r.iLanguageID)
AND r.iTypeID IN (COALESCE(@PubType, r.iTypeID))
GROUP BY Account, Custom1
/*
Sorting Status Count for ACCEPTED articles awaiting Sorting
*/
UNION
SELECT Account as 'BriefCode', 'TOSORT' as Status, COUNT(*) as Num
FROM [dbo].[Distribution] d
LEFT JOIN ReadingSource r ON d.PubID = r.iReadingSourceID
WHERE Custom1 = 'ACCEPTED' AND (Custom2 = '' OR Custom2 IS NULL)
AND r.iCountryID = COALESCE(@Country, r.iCountryID)
AND r.iLanguageID = COALESCE(@Language, r.iLanguageID)
AND r.iTypeID IN (COALESCE(@PubType, r.iTypeID))
GROUP BY Account, Custom1
/*
Summary Status Count for ACCEPTED articles awaiting Summarisation
*/
UNION
SELECT Account as 'BriefCode', 'TOSUM' as Status, COUNT(*) as Num
FROM [dbo].[Distribution] d
LEFT JOIN [dbo].[Summary] s ON s.iSummaryID = d.SummaryID
LEFT JOIN [dbo].[ClientSummary] c ON c.ID = d.ID
LEFT JOIN [dbo].[Profile] p ON p.chProfileKey = d.Account
LEFT JOIN [dbo].[Processing] o ON p.iProcessingID = o.iProcessingID
LEFT JOIN [dbo].[ReadingSource] r ON d.PubCode = r.vchSourceCode
WHERE Custom1 = 'ACCEPTED' AND
((s.txSummary1 like '' OR s.txSummary1 IS NULL) AND
(c.txSummary1 like '' OR c.txSummary1 IS NULL)) AND
(Custom2 = Path + FName OR Custom2 = '' OR Custom2 IS NULL) AND
((r.iRestricted=1 AND o.bSummaryRestrict=1) OR (o.bSummaryRestrict=0)) AND
((r.iCountryID = 1 AND o.iSummaryUKIntl >= 0) OR (r.iCountryID <> 1 AND o.iSummaryUKIntl <= 0)) AND
o.iSummaryOptions > 0
AND r.iCountryID = COALESCE(@Country, r.iCountryID)
AND r.iLanguageID = COALESCE(@Language, r.iLanguageID)
AND r.iTypeID IN (COALESCE(@PubType, r.iTypeID))
GROUP BY Account, Custom1) S
ON s.BriefCode = p.chProfileKey
WHERE p.iStatusID = 1
AND (pd.vchTime = CASE WHEN @Time <> '' OR LEFT(@Time, 1) <> '=' THEN SUBSTRING(pd.vchTime,28,2) IN(@Time)
OR SUBSTRING(pd.vchTime,40,2) IN(@Time) OR SUBSTRING(pd.vchTime,46,2) IN(@Time)
ELSE
WHEN @Time = '=Morning'
SUBSTRING(pd.vchTime,28,5) BETWEEN '00:01' AND '10:59'
WHEN @Time = '=Lunchtime'
SUBSTRING(pd.vchTime,40,5) BETWEEN '11:00' AND '14:59'
WHEN @Time = '=Evening'
SUBSTRING(pd.vchTime,46,5) BETWEEN '15:00' AND '23:59')
AND (@Sector = CASE WHEN l.vchSector IS NULL THEN c.vchSector ELSE l.vchSector IN (@Sector))
GROUP BY
p.iProfileID,
p.chProfileKey,
p.vchName,
c.vchName,
c.vchSector,
l.vchSector,
r.bSortRequired,
r.bSummaryRequired,
s.Status,
s.Num
ORDER BY
p.chProfileKey,
s.Status
GO

 

[nigelrivett]

AND (@Sector = CASE WHEN l.vchSector IS NULL THEN c.vchSector ELSE l.vchSector IN (@Sector))


Not even sure what this is trying to do

AND (CASE WHEN l.vchSector IS NULL THEN c.vchSector ELSE l.vchSector end IN (@Sector))

But this would still be wrong

maybe
AND (',' + @Sector + ',' like '%,' + convert(varchar(20),CASE WHEN l.vchSector IS NULL THEN c.vchSector ELSE l.vchSector end + ',%')

This will look in a comma delimitted list of entries in @Sector



======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[jjlbmcnews]

It can't be that line as when I take the @Time section out the @Sector section works fine, its got something to do with the @Time section but I don't know what.

 

[SQLBill]

Try taking out the close parenthesis in this line:

SUBSTRING(pd.vchTime,46,5) BETWEEN '15:00' AND '23:59')

That very last close parenthesis doesn't seem to have a matching open parenthesis.

-SQLBill

 

[SQLBill]

Another thing to change is this:

AND (@Sector = CASE WHEN l.vchSector IS NULL THEN c.vchSector ELSE l.vchSector IN (@Sector))

to

AND (@Sector = (CASE WHEN l.vchSector IS NULL THEN c.vchSector ELSE l.vchSector IN (@Sector)))

Since you are trying to assign a variable (@Sector), it might be treating the CASE expression as a STRING and trying to assign the whole CASE expression to the variable instead of assigning the result of the expression.

-SQLBill

 

[jjlbmcnews]

That doesn't work, I still get the same error.