CODEWITHSUNDEEP
sql-serverdatabase
user1138669
user1138669

Reputation: 21

Unable to run analytical function in Sql Server

In Oracle it works well. Query for oracle is As Follows

SELECT TEAM_ID, LEVEL_ID, FF_ID, MODULE_ID, TERR_ID, MERGE_KEY, count(distinct TERR_ID) over (partition by TEAM_ID,LEVEL_ID,FF_ID,MODULE_ID)||' of '||count(distinct TERR_ID) over (partition by TEAM_ID,LEVEL_ID,FF_ID) as MISMATCH_TERR_COUNT_IN_FF FROM SCN7BBFE80210E04E2F88653A.PA83FB9BD57E044618B7AC86A;

But for SQL Server, I'm getting an error

I created table with columnname as TEAM_ID, LEVEL_ID, FF_ID, MODULE_ID, TERR_ID, MERGE_KEY then inserted values into the table ...

insert into PA83FB9BD57E044618B7AC86A values('3',1,'YH','PERF','3XBDA3',1)
insert into PA83FB9BD57E044618B7AC86A values('3',1,'YH','PERF','3XAJA3',1)

This is the SQL query ...

SELECT  TEAM_ID ,
        LEVEL_ID ,
        FF_ID ,
        MODULE_ID ,
        TERR_ID ,
        MERGE_KEY ,
        ISNULL(CAST(COUNT(DISTINCT TERR_ID) OVER ( PARTITION BY TEAM_ID,
                                 LEVEL_ID, FF_ID, MODULE_ID ) AS NVARCHAR(MAX)),
               '') + ' of '
        + ISNULL(CAST(COUNT(DISTINCT TERR_ID) OVER ( PARTITION BY TEAM_ID,
                                 LEVEL_ID, FF_ID ) AS NVARCHAR(MAX)),
                 '') AS MISMATCH_TERR_COUNT_IN_FF
FROM    dbo.PA83FB9BD57E044618B7AC86A

Above query when run give ERROR:Incorrect syntax near 'distinct'.

making Query small i tried 

SELECT TEAM_ID, LEVEL_ID, FF_ID, MODULE_ID, TERR_ID, MERGE_KEY, cast(count(distinct TERR_ID) OVER(PARTITION BY TEAM_ID, LEVEL_ID, FF_ID, MODULE_ID) AS MISMATCH_TERR_COUNT_IN_FF FROM dbo.PA83FB9BD57E044618B7AC86A

ERROR:Incorrect syntax near 'distinct'

i tried by removing Cast part as well but same error appear again n again

Can anybody help .......

Upvotes: 0

Views: 605

Answers (1)

Eric
Eric

Reputation: 95153

You can't use a count(distinct col) in an over clause. You'll have to do a subquery that does the count(distinct col) with an old-fashioned group by to do what you want to do.

Upvotes: 1

Related Questions