MySQL Forums
Forum List  »  Newbie

Re: Another newbie question
Posted by: Marc Hillman
Date: February 09, 2014 02:56AM

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.

Options: ReplyQuote


Subject
Written By
Posted
February 06, 2014 11:58PM
February 07, 2014 01:11AM
February 07, 2014 06:41AM
February 07, 2014 11:15AM
February 07, 2014 07:50PM
February 08, 2014 11:54AM
February 08, 2014 05:41PM
February 08, 2014 06:55PM
February 08, 2014 06:59PM
February 08, 2014 08:48PM
Re: Another newbie question
February 09, 2014 02:56AM
February 09, 2014 07:43PM
February 10, 2014 02:31PM
February 11, 2014 05:10AM
February 11, 2014 10:41AM
February 11, 2014 08:12PM
February 12, 2014 12:50AM
February 12, 2014 08:32AM
February 12, 2014 07:39PM
February 12, 2014 10:47PM
February 12, 2014 11:50PM
February 13, 2014 12:00AM
February 13, 2014 02:14AM
February 13, 2014 11:10PM
February 16, 2014 12:51AM
February 27, 2014 04:54AM
February 27, 2014 08:47PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.