OK, I realise this might be a big ask, but I cannot see what's wrong with line 43 of this query at the HAVING statement. Previously in this thread I've got help to put together a query with some example data. Below is the actual query I use given the schema I have.
It all works beautifully until I put in the last bit of logic. The MODE field can have about 60 different values. In the query I need to map all 60 to one of 3 'modes', CW, Phone or Digital. Using the MD field to do this for each record. I'm trying to test that the MD value of the outer query is the same as the query in the EXISTS statement.
All I get is an error saying column a.MD is unknown. Help!
SELECT CALLSIGN,
c.Entity,
QSO_DATE,
TIME_ON,
BAND,
MODE,
CASE
WHEN MODE='CW' THEN 'CW'
WHEN MODE IN ('AM',
'SSB',
'FM') THEN 'Phone'
WHEN NOT MODE IN ('DSTAR',
'VOI') THEN 'Digital'
ELSE 'Other'
END AS MD,
CASE NOT EXISTS
(SELECT *
FROM QSO AS b
WHERE a.DXCC=b.DXCC
AND b.ClientID='20005881'
AND (find_in_set('QSL_OK',b.FLAGS)>0
OR find_in_set('EQSL_OK',b.FLAGS)>0
OR find_in_set('LOTW_OK',b.FLAGS)>0))
WHEN TRUE THEN 'Y'
ELSE 'N'
END AS NC,
CASE NOT EXISTS
(SELECT *,
CASE
WHEN MODE='CW' THEN 'CW'
WHEN MODE IN ('AM',
'SSB',
'FM') THEN 'Phone'
WHEN NOT MODE IN ('DSTAR',
'VOI') THEN 'Digital'
ELSE 'Other'
END AS MD
FROM QSO AS b
WHERE a.DXCC=b.DXCC
AND b.ClientID='20005881'
AND (find_in_set('QSL_OK',b.FLAGS)>0
OR find_in_set('EQSL_OK',b.FLAGS)>0
OR find_in_set('LOTW_OK',b.FLAGS)>0) HAVING a.MD=b.MD)
WHEN TRUE THEN 'Y'
ELSE 'N'
END AS NM,
CASE NOT EXISTS
(SELECT *
FROM QSO AS b
WHERE a.DXCC=b.DXCC
AND a.BAND=b.BAND
AND b.ClientID='20005881'
AND (find_in_set('QSL_OK',b.FLAGS)>0
OR find_in_set('EQSL_OK',b.FLAGS)>0
OR find_in_set('LOTW_OK',b.FLAGS)>0))
WHEN TRUE THEN 'Y'
ELSE 'N'
END AS NB
FROM QSO AS a
JOIN DXCC AS c ON a.DXCC=c.DXCCnum
WHERE ClientID='20005881'
AND find_in_set('QSL_RCVD',FLAGS)>0
AND find_in_set('QSL_OK',FLAGS)=0
AND find_in_set('EQSL_OK',FLAGS)=0
AND find_in_set('LOTW_OK',FLAGS)=0 HAVING NC='Y'
OR NB='Y'
OR NM='Y'
ORDER BY Entity
Edited 3 time(s). Last edit at 02/09/2014 02:59AM by Marc Hillman.