Connecting two queries using UNION ALL-CodePudding

I want to connect the following two queries with union all.

First query is following:

    WITH
    week_source
    AS
        (SELECT week_desc,
                TO_CHAR (day_date, 'IYYY') || 'W' || TO_CHAR (day_date, 'IW')
                    week,
                (SELECT DISTINCT day_number
                   FROM period_day
                  WHERE day_key = d.day_key AND day_number NOT IN ('H', 'W') AND WEEK_NUM !=to_char(sysdate, 'WW')
)
                

        workdays_count
               FROM period_day d
              WHERE TO_CHAR (day_date, 'IYYYIW') BETWEEN TO_CHAR (
                                                             (SYSDATE - 1) - 28,
                                                             'IYYYIW')
                                                     AND TO_CHAR (
                                                             (SYSDATE - 1),
                                                             'IYYYIW')
                                                             
                                                                       )
                                                                       
                                                             
      SELECT DISTINCT week_desc, week, workdays_count
        FROM week_source
       WHERE workdays_count IS NOT NULL
      ORDER BY week;

It gives following table

The second query is:

SELECT 'W' || TO_CHAR (SYSDATE - 1, 'IW')     WEEK_DESC,
       TO_CHAR (SYSDATE - 1, 'IYYYIW')        WEEK,
       
       COUNT (day_date)                      WORKDAYS_COUNT
  FROM period_day
 WHERE     day_number NOT IN ('H', 'W')
       AND TO_CHAR (day_date, 'IYYYIW') =
           TO_CHAR ((SYSDATE - 1), 'IYYYIW')
       AND day_date <= (SYSDATE - 1)

which gives the following table

Any help?

CodePudding user response：

You have to put order by clauseat the end. If the first query returns 3 columns with data types varchar, int, varchar then the second query has to return columns of same data types.

WITH week_source AS
(SELECT week_desc,
        TO_CHAR (day_date, 'IYYY') || 'W' || TO_CHAR (day_date, 'IW') week,
        (SELECT DISTINCT day_number
         FROM period_day
         WHERE day_key = d.day_key 
         AND day_number NOT IN ('H', 'W') 
         AND WEEK_NUM !=to_char(sysdate, 'WW')) workdays_count 
 FROM period_day d
 WHERE TO_CHAR (day_date, 'IYYYIW') BETWEEN 
                                    TO_CHAR ((SYSDATE - 1) - 28, 'IYYYIW')
                                        AND 
                                    TO_CHAR ((SYSDATE - 1),'IYYYIW'))
SELECT DISTINCT week_desc, week, workdays_count
FROM week_source
WHERE workdays_count IS NOT NULL
union all
SELECT 'W' || TO_CHAR (SYSDATE - 1, 'IW') WEEK_DESC,
       TO_CHAR (SYSDATE - 1, 'IYYYIW') WEEK,
       to_char(COUNT(day_date)) WORKDAYS_COUNT
FROM period_day
WHERE day_number NOT IN ('H', 'W')
AND TO_CHAR (day_date, 'IYYYIW') = TO_CHAR ((SYSDATE - 1), 'IYYYIW')
AND day_date <= (SYSDATE - 1)
ORDER BY week

In this DEMO you can see the errors returning when the order by is located after the first query. Also you can see what error you get if I just remove the order by clause and this second error is because data types that first query is returning is different that the data types second row is returning. That is why I have added to_char to your count function in your second query. You could of also add to_number in your first query like this: SELECT DISTINCT to_number(day_number).