CODEWITHSUNDEEP
sqlsql-serverhaving
Caetano
Caetano

Reputation: 25

SQL query - having expression > 0

I am working on Microsoft SQL Server 2014 and I have the following SQL query which works:

SELECT 
    h.entidade, h.datadoc, h.tipodoc, h.numdoc,
    (SELECT valortotal -
         (SELECT COALESCE(SUM(l.valorrec), 0) 
          FROM LinhasLiq as l
          INNER JOIN cabliq ON l.IdCabLiq = CabLiq.id
          INNER JOIN Historico ON L.IdHistorico = Historico.id
          WHERE cabliq.DataDoc < '01/05/2015'
            AND historico.id = h.id)) as 'valor pendente',  
    coalesce(documentosCCT.Descricao,'')
+' '+ CASE WHEN h.modulo<>'V' THEN coalesce(documentosVenda.Descricao,'') else'' END 
+' '+  coalesce(h.descricao,'') AS descricaogeral   
FROM 
    Historico h 
LEFT JOIN 
    documentosCCT ON h.TipoDoc = documentosCCT.Documento  
LEFT JOIN 
    documentosVenda ON h.Tipodoc = documentosVenda.Documento
WHERE
    h.entidade = 'ta0141' AND
    (h.tipoentidade = 'C' OR h.tipoentidade = 'F') 
ORDER BY 
    datadoc ASC

and this specific expression 

(select valortotal - (SELECT COALESCE(SUM(l.valorrec),0) from LinhasLiq as l
    inner join cabliq on l.IdCabLiq = CabLiq.id
    inner join Historico on L.IdHistorico = Historico.id
    where cabliq.DataDoc < '01/05/2015' and historico.id = h.id)) as 'valor pendente'

returns a lot of 0 values, so how can I put this entire expression in a having X > 0 clause, or any other way as long as the rows with this expression = 0 doesn't show?

Many thanks.

Upvotes: 0

Views: 210

Answers (1)

wiretext
wiretext

Reputation: 3342

i don't know what is your circumstances if it is feasible try to make query in single select statement instead of multiple Don't make an alias valor pendente like that should be valorpendente

Now you can use CTE 

;WITH CTE AS
(
-- YOUR QUERY
)

SELECT * FROM CTE
WHERE valorpendente > 0

Upvotes: 1

Related Questions