-
Oct 15th, 2021, 08:55 AM
#1
Thread Starter
Fanatic Member
-
Oct 15th, 2021, 09:09 AM
#2
Re: SQL - Select two columns where not null - return one row
Is this specific to those two rows or do you want to be able to handle a more general case? I would think that you would just be able to group on the first column and get the max of the other two columns. I would expect that to get the text value from each column and exclude the NULL.
-
Oct 15th, 2021, 09:16 AM
#3
Thread Starter
Fanatic Member
Re: SQL - Select two columns where not null - return one row
It is a more general case where the values of those two columns are different than the ones we see here. Also in some cases there might be only one row to begin with
If it is very complicated, I could get this to a datatable and work it out on client side.
-
Oct 15th, 2021, 09:43 AM
#4
Re: SQL - Select two columns where not null - return one row
1. Remove AdditionalInfo and ListItemName from Group by items, AdditionalInfo, ListItemName so it become Group by items only.
2. Put MAX aggregate function in front of both ICroles and DVRoles expressions so these become MAX(case when ... end)
cheers,
</wqw>
-
Oct 15th, 2021, 09:45 AM
#5
Re: SQL - Select two columns where not null - return one row
Very simple when you look at it from a 30,000 foot view...
First of all - NULL is evil and hard to work with - I changed your query to have '' instead of null - see below:
Code:
SELECT Items As UserName, (case when AdditionalInfo = 'ICRelated' then ListItemName else '' end) as ICroles,
(case when AdditionalInfo Is Null then ListItemName else '' end) as DVRoles
FROM DV_Arena_ListItems
WHERE
ListItemType = 'Assigned Role' And
Items = 'MIAD' And
ActiveStatus = 'Yes'
Group by items, AdditionalInfo, ListItemName
Now here is the simple part.
What is the "set" you are working with? Answer - two rows.
What do you want from that "group" of two rows?
You want the value that is not blank - that would be the MAX() function that handles that!
Code:
SELECT Items As UserName, Max(case when AdditionalInfo = 'ICRelated' then ListItemName else '' end) as ICroles,
Max(case when AdditionalInfo Is Null then ListItemName else '' end) as DVRoles
FROM DV_Arena_ListItems
WHERE
ListItemType = 'Assigned Role' And
Items = 'MIAD' And
ActiveStatus = 'Yes'
Group by items
-
Oct 15th, 2021, 09:46 AM
#6
Re: SQL - Select two columns where not null - return one row
Originally Posted by wqweto
1. Remove AdditionalInfo and ListItemName from Group by items, AdditionalInfo, ListItemName so it become Group by items only.
2. Put MAX aggregate function in front of both ICroles and DVRoles expressions so these become MAX(case when ... end)
cheers,
</wqw>
Beat me to it by 2 minutes - but you shouldn't max that null...
-
Oct 15th, 2021, 09:48 AM
#7
Re: SQL - Select two columns where not null - return one row
Originally Posted by szlamany
. . . but you shouldn't max that null...
LOL! The warning is going to scare me? :-))
Btw, the bigger problem is with AVG, not MAX.
AVG on 1, 2, 3, NULL, 5 takes the sum and divides by 4 (ignoring the NULL) instead of 5 as some might expect.
cheers,
</wqw>
-
Oct 15th, 2021, 09:58 AM
#8
Re: SQL - Select two columns where not null - return one row
Originally Posted by wqweto
LOL! The warning is going to scare me? :-))
Btw, the bigger problem is with AVG, not MAX.
AVG on 1, 2, 3, NULL, 5 takes the sum and divides by 4 (ignoring the NULL) instead of 5 as some might expect.
cheers,
</wqw>
As I warned - NULL is evil and you don't want it in your AGGREGATE functions! Unexpected behavior especially to the novice.
I find that by default nature I always wrap column names in ISNULL() for all comparisons.
Code:
Case When IsNull(SomeYNField,'')='Y' Then 'Yes' Else 'No' End
And always use CASE ...ELSE... so that your CASE statements are always DETERMINISTIC!
"always DETERMINISTIC" - is that being redundant?
-
Oct 15th, 2021, 10:14 AM
#9
Thread Starter
Fanatic Member
Re: SQL - Select two columns where not null - return one row
That is fantastic. Thank you all so much; also for the explanation.
-
Oct 15th, 2021, 10:16 AM
#10
Re: SQL - Select two columns where not null - return one row
Btw, in Case When IsNull(SomeYNField,'')='Y' Then 'Yes' Else 'No' End the use of IsNull is completely redundant. The result of the whole expression cannot be NULL no matter the value (or lack of value) in SomeYNField column.
cheers,
</wqw>
-
Oct 15th, 2021, 02:11 PM
#11
Re: SQL - Select two columns where not null - return one row
Originally Posted by wqweto
Btw, in Case When IsNull(SomeYNField,'')='Y' Then 'Yes' Else 'No' End the use of IsNull is completely redundant. The result of the whole expression cannot be NULL no matter the value (or lack of value) in SomeYNField column.
cheers,
</wqw>
On the surface what you say is obviously true, but for maintainability and furture enhancements - someone could change that to <> instead of = and then you have big problems.
I always alias my SELECT's with 2 character "common" to that database aliases, even if only one TABLE. Adding JOINS to poorly alias'ed SELECT's is a nightmare
-
Oct 15th, 2021, 07:24 PM
#12
Re: SQL - Select two columns where not null - return one row
Originally Posted by Grand
It is a more general case where the values of those two columns are different than the ones we see here. Also in some cases there might be only one row to begin with
If it is very complicated, I could get this to a datatable and work it out on client side.
In future, explain what you actually want. Providing an example can help but specifying a single case without specifying the general case is a great way to waste everyone's time. There are going to be a number of ways to achieve a result in a specific case that won't work in the general case. ALWAYS describe the rules in their entirety, because that's what the code has to implement.
-
Oct 16th, 2021, 06:19 AM
#13
Re: SQL - Select two columns where not null - return one row
Originally Posted by szlamany
On the surface what you say is obviously true, but for maintainability and furture enhancements - someone could change that to <> instead of = and then you have big problems.
Sure, the same way someone might swap 'Yes' and 'No' and get themselves into trouble land.
Originally Posted by szlamany
I always alias my SELECT's with 2 character "common" to that database aliases, even if only one TABLE. Adding JOINS to poorly alias'ed SELECT's is a nightmare
Nice! We usually use 3 letter aliases with a separate 't' for temp tables i.e. lnk vs tlnk -- the second comes from a temp table.
We use tabsize=4 but use 3 tabs for nested level indentation and format SELECTs with comma-first on new line so that commenting out columns is easy.
We format our T-SQL consistently like every other "application" code (VB6 or C#) and never leave messy unformatted chunks. We have a lot (100s of thousands of LOC) of T-SQL code under source control like this.
cheers,
</wqw>
-
Oct 18th, 2021, 02:46 AM
#14
Re: SQL - Select two columns where not null - return one row
Sample Data taken from here: https://www.vbforums.com/showthread....onditions-true
tested in SQLite
Code:
SELECT DISTINCT T1.Items, T2.ICRoles, T3.DVRoles
FROM test AS T1
INNER JOIN
(SELECT DISTINCT Items, ItemName AS ICRoles FROM test WHERE Info='ICRelated') As T2
ON
T2.Items=T1.Items
INNER JOIN
(SELECT DISTINCT Items, ItemName AS DVRoles FROM test WHERE Info Is Null) AS T3
ON
T3.Items=T1.Items
WHERE
T1.Items='A'
Returns:
Items |
ICRoles |
DVRoles |
A |
Observer |
Admin |
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Oct 18th, 2021, 09:49 AM
#15
Re: SQL - Select two columns where not null - return one row
Originally Posted by wqweto
We format our T-SQL consistently like every other "application" code (VB6 or C#) and never leave messy unformatted chunks. We have a lot (100s of thousands of LOC) of T-SQL code under source control like this.
Very nice - the only way to do it, I agree!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|