Why not work please

• Ali.M.Habib

  SSC Eights!

  Points: 930

  More actions

  January 28, 2009 at 2:16 am

  #196484

  declare Dist cursor for

  select [name]from #DISTINCTC

  open Dist

  DECLARE @z nvarchar(500)

  --Declare @word nvarchar(200)

  declare @x nvarchar(120)

  declare @y nvarchar(120)

  fetch next from Dist into @x

  while @@fetch_status=0

  begin

  set @y = @x;

  print @y

  select count(distinct(cast(@y as nvarchar))) from GLIF

  fetch next from Dist into @x

  end

  close Dist

  Deallocate Dist

  drop table #DISTINCTC

  the out put of the select is just 1 for all column

  @y not return value in the select how canI solve the

  the result is

  name

  ------------------------------------------------------------------------------------------------------------------------

  KEY_1

  SOC

  BRCH

  FCY_CODE

  GL_CLASS_CODE

  DESCRIPTION

  POSTING_IND

  TRANS_DATE

  LCY_AMT

  FCY_AMT

  REVERSAL_CODE

  REVERSAL_DATE

  GLIF_REFERENCES

  SOURCE_APPLN

  PS_JOURNAL_ID

  PS_JOURNAL_NBR

  CNTL_CENTRE

  LCY_NPV_AMT

  FCY_NPV_AMT

  FIL01

  (20 row(s) affected)

  KEY_1

  -----------

  1

  (1 row(s) affected)

  SOC

  -----------

  1

  (1 row(s) affected)

  BRCH

  -----------

  1

  (1 row(s) affected)

  FCY_CODE

  -----------

  1

  (1 row(s) affected)

  GL_CLASS_CODE

  -----------

  1

  (1 row(s) affected)

  DESCRIPTION

  -----------

  1

  (1 row(s) affected)

  POSTING_IND

  -----------

  1

  (1 row(s) affected)

  TRANS_DATE

  -----------

  1

  (1 row(s) affected)

  LCY_AMT

  -----------

  1

  (1 row(s) affected)

  FCY_AMT

  -----------

  1

  (1 row(s) affected)

  REVERSAL_CODE

  -----------

  1

  (1 row(s) affected)

  REVERSAL_DATE

  -----------

  1

  (1 row(s) affected)

  GLIF_REFERENCES

  -----------

  1

  (1 row(s) affected)

  SOURCE_APPLN

  -----------

  1

  (1 row(s) affected)

  PS_JOURNAL_ID

  -----------

  1

  (1 row(s) affected)

  PS_JOURNAL_NBR

  -----------

  1

  (1 row(s) affected)

  CNTL_CENTRE

  -----------

  1

  (1 row(s) affected)

  LCY_NPV_AMT

  -----------

  1

  (1 row(s) affected)

  FCY_NPV_AMT

  -----------

  1

  (1 row(s) affected)

  FIL01

  -----------

  1

  (1 row(s) affected)

• Ken McKelvey

  SSCoach

  Points: 18843

  More actions

  January 28, 2009 at 3:28 am

  #933681

  Your question is not clear.

  To get decent answers you will need to post:

  1. test Data - missing.

  2. your query - done.

  3. your results - done.

  4. expected results - missing.

  You query seems to be doing exactly what you are telling it as there is

  no relation between the tables #DistinctC and GLIF.

  If the GLIF table contains a [name] column you might want something like:

  SELECT C.[Name]

  &nbsp&nbsp&nbsp&nbsp,COALESCE(D.Occurs, 0) AS Occurs

  FROM #DistinctC C

  &nbsp&nbsp&nbsp&nbspLEFT JOIN

  &nbsp&nbsp&nbsp&nbsp(

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspSELECT G.[name], COUNT(*) AS Occurs

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspFROM GLIF G

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspGROUP BY G.[name]

  &nbsp&nbsp&nbsp&nbsp) D

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspON C.[name] = D.[name]

• Grant Fritchey

  SSC Guru

  Points: 398886

  More actions

  January 28, 2009 at 4:31 am

  #933692

  It looks like you're trying to return a list and a set of counts. You can do this without a cursor:

  SELECT [name]

  ,COUNT([name])

  FROM #DISTINCTC

  GROUP BY [name]

  You might want to pick up a copy of Itzik Ben Gan's excellent book TSQL Fundamentals

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning