How to Select All Values and Hide Null Values in SQL

How to select all values and hide NULL values in SQL?

This is what you are looking for:

SELECT x, y, etc, CASE WHEN field IS NOT NULL THEN field ELSE '' END AS hehe FROM table;

Edit: Addin to your comments, this is pretty trivial once you know how to do it for one column. Apply the same for all columns. In SO dont expect homeworks to get done, instead expect a help to solve your problem which ultimately you yourself have to do.

Btw, here is how..

SELECT COALESCE(name, ''), COALESCE(kg, ''), COALESCE(pod, ''), COALESCE(reps, ''), 
       COALESCE(time, '') 
FROM   table

You have three good methods in this thread (including mine), and I personally feel the other two are more intuitive. Use any by applying the same logic as I have shown.

How to remove empty results (NULL values) in SQL

I think your error was caused by using TOTALORDERED alias in the where section and it's not yet available in there.

Select * from (
    SELECT P.PRODNR, P.PRODNAME,
    (SELECT SUM(QUANTITY) FROM PO_LINE POL
    WHERE P.PRODNR = POL.PRODNR) AS TOTALORDERED
    FROM PRODUCT P
    join (SELECT PRODNR
                    FROM PO_LINE
                    WHERE QUANTITY >= ALL
                                    (SELECT QUANTITY
                                    FROM PO_LINE
                                    )
          )pl on pl.PRODNR = P.PRODNR
)t 
WHERE TOTALORDERED is not NULL

Update: new answer based on query in question

select * from (
SELECT 
    P.PRODNR, P.PRODNAME,
    (SELECT SUM(QUANTITY) 
     FROM PO_LINE POL
     WHERE P.PRODNR = POL.PRODNR) AS TOTALORDERED
FROM 
    PRODUCT P
WHERE 
    P.PRODNR IN (SELECT P.PRODNR
                 FROM PO_LINE
                 WHERE QUANTITY >= ALL (SELECT QUANTITY
                                        FROM PO_LINE))
)s
WHERE TOTALORDERED is not NULL

How do I remove NULL values from a query result in SQL

Use a where clause to filter rows:

SELECT Wands.id, Wands_Property.age, Wands.coins_needed,
       Wands.power
FROM Wands
WHERE Wands_Property.is_evil = 0

Hide null values on parameter value

You can extend the filtering conditions:

WHERE a.date is null AND (@startDate is NOT NULL OR @EndDate IS NOT NULL) OR
      a.date between @StartDate AND @EndDate

how to omit null values using SQL query

Quickfix, without reading query:

WITH cte AS
(
SELECT Submission.Title AS [Submission_Title], CA.Surname AS [Researchers], Submission.Status AS [Status] 
FROM Submission 
CROSS APPLY (SELECT STUFF((SELECT DISTINCT ', ' + r.Surname 
FROM ResearcherSubmission rs INNER JOIN Researcher r 
ON r.ResearcherID = rs.ResearcherID 
WHERE CONCAT (DATENAME(MONTH,[Submission].[SubmissionDate]), ' ',DATEPART (YEAR,[Submission].[SubmissionDate])) = 'October 2015'  
AND Submission.SubmissionID = rs.SubmissionID 
FOR XML PATH (''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, ' ')) AS CA (Surname) 
GROUP BY convert(varchar(10),datename(month,Submission.SubmissionDate)), Submission.Title, CA.Surname, Submission.Status
)
SELECT *
FROM cte
WHERE Researchers IS NOT NULL;

There is probably more elegant solution, but you need to share sample data and structures.

This part may cause problems:

SELECT DISTINCT ', ' + r.Surname

try with CONCAT instead or :

SELECT DISTINCT ', ' + ISNULL(r.Surname, '')

SQL Server, Remove null values in a select case query

You can use sum.

select 
A.ekod
,sum(case when a.unit='3' then 1 else 0 end) AS [Unit=3]
from [Stat_unitdata].[dbo].[XXX_YYY] A
group by a.ekod
order by ekod

How to exclude null value rows from the result set of CASE statement oracle SQL

A simple option - if query you have returns desired result set - is to use it as a CTE (or a subquery) and filter rows out:

    with temp as
    -- your current query begins here
    (SELECT DISTINCT td.task_id,td.cntr_nbr,lh.dsp_locn AS pull_locn,td.orig_reqmt,td.qty_pulld,
        (
        CASE 
        WHEN
        ((SUM(td.qty_pulld) over (partition by td.pull_locn_id)) < td.orig_reqmt)  and ((SUM(td.qty_pulld) over (partition by td.pull_locn_id))-td.orig_reqmt <> 0) 
        THEN (td.orig_reqmt- td.qty_pulld)
        END) AS units_shorted
        FROM wm14.task_dtl td 
        INNER JOIN wm14.locn_hdr lh ON lh.locn_id = td.pull_locn_id
        INNER JOIN wm14.order_line_item oli ON oli.item_id = td.item_id
        WHERE  EXISTS (SELECT 1 FROM wm14.msg_log ml WHERE ml.user_id = td.user_id AND ml.msg_id IN ('1060','1034') AND module = 'CTRLKEY' 
        AND TRUNC(td.mod_date_time) = TRUNC(ml.create_date_time)) AND td.invn_need_type IN ('53','54')
        AND td.stat_code >= '90' 
        and td.task_genrtn_ref_nbr NOT IN (SELECT  ml.ref_value_1 FROM wm14.msg_log ml WHERE  ml.msg ='Undo Wave completed')
        group by td.task_id,td.cntr_nbr,lh.dsp_locn,td.task_genrtn_ref_nbr,td.pull_locn_id,td.item_id,td.qty_pulld,td.orig_reqmt
    -- your current query ends here; only the ORDER BY clause is moved out
    )
    select *
    from temp
    where units_shorted is not null      --> filter
    ORDER BY task_id, dsp_locn DESC;

Remove null values in SQL server

It looks like you want to combine the rows together for each ProductKey.

So instead of 5 lines, each with a single column populated, you want one line, with all 5 columns populated.

Do it like this:

Select Distinct T.ProductKey, C1.Column1, C2.Column2, C3.Column3 from MyTable T
left join MyTable C1 on C1.ProductKey = T.ProductKey and C1.Column1 is not null
left join MyTable C2 on C2.ProductKey = T.ProductKey and C2.Column2 is not null
left join MyTable C3 on C3.ProductKey = T.ProductKey and C3.Column3 is not null

Just replace "MyTable" above with your table name, and "Column1, Column2, Column3" with the names of the columns your data is in.

Pretend that each column is on its own separate table, and you need to use joins to connect all the tables back to your master set of ProductKeys.

Think about it in sets:
Basically you are going to make one master list of the keys that is distinct/unique (step 1), and then do a new left join for each column you want to attach to the master list (step 2), and as part of the joins, tell it to get the non-NULL values and ignore the NULLs.

---

Related Topics