case function with row count

• oracle_coorgi

  SSC-Addicted

  Points: 483

  More actions

  September 4, 2006 at 4:27 am

  #61328

  hi

  can any one help with

  case function with row count

  if row count is greater then 1 or >1 case1

  if row count is zero then case two

  ------this is my table and cloumn ---sample code

  DECLARE

  @rc tinyint ,

  @var varchar(50)

  set @var = (SELECT emd.tpa_id

  from dbo.EMPLOYER_DIVISION AS ED,EMPLOYEE_MISC_DATA AS EMD

  where emd.tpa_id=ed.tpa_id

  and

  emd.empe_key=ed.division_key)

  SELECT ed.tpa_id,

  ed.division_key,

  emd.tpa_id,

  emd.empe_key

  from dbo.EMPLOYER_DIVISION AS ED,EMPLOYEE_MISC_DATA AS EMD

• R M Buda

  SSCrazy

  Points: 2225

  More actions

  September 4, 2006 at 6:35 am

  #658289

  I dont quite understand the question.

  It may help if you give an example of your desired output.

  e.g. Do you want one row per division?

  On each row what do you want to display?

  You may want something like

  SELECT ed.division_key, CASE WHEN count(*) > 1

  THEN 'A' ELSE 'B'

  END

  FROM dbo.EMPLOYER_DIVISION AS ED

  LEFT JOIN EMPLOYEE_MISC_DATA AS EMD

  ON EMD.tpa_id=ED.tpa_id

  GROUP BY ed.division_key