November 3, 2005 at 9:41 am
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
November 3, 2005 at 12:55 pm
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
November 3, 2005 at 12:59 pm
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
November 3, 2005 at 1:08 pm
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)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply