How to use CASE statement inside HAVING clause
<p>I tried searching the site for an answer on this topic but could not find anything that answered my question. I have a feeling this is very simple.</p>

<p>I am writing a simple SQL Query to help find Unvouchered Purchase Orders and depending on if the purchase order type is Purchase or Credit it requires different logic in the HAVING clause. In order to get the data I had to write two different queries. See Code Samples below</p>

<p>PURCHASE PO</p>

<pre><code>select 
prchseordr_id as 'PO ID',
max(prchseordr_dte_rqstd) as DateRequested,
max(prchseordr_type) as POType,
max(vndr_nme) as Vendor, 
sum(imhstry_qntty_ordrd) as QuantityOrdered,
sum(imhstry_qntty_invcd_ap) as QuantityVouchered
from imhstry
join prchseordr on imhstry.imhstry_ordr_id = prchseordr.prchseordr_id
join brnch on prchseordr.brnch_rn = brnch.brnch_rn
join vndr on prchseordr.vndr_rn = vndr.vndr_rn
where prchseordr_dte_rqstd between '2016-01-01' and '2016-04-01'
and prchseordr_type = 'PURCHASE'
group by prchseordr.prchseordr_id
HAVING sum(imhstry_qntty_invcd_ap) &lt; sum(imhstry_qntty_ordrd)
order by prchseordr_id asc 
</code></pre>

<p>CREDIT PO</p>

<pre><code>select 
prchseordr_id as 'PO ID',
max(prchseordr_dte_rqstd) as DateRequested,
max(prchseordr_type) as POType,
max(vndr_nme) as Vendor, 
sum(imhstry_qntty_ordrd) as QuantityOrdered,
sum(imhstry_qntty_invcd_ap) as QuantityVouchered
from imhstry
join prchseordr on imhstry.imhstry_ordr_id = prchseordr.prchseordr_id
join brnch on prchseordr.brnch_rn = brnch.brnch_rn
join vndr on prchseordr.vndr_rn = vndr.vndr_rn
where prchseordr_dte_rqstd between '2016-01-01' and '2016-04-01'
and prchseordr_type = 'CREDIT'
group by prchseordr.prchseordr_id
HAVING sum(imhstry_qntty_invcd_ap) = '0' 
order by prchseordr_id asc 
</code></pre>

<p>What I want to is combine these two queries into one but I am running into errors when I try to combine them using a CASE statement in the HAVING clause. Latest attempt is throwing me a syntax error on the = and &lt; sign in the HAVING clause. I am obviously missing something important.</p>

<pre><code>select 
prchseordr_id as 'PO ID',
max(prchseordr_dte_rqstd) as DateRequested,
max(prchseordr_type) as POType,
max(vndr_nme) as Vendor, 
sum(imhstry_qntty_ordrd) as QuantityOrdered,
sum(imhstry_qntty_invcd_ap) as QuantityVouchered
from imhstry
join prchseordr on imhstry.imhstry_ordr_id = prchseordr.prchseordr_id
join brnch on prchseordr.brnch_rn = brnch.brnch_rn
join vndr on prchseordr.vndr_rn = vndr.vndr_rn
where prchseordr_dte_rqstd between '2016-01-01' and '2016-04-01'
group by prchseordr.prchseordr_id
HAVING CASE WHEN prchseordr_type = 'Credit' THEN sum(imhstry_qntty_invcd_ap) = '0' 
OR CASE WHEN prchseordr_type = 'Purchase' THEN sum(imhstry_qntty_invcd_ap) &lt; sum(imhstry_qntty_ordrd)
order by prchseordr_id asc 
</code></pre>

<p>Any ideas or help would be much appreciated.</p>