Don't like ads? PRO users don't see any ads ;-)
Public Pastes
Guest

Untitled

By: a guest on May 9th, 2012  |  syntax: None  |  size: 1.31 KB  |  hits: 26  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. What is the SQL Server equivalent of this Oracle query? [closed]
  2. SELECT
  3.     TEAM_ID,
  4.     LEVEL_ID,
  5.     FF_ID,
  6.     MODULE_ID,
  7.     TERR_ID,
  8.     MERGE_KEY,
  9.     count(distinct TERR_ID) over (partition by TEAM_ID,LEVEL_ID,FF_ID,MODULE_ID)
  10.     || ' of ' ||
  11.     count(distinct TERR_ID) over (partition by TEAM_ID,LEVEL_ID,FF_ID)
  12.     as MISMATCH_TERR_COUNT_IN_FF
  13. FROM SCN7BBFE80210E04E2F88653A.PA83FB9BD57E044618B7AC86A;
  14.        
  15. insert into PA83FB9BD57E044618B7AC86A values('3',1,'YH','PERF','3XBDA3',1)        
  16. insert into PA83FB9BD57E044618B7AC86A values('3',1,'YH','PERF','3XAJA3',1)
  17.        
  18. SELECT
  19.     TEAM_ID,
  20.     LEVEL_ID,
  21.     FF_ID,
  22.     MODULE_ID,
  23.     TERR_ID,
  24.     MERGE_KEY,
  25.     ISNULL(CAST(count(distinct TERR_ID) OVER(PARTITION BY TEAM_ID, LEVEL_ID, FF_ID, MODULE_ID) AS nvarchar(max)), '')
  26.     + ' of ' +
  27.     ISNULL(CAST(count(distinct TERR_ID) OVER(PARTITION BY TEAM_ID, LEVEL_ID, FF_ID) AS nvarchar(max)), '')
  28.     AS MISMATCH_TERR_COUNT_IN_FF
  29. FROM dbo.PA83FB9BD57E044618B7AC86A
  30.        
  31. ERROR:Incorrect syntax near 'distinct'.
  32.        
  33. SELECT
  34.     TEAM_ID,
  35.     LEVEL_ID,
  36.     FF_ID,
  37.     MODULE_ID,
  38.     TERR_ID,
  39.     MERGE_KEY,
  40.     cast(count(distinct TERR_ID) OVER(PARTITION BY TEAM_ID, LEVEL_ID, FF_ID, MODULE_ID)
  41.     AS MISMATCH_TERR_COUNT_IN_FF
  42. FROM dbo.PA83FB9BD57E044618B7AC86A
  43.        
  44. ERROR:Incorrect syntax near 'distinct'
create a new version of this paste RAW Paste Data