0

I have 3 subqueries that work fine individually. I am attempting to join them with Union All. The query runs and gives the correct number of results but the format of the date field is messed up; for example '2016-11-01' is showing as '2,016, -11, -01'. This is the same for every row.

I've had a search and have found some threads concerning aliases but they do not seem to be the problem here as the query is running fine, with the exception of the date format.

Any help would be appreciated, thanks.

Here is the code:

    # offers_8a
    SELECT advisor, ats_id, offer_date
    FROM max_offers
    WHERE offer_date > (CURDATE()- INTERVAL 135 DAY)
    GROUP BY advisor, ats_id, offer_date

    UNION ALL

    # offers_8b
    SELECT mo.advisor, mo.ats_id, mo.offer_date

    FROM max_offers as mo
        INNER JOIN source_tables.ats as ats
            ON mo.ats_id = ats.ats_id

        INNER JOIN source_tables.intake_corrections as ic
            ON ats.intake = ic.sugar_name

        INNER JOIN source_tables.intakes as it
            ON ic.intake = it.intake

        INNER JOIN source_tables.dates as dt
            ON ic.intake = dt.current_intake

    GROUP BY mo.advisor, mo.ats_id, mo.offer_date, it.adms_intake_start

    HAVING offer_date > (it.adms_intake_start - INTERVAL 135 DAY)

    UNION ALL 


    # offers_8c
    SELECT mo.advisor, mo.ats_id, mo.offer_date

    FROM max_offers as mo
        INNER JOIN source_tables.ats as ats
            ON mo.ats_id = ats.ats_id

        INNER JOIN source_tables.intake_corrections as ic
            ON ats.intake = ic.sugar_name

        INNER JOIN source_tables.intakes as it
            ON ic.intake = it.intake

        INNER JOIN 

            #s_prev_intake
            (SELECT current_next_intakes.current_intake, assign,         forecast_intake, forecast_intake_sugar

                FROM source_tables.dates
                    INNER JOIN source_tables.current_next_intakes
                        ON source_tables.dates.current_intake =  source_tables.current_next_intakes.current_intake

                WHERE assign = 'Prev') AS prv

            ON ic.intake = prv.forecast_intake


    GROUP BY mo.advisor, mo.ats_id, mo.offer_date, it.adms_intake_start

    HAVING offer_date > (it.adms_intake_start - INTERVAL 135 DAY);
  • Have you tried forcing a format on the date field in the SELECT clause? mysqltutorial.org/mysql-date_format – CaM Feb 7 '17 at 16:11
  • Just tried that, it shows correctly but is in text format, whereas the individual queries return the date as date-time, which is what I need. Thanks – agwah Feb 7 '17 at 16:18
  • What tool are you using to display the result? UNION is not the villain. – Rick James Feb 8 '17 at 2:58
  • I'm not sure posting the DDL will be helpful - max_offers (the table that offer_date is taken from in every subquery) is a view, created using a table where all the date fields do show as 'DATE'. When the 3 parts of the Union are ran individually they show the correct Date format. – agwah Feb 8 '17 at 9:21
  • I am using HeidiSQL. – agwah Feb 8 '17 at 9:21
0

I have fixed this by enclosing the Union in brackets and using it as a derived table in a Select query, didn't need to alter any formats etc.

Is it always best to do this with Union queries? The examples I found online did not show this.

Thanks

-3

Use UNION instead of UNION ALL.

  • Welcome to DBA.SE! Please can you expand your answer to explain why this would resolve the problem that the original poster was having? – Mr.Brownstone May 24 '19 at 21:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.