CODEWITHSUNDEEP
sqlt-sql
BAD_SEED
BAD_SEED

Reputation: 5056

Replace no result

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.

Upvotes: 4

Views: 1149

Answers (4)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

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

Upvotes: 2

Omaer
Omaer

Reputation: 827

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...

Upvotes: 0

Joe
Joe

Reputation: 6827

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

Upvotes: -3

dKen
dKen

Reputation: 3127

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

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

Will return 0 if TV.DESC is NULL.

Upvotes: 2

Related Questions