Replace no result

Question

I have a query like this:

SELECT TV.Descrizione as TipoVers, 
       sum(ImportoVersamento) as ImpTot, 
       count(*) as N,
       month(DataAllibramento) as Mese
FROM PROC_Versamento V
left outer join dbo.PROC_TipoVersamento TV
    on V.IDTipoVersamento = TV.IDTipoVersamento
inner join dbo.PROC_PraticaRiscossione PR 
    on V.IDPraticaRiscossioneAssociata = PR.IDPratica
inner join dbo.DA_Avviso A
    on PR.IDDatiAvviso = A.IDAvviso
where DataAllibramento between '2012-09-08' and '2012-09-17' and  A.IDFornitura = 4
group by V.IDTipoVersamento,month(DataAllibramento),TV.Descrizione
order by V.IDTipoVersamento,month(DataAllibramento)

This query must always return something. If no result is produced a

0 0 0 0

row must be returned. How can I do this. Use a isnull for every selected field isn't usefull.

Kinfly provide the complete Query. – Prahalad Gaggar May 30 '13 at 12:25 — Prahalad Gaggar, May 30 '13 at 12:25
@Luv all code is reported now... – BAD_SEED May 30 '13 at 12:37 — BAD_SEED, May 30 '13 at 12:37

score 2 · Answer 1 · edited May 23 '17 at 12:18

2

Use COALESCE. It returns the first non-null value. E.g.

SELECT COALESCE(TV.Desc, 0)...

Will return 0 if TV.DESC is NULL.

edited May 23 '17 at 12:18

Community

1
1

answered May 30 '13 at 12:27

dKen

3,078
1
28
37

It doesn't works. Are we sure that no result is the same as NULL? – BAD_SEED May 30 '13 at 12:35
Well, that depends on the data in your database. You did mention using isnull, so I assumed there were some non-null values that you were checking against. What specifically are you considering "no result"? An empty string? – dKen May 30 '13 at 12:38

Mikael Eriksson · Accepted Answer · 2013-05-30T13:10:08.647

2

Use a derived table with one row and do a outer apply to your other table / query.

Here is a sample with a table variable @T in place of your real table.

declare @T table
(
  ID int,
  Grp int
)

select isnull(Q.MaxID, 0) as MaxID,
       isnull(Q.C, 0) as C
from (select 1) as T(X)
  outer apply (
              -- Your query goes here
              select max(ID) as MaxID,
                     count(*) as C
              from @T
              group by Grp
              ) as Q
order by Q.C -- order by goes to the outer query

That will make sure you have always at least one row in the output.

Something like this using your query.

select isnull(Q.TipoVers, '0') as TipoVers, 
       isnull(Q.ImpTot, 0) as ImpTot, 
       isnull(Q.N, 0) as N,
       isnull(Q.Mese, 0) as Mese
from (select 1) as T(X)
  outer apply (
              SELECT TV.Descrizione as TipoVers, 
                     sum(ImportoVersamento) as ImpTot, 
                     count(*) as N,
                     month(DataAllibramento) as Mese,
                     V.IDTipoVersamento
              FROM PROC_Versamento V
              left outer join dbo.PROC_TipoVersamento TV
                  on V.IDTipoVersamento = TV.IDTipoVersamento
              inner join dbo.PROC_PraticaRiscossione PR 
                  on V.IDPraticaRiscossioneAssociata = PR.IDPratica
              inner join dbo.DA_Avviso A
                  on PR.IDDatiAvviso = A.IDAvviso
              where DataAllibramento between '2012-09-08' and '2012-09-17' and  A.IDFornitura = 4
              group by V.IDTipoVersamento,month(DataAllibramento),TV.Descrizione
              ) as Q
order by Q.IDTipoVersamento, Q.Mese

edited May 30 '13 at 13:10

answered May 30 '13 at 12:29

Mikael Eriksson

136,425
22
210
281

Msg 156, Level 15, State 1, Line 5 Incorrect syntax near the keyword 'values'. Msg 156, Level 15, State 1, Line 21 Incorrect syntax near the keyword 'as'. – BAD_SEED May 30 '13 at 12:54
@marianoc84 What version of SQL Server are you using? – Mikael Eriksson May 30 '13 at 12:55
1

@marianoc84 You are probably on SQL Server 2005. Try the updated version of the answer. – Mikael Eriksson May 30 '13 at 12:56
1

Perfect! I just replaced nullif with isnull! ;) – BAD_SEED May 30 '13 at 13:07
1

@marianoc84 Haha. that would certainly improve matters :) – Mikael Eriksson May 30 '13 at 13:09
1

@marianoc84 Don't really understand that. What values should there be on the rows? Why does it matter how many rows you get? You should probably ask that as a new question where you provide the table structures some sample data and the expected output. You could even setup a [SQL Fiddle](http://sqlfiddle.com/) to help the answerers along the way. – Mikael Eriksson May 30 '13 at 13:49
A disadvantage of doing things this way is that you assume the result set will never contain nulls. – Omaer Jun 05 '13 at 13:25

score 0 · Answer 3 · answered May 30 '13 at 12:38

0

You can try:

with    dat as (select    TV.[Desc] as TipyDesc, sum(Import) as ToImp, count(*) as N, month(Date) as Mounth
                from        /*DATA SOURCE HERE*/ as TV
                group by    [Desc], month(Date))
    select    [TipyDesc], ToImp, N, Mounth from    dat
    union all
    select    '0', 0, 0, 0 where    (select count (*) from dat)=0

That should do what you want...

answered May 30 '13 at 12:38

Omaer

817
1
7
22

1

PS. I'm guessing that by 'No result', you mean that no rows were returned. – Omaer May 30 '13 at 12:44

score -3 · Answer 4 · answered May 30 '13 at 12:31

If it's ok to include the "0 0 0 0" row in a result set that has data, you can use a union:

SELECT TV.Desc as TipyDesc, 
   sum(Import) as TotImp, 
   count(*) as N,
   month(Date) as Mounth
   ...
UNION
SELECT
    0,0,0,0

Depending on the database, you may need a FROM for the second SELECT. In Oracle, this would be "FROM DUAL". For MySQL, no FROM is necessary

Replace no result

4 Answers4