CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user3766577
user3766577

Reputation: 11

Incorrect syntax near the keyword 'FROM' in my query

SELECT dbo.ScrCalcr$.AgingDays, dbo.ScrCalcr$.EndingCount, dbo.ScrCalcr$.Priority, dbo.ScrCalcr$.TPDUNS, Score=(
CASE 
  WHEN dbo.ScrCalcr$.AgingDays >= 150 THEN 
    CASE 
    WHEN dbo.ScrCalcr$.EndingCount >= 150 THEN 
    ((10 * dbo.ScrCalcr$.EndingCount) + (15 * dbo.ScrCalcr$.AgingDays))/ (dbo.ScrCalcr$.Priority * 0.1) * 1 
    ELSE((10 * dbo.ScrCalcr$.EndingCount) + (15 * dbo.ScrCalcr$.AgingDays))/ (dbo.ScrCalcr$.Priority * 0.1) * 0.1 
    END 
  ELSE 0 

END
FROM dbo.ScrCalcr$
ORDER BY Score DESC
GO

I am getting an error as:

Msg 156, Level 15, State 1, Line 12

Incorrect syntax near the keyword 'FROM'.

Upvotes: 0

Views: 2505

Answers (4)

Vinoth_S
Vinoth_S

Reputation: 1540

You Give ) parenthesis After END Statement like this

SELECT dbo.ScrCalcr$.AgingDays,
 dbo.ScrCalcr$.EndingCount
 , dbo.ScrCalcr$.Priority, 
 dbo.ScrCalcr$.TPDUNS, 
 Score=
 (
CASE 
  WHEN dbo.ScrCalcr$.AgingDays >= 150 THEN 
    CASE 
    WHEN dbo.ScrCalcr$.EndingCount >= 150 THEN 
    ((10 * dbo.ScrCalcr$.EndingCount) + (15 * dbo.ScrCalcr$.AgingDays))/ (dbo.ScrCalcr$.Priority * 0.1) * 1 
    ELSE((10 * dbo.ScrCalcr$.EndingCount) + (15 * dbo.ScrCalcr$.AgingDays))/ (dbo.ScrCalcr$.Priority * 0.1) * 0.1 
    END 
  ELSE 0 

END)
FROM dbo.ScrCalcr$
ORDER BY Score DESC
GO

Upvotes: 0

Patrick Hofman
Patrick Hofman

Reputation: 157118

We don't have your data model, so I can't say for certain, but it seems you need something like this:

SELECT dbo.ScrCalcr$.AgingDays, dbo.ScrCalcr$.EndingCount, dbo.ScrCalcr$.Priority, dbo.ScrCalcr$.TPDUNS
FROM dbo.ScrCalcr$
WHERE Score=(
CASE 
  WHEN dbo.ScrCalcr$.AgingDays >= 150 THEN 
    CASE 
    WHEN dbo.ScrCalcr$.EndingCount >= 150 THEN 
    ((10 * dbo.ScrCalcr$.EndingCount) + (15 * dbo.ScrCalcr$.AgingDays))/ (dbo.ScrCalcr$.Priority * 0.1) * 1 
    ELSE((10 * dbo.ScrCalcr$.EndingCount) + (15 * dbo.ScrCalcr$.AgingDays))/ (dbo.ScrCalcr$.Priority * 0.1) * 0.1 
    END 
  ELSE 0 
END )
ORDER BY Score DESC

I moved the condition to the where clause and removed the line break. Also, added a ) after END.

If you meant Score to be a field in your result, use this:

SELECT dbo.ScrCalcr$.AgingDays, dbo.ScrCalcr$.EndingCount, dbo.ScrCalcr$.Priority, dbo.ScrCalcr$.TPDUNS
,      CASE 
       WHEN dbo.ScrCalcr$.AgingDays >= 150 THEN 
            CASE 
            WHEN dbo.ScrCalcr$.EndingCount >= 150 THEN 
            ((10 * dbo.ScrCalcr$.EndingCount) + (15 * dbo.ScrCalcr$.AgingDays))/ (dbo.ScrCalcr$.Priority * 0.1) * 1 
            ELSE((10 * dbo.ScrCalcr$.EndingCount) + (15 * dbo.ScrCalcr$.AgingDays))/ (dbo.ScrCalcr$.Priority * 0.1) * 0.1 
            END 
     ELSE 0 
     END
     Score
FROM dbo.ScrCalcr$
ORDER BY Score DESC

Upvotes: 1

Jithin Shaji
Jithin Shaji

Reputation: 6071

SELECT  S.AgingDays, 
        S.EndingCount, 
        S.Priority, 
        S.TPDUNS,
        CASE    WHEN S.AgingDays >= 150 THEN (
                CASE WHEN S.EndingCount >= 150 THEN ((10 * S.EndingCount) + (15 * S.AgingDays))/ (S.Priority * 0.1) * 1 
                ELSE((10 * S.EndingCount) + (15 * S.AgingDays))/ (S.Priority * 0.1) * 0.1 
        END)
        ELSE 0 END Score
FROM dbo.ScrCalcr$ S
ORDER BY Score DESC
GO

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460288

I'm missing the right paranthesis of: Score=(.

So this should work:

SELECT dbo.ScrCalcr$.Agingdays, 
       dbo.ScrCalcr$.Endingcount, 
       dbo.ScrCalcr$.Priority, 
       dbo.ScrCalcr$.Tpduns, 
       Score=( CASE 
                 WHEN dbo.ScrCalcr$.Agingdays >= 150 THEN 
                   CASE 
                     WHEN dbo.ScrCalcr$.Endingcount >= 150 THEN 
                     ( 
                     ( 10 * dbo.ScrCalcr$.Endingcount ) + ( 15 * 
                     dbo.ScrCalcr$.Agingdays ) ) / 
                     ( 
                     dbo.ScrCalcr$.Priority * 0.1 
                     ) 
                     * 1 
                     ELSE( ( 10 * dbo.ScrCalcr$.Endingcount ) + 
                           ( 15 * dbo.ScrCalcr$.Agingdays ) ) / ( 
                               dbo.ScrCalcr$.Priority * 0.1 ) * 0.1 
                   END 
                 ELSE 0 
               END ) 
FROM   dbo.ScrCalcr$ 
ORDER  BY Score DESC

Upvotes: 1

Related Questions