display other value if column is nothing !

• bernardchristophe

  Old Hand

  Points: 366

  More actions

  January 18, 2010 at 2:42 am

  #138274

  Hi,

  i'm a beginner with t sql (sql server 2005)

  i have two table :

  training (trainingId, nomFR, nomEN)

  course (courseId, trainingId, nomFR, nomEN)

  I want to display all training and course

  BUT

  if course.nomFR is null or empty i must to display the training.nomFR column !

  any idea ?

  i've try to use ISNULL() but sometimes the course.nomFR is not null but there is no data (len = 0)

  thanks

  christophe

• arun.sas

  SSChampion

  Points: 11831

  More actions

  January 18, 2010 at 3:14 am

  #1104835

  Hi,

  The table schema haven’t the null, so that it’s happened some time,

  Use the case statement to set this issue, like

  select a.CID,a.trainingId,

  (case when (a.nomFR = '') or (a.nomFR is null) then b.nomFR else a.nomFR end)nomFR,

  (case when (a.nomEN = '') or (a.nomEN is null) then b.nomEN else a.nomEN end)nomEN

  from

  course a

  inner join

  training b

  on

  a.trainingId = b.trainingId

• RamG

  Right there with Babe

  Points: 767

  More actions

  January 18, 2010 at 3:28 am

  #1104842

  Can u try this one!

  select

  a.courseId,

  a.trainingId,

  "nomFR" = case when coalesce(a.nomFR, '') = '' then b.nomFR else a.nomFR end,

  "nomEN" = case when coalesce(a.nomEN, '') = '' then b.nomEN else a.nomEN end

  from

  #course a

  inner join #training b on a.trainingId = b.trainingId

• arun.sas

  SSChampion

  Points: 11831

  More actions

  January 18, 2010 at 4:03 am

  #1104853

  Hi ram,

  This may also another way you to go by coalesces. However you got the mean to archive.

• bernardchristophe

  Old Hand

  Points: 366

  More actions

  January 19, 2010 at 4:27 am

  #1105321

  Hi,

  thanks for your time and sample !

  that exactly what i want !

  have a nice day

  christophe