• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 41
  • Last Modified:

Unknown column when using CASE in MySQL query

After 14 years,  my 300th question...

Please see this MySQL query
CREATE TEMPORARY TABLE scores AS
SELECT * FROM
(
SELECT home_team AS Team, home_score AS f_score, away_score AS a_score, match_date,
       CASE 
            WHEN home_score > away_score THEN 'W'  
            WHEN home_score < away_score THEN 'L' 
            ELSE 'D' 
       END AS wdl
    FROM all_results
    WHERE ar_fobg_team_code = '1'
    AND ar_season_start = 2017
    AND home_score REGEXP '[[:digit:]]'
        UNION
    SELECT away_team AS Team, away_score AS f_score, home_score AS a_score, match_date,
       CASE wdl
            WHEN home_score > away_score THEN 'L'  
            WHEN home_score < away_score THEN 'W' 
            ELSE 'D' 
       END AS wdl
    FROM all_results
    WHERE ar_fobg_team_code = '1'
    AND ar_season_start = 2017
    AND home_score REGEXP '[[:digit:]]'
) AS the_data;

Open in new window


When I run it I get an error 1054 Unknown column 'wdl' in field list.

My immediate response is...Of course it doesn't exist, I'm trying to create it!

What am I missing?
0
colinspurs
Asked:
colinspurs
  • 2
  • 2
1 Solution
 
PortletPaulfreelancerCommented:
Have you tried specifically listing the columns? (i.e. not using "*")

Does the union query operate if run by itself without creating a table?
0
 
PortletPaulfreelancerCommented:
Oh I see it now... there was a "wdl" after the case keyword in the send half of your query, now removed
CREATE TEMPORARY TABLE scores AS
SELECT
      team, f_score, a_score,match_date, wdl
FROM (
      SELECT
            home_team AS Team
          , home_score AS f_score
          , away_score AS a_score
          , match_date
          , CASE
                  WHEN home_score > away_score THEN 'W'
                  WHEN home_score < away_score THEN 'L'
                  ELSE 'D'
            END AS wdl
      FROM all_results
      WHERE ar_fobg_team_code = '1'
      AND ar_season_start = 2017
      AND home_score = REGEXP '[[:digit:]]'
      UNION
      SELECT
            away_team AS Team
          , away_score AS f_score
          , home_score AS a_score
          , match_date
          , CASE
                  WHEN home_score > away_score THEN 'L'
                  WHEN home_score < away_score THEN 'W'
                  ELSE 'D'
            END AS wdl
      FROM all_results
      WHERE ar_fobg_team_code = '1'
      AND ar_season_start = 2017
      AND home_score = REGEXP '[[:digit:]]'
) AS the_data;

Open in new window

1
 
NorieVBA ExpertCommented:
Should line 16 not just be CASE instead of CASE wdl?
1
 
colinspursAuthor Commented:
I now use SELECT Team, f_score, a_score, match_date, wdl FROM

and it still fails with the same error whether creating the table or not.  It's late, I'll check again tomorrow. Thanks for responding.

Col
0
 
colinspursAuthor Commented:
Thanks - I said it was late!  That was a result of not remembering how CASE worked and the legacy of an earlier effort.  Cheers.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now