Replace 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 should always return something. If the result is not obtained,

 0 0 0 0 

Line

must be returned. How can i do this. Using isnull for each selected field is not useful.

Answer 1

Use a view with one row, and an outer one to another table / query.

Here is an example with a table variable @T instead of your real table.

 declare @T table ( ID int, Grp int ) select isnull(Q.MaxID, 0) as MaxID, isnull(QC, 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 QC -- order by goes to the outer query 

This will result in the output always having at least one line.

Something like this using your request.

 select isnull(Q.TipoVers, '0') as TipoVers, isnull(Q.ImpTot, 0) as ImpTot, isnull(QN, 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 

Answer 2

Use COALESCE . It returns the first nonzero value. For instance.

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

Will return 0 if TV.DESC is NULL.

Answer 3

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

Answer 4

If you want to include the string "0 0 0 0" in the result set in which it has data, you can use 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 FROM for the second SELECT. In Oracle, it will be "FROM DUAL". MySQL does not require OT