Fill View based on null criteria?

• Todd McDonald

  SSC Enthusiast

  Points: 160

  More actions

  November 3, 2005 at 9:41 am

  #188248

  I'm finally moving up from Access to SQL Server. I'm very new to this environment, so I could use some help.

  I have the following table: (very basic description)

  ID | NAME_EN | NAME_FR

  Each NAME field will contain data or NULLs.

  I need to build two views (or stored procedures?), one for all english names and one for all french names.

  The trick is that in each of these views, I need to substitute the opposite language if a null is encountered.

  Any thoughts? Ideas?

  Cheers.

  -Todd

  -- McWare

• Ray M

  SSC-Insane

  Points: 21093

  More actions

  November 3, 2005 at 12:55 pm

  #602222

  Please provide more information about your data. But I think this will work.

  Create view my_EN_view

  as

  select ID, Case when Name_EN is null then name_FR else NAME_EN  end as NAME_EN

  From MyNameTable

  Create view my_FR_view

  as

  select ID, Case when Name_FR is null then NAME_EN else NAME_FR  end as NAME_FR

  From MyNameTable

• Todd McDonald

  SSC Enthusiast

  Points: 160

  More actions

  November 3, 2005 at 12:59 pm

  #602225

  Thanks, I'll give that a try.

  The data is just two fields of varchars, but the site it servers has to be bi-lingual. So each organization name

  must be listed in both english and french. Some times there won't be a french equivalent, so the english needs

  to be substituted, and vice versa.

  Thanks again for the quick reply.

  Cheers.

  -Todd

  -- McWare

• rsharma

  SSCarpal Tunnel

  Points: 4865

  More actions

  November 3, 2005 at 1:08 pm

  #602228

  You can also use COALESCE() function.

  CREATE TABLE TESTCASE (ID INT IDENTITY(1,1), NAME_EN VARCHAR(10), NAME_FR VARCHAR(10))

  GO

  INSERT INTO TESTCASE VALUES ('ENG', NULL)

  INSERT INTO TESTCASE VALUES (NULL, 'FRENCH')

  INSERT INTO TESTCASE VALUES ('ENG', 'FRENCH')

  GO

  SELECT * FROM TESTCASE

  --Output

  ID          NAME_EN    NAME_FR   

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

  1           ENG        NULL

  2           NULL       FRENCH

  3           ENG        FRENCH

  (3 row(s) affected)

  --Data for English

  SELECT COALESCE(NAME_EN, NAME_FR) SQL_WHEN_ENG, *

  FROM TESTCASE

  --Output

  SQL_WHEN_ENG ID          NAME_EN    NAME_FR   

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

  ENG          1           ENG        NULL

  FRENCH       2           NULL       FRENCH

  ENG          3           ENG        FRENCH

  (3 row(s) affected)

  --Data for French

  SELECT COALESCE(NAME_FR, NAME_EN) SQL_WHEN_FRENCH, *

  FROM TESTCASE

  --Output

  SQL_WHEN_FRENCH ID          NAME_EN    NAME_FR   

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

  ENG             1           ENG        NULL

  FRENCH          2           NULL       FRENCH

  FRENCH          3           ENG        FRENCH

  (3 row(s) affected)