Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Burniske
Starting Member
4 Posts |
 Posted - 2014-09-19 : 12:55:09
|
| I have a query that pulls a list of organizations. I'm trying to use a CASE statement to add a column of names based on the relationship of the individual to that organization. It is possible for two people at the organization to both have that relationship to the organization. Currently, when I pull my results, I get all the individual's names for all the organizations, but I'm also getting a row where there is no name, something like:Company1 ''Company1 'Individual1'Company1 'Individual2'Some of the organizations, they have no one with the relationship, but for all the organizations that do have someone in the position I want to not see the blank row. My case statement is:CASE WHEN EXISTS (Select ind_customer.cst_key WHERE ixo_rlt_code = 'Relationship' ) THEN ind_customer.cst_ind_full_name_dn ELSE '' END AS 'FULL NAME'I feel like I'm missing something obvious. Any ideas? |
|
|
MichaelJSQL
Constraint Violating Yak Guru
252 Posts |
Posted - 2014-09-19 : 13:07:03
|
| You have a ixo_rlt_code that is not 'Relationship' and your else clause is being tripped: |
 |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-09-19 : 13:07:50
|
| the ELSE part of your CASE statement returns a blank (empty string) when the EXISTS clause returns FALSE. Obviously in your case this is exactly what is happening. That is, for the row with no name, (Select ind_customer.cst_key WHERE ixo_rlt_code = 'Relationship' ) returns no rows.Would you please post the whole query -- not just the CASE statement? |
|
|
|
Burniske
Starting Member
4 Posts |
Posted - 2014-09-19 : 14:44:04
|
| Here's the full query:SELECT DISTINCT org_cst_key,org_name,CASE WHEN EXISTS (Select ind_customer.cst_key WHERE ixo_rlt_code = 'Relationship' ) THEN ind_customer.cst_ind_full_name_dn ELSE '' END AS [ind_customer.cst_ind_full_name_dn]FROM co_organization (NOLOCK) JOIN co_organization_ext (NOLOCK) ON org_cst_key_ext=org_cst_key LEFT JOIN co_individual_x_organization (NOLOCK) ON ixo_org_cst_key=org_cst_key LEFT JOIN co_individual (NOLOCK) ON ind_cst_key=ixo_ind_cst_key LEFT JOIN co_customer ind_customer (NOLOCK) ON ind_cst_key=ind_customer.cst_key WHERE org_ogt_code = 'SEA' AND org_delete_flag = '0'ORDER BY org_name |
|
|
|
Burniske
Starting Member
4 Posts |
Posted - 2014-09-19 : 14:47:34
|
| With the ELSE clause I'm just trying to cover what happens for an organization that doesn't have anyone with ixo_rlt_code = 'Relationship' |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-09-19 : 14:54:27
|
| OK, well that's what's happening in your case. |
|
|
|
Burniske
Starting Member
4 Posts |
Posted - 2014-09-19 : 15:24:01
|
| But how do I keep the else statement from also giving me a blank row if there is someone? So my results look like:Company1 ''Company2 ''Company2 'Name'Company2 'Name'Company3 ''Company3 'Name'I want it to show:Company1 ''Company2 'Name'Company2 'Name'Company3 'Name' |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-09-19 : 16:48:20
|
| You might need more conditions in your join predicates. |
|
|
|
|
|
|
|
|
Case statement returning blank results