Return a Value with Select Sta. WHen table is EMPTY

• SSIS\SSRS\SSAS

  SSCertifiable

  Points: 6038

  More actions

  September 9, 2008 at 11:56 am

  #192448

  i am writing this SSIS and I need to pass a value to a varibale.

  SELECT Top (1) CASE ENCT_HDR_BATCH_ID

  WHEN '837P'THEN 'M'

  WHEN '837I'THEN 'M'

  WHEN '837D'THEN 'M'

  WHEN 'DRUG' THEN 'G'

  END AS Value

  FROM dbo.tblErrorReportsLoadingHDR

  But now if table is empty there is no value to return so i get error in my ssis.

  HOW CAN I ALTER THIS SELECT STATEMENT OR INCORPORATE ANY OTHER SO THAT IF TABLE is EMPTY Then I want to have value return as 'X' or any other..

  thanks

• GSquared

  SSC Guru

  Points: 260824

  More actions

  September 9, 2008 at 12:03 pm

  #869216

  You could do:

  SELECT Top (1) CASE ENCT_HDR_BATCH_ID

  WHEN '837P'THEN 'M'

  WHEN '837I'THEN 'M'

  WHEN '837D'THEN 'M'

  WHEN 'DRUG' THEN 'G'

  END AS Value

  FROM dbo.tblErrorReportsLoadingHDR

  union

  select 'E'

  where not exists

  (select *

  from dbo.tblErrorReportsLoadingHDR)

  If you have a Where clause on the top query, you'll need to add that to the Not Exits statement.

  For this, I used the value E, short for Error. You might need another value, that'll be up to you, of course.

  Will that do what you need?

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

• SSIS\SSRS\SSAS

  SSCertifiable

  Points: 6038

  More actions

  September 9, 2008 at 12:17 pm

  #869230

  there is no where clause , thanks....