Geek Answers Handbook

SQL, if rows are not returned,

I have a select statement, and I want to say that if this select statement does not return any rows, then put '' in each cell. How to do it?

+5
source share
7 answers

It looks like you are still not getting all the rows you need. Truth? I think @Joe Sefanelli is an important part of your decision, and then mentions that you need to change INNER to LEFT.

So, you say that you want to display all units in the list of your devices. And, if there is no data for the device, then display a block and spaces for data that does not exist.

Here is a possible solution. Modify the FROM clause as follows:

FROM  [dbo].[Unit] u 
LEFT OUTER JOIN 
    (
    SELECT *
    FROM [dbo].[IUA] i
    JOIN [dbo].[Reports] r ON r.[Report_ID] = i.[Report_ID]
    JOIN [dbo].[State] s ON i.[St_ID] = s.[St_Id]
    WHERE r.[Account] = [dbo].[fn_Get_PortalUser_AccountNumber](11-11)
        AND r.[Rpt_Period] = '2126'
        AND r.[RptName] = 'tfd'
        AND r.[Type] = 'h'    
    ) ir ON ir.[Unit_ID] = u.[Unit_ID]
LEFT JOIN [dbo].[UnitType] ut ON u.[UnitType] = ut.[UnitType]
WHERE u.[Unit] IN (SELECT [VALUE] 
               FROM dbo.udf_GenerateVarcharTableFromStringList(@Units, ','))
;

, @Units. , , , .

+2
select a, b, c from t
if @@rowcount = 0
    select '' as a, '' as b, '' as c

, , '' , a, b c.

+8

-

IF NOT EXISTS ( SELECT 'x' FROM <TABLE> .... )
BEGIN
    -- Your logic goes here
END
+4

select x.JobName , x.Description
from MasterJobList x
where x.IsCycleJob = 1 

union all

select "" , "" 
from MasterJobList x
where not exists
    (
    select 1
    from MasterJobList x
    where x.IsCycleJob = 1 
    )
+3

, , UnitType, , .

ISNULL(ut.[Description], '')  AS UnitType
+1
select top 1 isnull(max(col2),' ') as noNullCol from table1 where col1='x'

max returns a null, where there are no rows, then the function isnullreturns ' 'instead of the valuenull

0
source

Here is an example that I use for a single column - it is simple and only creates an empty row if there are no matches in the dataset.

select Company from customer where customer=@Company
union
select '' where not exists (select 1 from customer where customer=@Company)

This creates a string of spaces if there are no matches.

0
source

More articles:


All Articles