October 12, 2006 at 11:10 am
If I remove the INNER JOIN (which I need), these statements run fine by themselves. When I try to join them, I get the following errors:
Server: Msg 156, Level 15, State 1, Line 29
Incorrect syntax near the keyword 'INNER'.
Server: Msg 156, Level 15, State 1, Line 33
Incorrect syntax near the keyword 'ORDER'
I'm trying to SELECT the TOP 1 account from the table QueueOrderBatch. Does anyone see anthing obvious? Thanks!
DECLARE @WorkQueue as int,
@UserCode as int,
@SpreadSelect as int,
@txtQueueOrder as int
SELECT @WorkQueue = 1
SELECT @UserCode = 1
SELECT @SpreadSelect = 1
SELECT @txtQueueOrder = 1
DECLARE @UseIt1 as varchar(100)
UPDATE _FinalFlat SET
Tagged = 1,
WorkStart = GETDATE(),
UserCode = @UserCode,
@UseIt1 = A.AcctNo
FROM _FinalFlat A
INNER JOIN QueueComboBox B
ON A.WorkQueue = B.ProgramKeyField
WHERE A.Tagged = 0
AND B.ProgramKeyField = @WorkQueue
AND (
(A.SpreadFile < CASE WHEN @SpreadSelect = 0 THEN 9 ELSE 0 END) OR
(A.SpreadFile = CASE WHEN @SpreadSelect <> 0 THEN @SpreadSelect ELSE 0 END)
)
INNER JOIN
(
SELECT TOP 1 AcctNo
FROM QueueOrderBatch
ORDER BY RiskId
) Z
ON A.AcctNo = Z.AcctNo
October 12, 2006 at 5:50 pm
Try this:
UPDATE _FinalFlat SET
Tagged = 1,
WorkStart = GETDATE(),
UserCode = @UserCode,
/*@UseIt1 = A.AcctNo */
FROM _FinalFlat A
INNER JOIN QueueComboBox B
ON A.WorkQueue = B.ProgramKeyField
INNER JOIN
(
SELECT TOP 1 AcctNo
FROM QueueOrderBatch
ORDER BY RiskId
) Z
ON A.AcctNo = Z.AcctNo
WHERE A.Tagged = 0
AND B.ProgramKeyField = @WorkQueue
AND (
(A.SpreadFile < CASE WHEN @SpreadSelect = 0 THEN 9 ELSE 0 END) OR
(A.SpreadFile = CASE WHEN @SpreadSelect <> 0 THEN @SpreadSelect ELSE 0 END)
)
October 13, 2006 at 8:25 am
Actually, the final INNER JOIN follows the WHERE clause, which is a syntax error.
Move:
INNER JOIN
(
SELECT TOP 1 AcctNo
FROM QueueOrderBatch
ORDER BY RiskId
) Z
ON A.AcctNo = Z.AcctNo
before the WHERE clause.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply