I have created a script that returns every column and row that is queried
and some of the fields are blank. I want to only return the fields that are
populated. Below is the script. Any advice would be appreciated:
SELECT Relation.xparent_prov_id,
Relation.Parent,
Provider.DataSource_ID as prov_datasource_ID,
Provider.Provider_Name,
Provider.Provider_Type,
Provider.Degree_Type,
FEIProviderStatus.Status,
Provider.DataSource_ID,
Provider.City,
Provider.State,
evClinician_Profile.arabic
CASE
WHEN evClinician_Profile.arabic = 'Y' THEN 'Arabic'
ELSE ''
END AS Arabic,
CASE
WHEN evClinician_Profile.chinese = 'Y' THEN 'Chinese'
ELSE ''
END AS Chinese,
CASE
WHEN evClinician_Profile.french = 'Y' THEN 'French'
ELSE ''
END AS French,
CASE
WHEN evClinician_Profile.german = 'Y' THEN 'German'
ELSE ''
END AS German,
CASE
WHEN evClinician_Profile.hebrew = 'Y' THEN 'Hebrew'
ELSE ''
END AS Hebrew,
CASE
WHEN evClinician_Profile.italian = 'Y' THEN 'Italian'
ELSE ''
END AS Italian,
CASE
WHEN evClinician_Profile.japanese ='Y' THEN 'Japanese'
ELSE ''
END AS Japanese,
CASE
WHEN evClinician_Profile.russian = 'Y' THEN 'Russian'
ELSE ''
END AS Russian,
CASE
WHEN evClinician_Profile.spanish = 'Y' THEN 'Spanish'
ELSE ''
END AS Spanish
INTO #TEMP
FROM Provider
INNER JOIN Relation ON Provider.DataSource_ID = Relation.datasource_id
INNER JOIN evClinician_Profile ON Provider.Provider_Key =
evClinician_Profile.RelMan_Key
INNER JOIN FEIProviderStatus ON Provider.DataSource_ID =
FEIProviderStatus.ProviderID
SELECT * FROM #TEMP
DROP TABLE #TEMP
Message posted via http://www.webservertalk.comAs a general suggestion, you can use a WHERE clause in your query like:
WHERE '' NOT IN ( Arabic, Chinese, ... Spanish )
Anith|||What if one of the fields is blank and others are populated, do you want to
reject the whole row because of this?
If so, specify a WHERE clause as mentioned in an earlier reply.
Ilyan Mishiyev
IGM Consulting Corporation
Enterprise Web Solutions
www.igmcc.com
"Jay via webservertalk.com" <forum@.nospam.webservertalk.com> wrote in message
news:c3177033e42f430f85a389ecc7a323f6@.SQ
webservertalk.com...
>I have created a script that returns every column and row that is queried
> and some of the fields are blank. I want to only return the fields that
> are
> populated. Below is the script. Any advice would be appreciated:
> SELECT Relation.xparent_prov_id,
> Relation.Parent,
> Provider.DataSource_ID as prov_datasource_ID,
> Provider.Provider_Name,
> Provider.Provider_Type,
> Provider.Degree_Type,
> FEIProviderStatus.Status,
> Provider.DataSource_ID,
> Provider.City,
> Provider.State,
> evClinician_Profile.arabic
> CASE
> WHEN evClinician_Profile.arabic = 'Y' THEN 'Arabic'
> ELSE ''
> END AS Arabic,
> CASE
> WHEN evClinician_Profile.chinese = 'Y' THEN 'Chinese'
> ELSE ''
> END AS Chinese,
> CASE
> WHEN evClinician_Profile.french = 'Y' THEN 'French'
> ELSE ''
> END AS French,
> CASE
> WHEN evClinician_Profile.german = 'Y' THEN 'German'
> ELSE ''
> END AS German,
> CASE
> WHEN evClinician_Profile.hebrew = 'Y' THEN 'Hebrew'
> ELSE ''
> END AS Hebrew,
> CASE
> WHEN evClinician_Profile.italian = 'Y' THEN 'Italian'
> ELSE ''
> END AS Italian,
> CASE
> WHEN evClinician_Profile.japanese ='Y' THEN 'Japanese'
> ELSE ''
> END AS Japanese,
> CASE
> WHEN evClinician_Profile.russian = 'Y' THEN 'Russian'
> ELSE ''
> END AS Russian,
> CASE
> WHEN evClinician_Profile.spanish = 'Y' THEN 'Spanish'
> ELSE ''
> END AS Spanish
> INTO #TEMP
> FROM Provider
> INNER JOIN Relation ON Provider.DataSource_ID = Relation.datasource_id
> INNER JOIN evClinician_Profile ON Provider.Provider_Key =
> evClinician_Profile.RelMan_Key
> INNER JOIN FEIProviderStatus ON Provider.DataSource_ID =
> FEIProviderStatus.ProviderID
> SELECT * FROM #TEMP
> DROP TABLE #TEMP
> --
> Message posted via http://www.webservertalk.com|||No, if the field is populated I want those to return those records.
Message posted via http://www.webservertalk.com|||That doesn't answer Ilyan's question. For example, given the following:
CREATE TABLE T1 (x INTEGER NOT NULL PRIMARY KEY, y CHAR(1) NULL, z
CHAR(1) NULL)
INSERT INTO T1 VALUES (1,'A',NULL)
INSERT INTO T1 VALUES (2,NULL,'B')
INSERT INTO T1 VALUES (3,'A','B')
You could exclude rows where either Y or Z is NULL:
SELECT x,y,z
FROM T1
WHERE y IS NOT NULL
AND z IS NOT NULL
or only where BOTH are NULL
SELECT x,y,z
FROM T1
WHERE y IS NOT NULL
OR z IS NOT NULL
You said you wanted to return "fields that are populated". Does that
mean you want to see a different number of columns depending on what
data exists? You'll have to do that either client-side, or with Dynamic
SQL or using a set of IF statements to cover all the various cases. A
static query always returns the same number of columns - it can't be
changed at runtime.
David Portas
SQL Server MVP
--|||I think I solved the issue by using:
WHERE LEN (fieldname) < 0
I did this code for each of the fields that I was querying in the sp. Is
this the most effeicient way to do this?
Message posted via http://www.webservertalk.com|||This achieves nothing except exclude the rows where fieldname is NULL
so you might as well write:
WHERE fieldname IS NOT NULL
Notice that NULL is not the same as an empty string. If you want to
exclude empty strings as well then you can do:
WHERE fieldname > ''
David Portas
SQL Server MVP
--|||You're right I got the same number of rows. Thanks for the feedback.
Message posted via http://www.webservertalk.com
