Union all on different columns

Question

I have a query that is retrieving two columns to my results. I am using this query twice and putting these two queries together with a UNION ALL, because for every record there should be another record but with a different values in the two columns where I am pre-setting values, depending on the value of one of my set columns.

I should have a column that has all the prices set as Debit or Credit, if the ExamType is 6 then the prices should be set in a Credit column, if the ExamType is 2 then the prices should be set in Debit.

If I run them individually it works fine but once I use union all, I get a datatype error message. I have set these columns as NULLS but everything is under the Credit column and the Debit column is with null values. How can I fix this?

Example with short "invisible" string:

  Select StudentID, ExamDate, 2 AS ExamType, '232-442' AS Account,Amounts AS Debit, '' AS Credit 

    From TableA

    UNION ALL 

    Select StudentID, ExamDate, 6 AS ExamType, '832-446' AS Account, Amounts AS Credit, '' AS Debit

    From TableA
ORDER BY 1

Example with NULL

  Select StudentID, ExamDate, 2 AS ExamType, '232-442' AS Account,NULL  AS Credit,Amounts AS Debit  

    From TableA

    UNION ALL 

    Select StudentID, ExamDate, 6 AS ExamType, '832-446' AS Account, NULL AS Debit, Amounts AS Credit, 

    From TableA
ORDER BY 1

Final Results - How they should be:

    StudentID    ExamDate    ExamType  Account   Debit  Credit 
    1232111      8/9/2010    2         232-442   56.90
    1232111      8/9/2010    6         832-446          56.90 
    4773923      7/5/2010    2         232-442   46.91
    4773923      7/5/2010    6         832-446          46.91 

Answer 1

In Firebird, columns must be typed, and this is inferred from the underlying column or from the literal value. In the case of a UNION, the first query will determine the column data types and their names. The second (and subsequent) queries must have the same data types for the columns by position.

In other words, your query

Select StudentID, ExamDate, 2 AS ExamType, '232-442' AS Account,Amounts AS Debit, '' AS Credit 
From TableA
UNION ALL 
Select StudentID, ExamDate, 6 AS ExamType, '832-446' AS Account, Amounts AS Credit, '' AS Debit
From TableA

This query has two distinct problems:

1. The sixth column will be called Debit and the seventh will be called Credit. This means that even though in the second query you switch around the names, the values will not, and those values will still appear in the Debit column.
2. The datatype of the seventh column will be inferred as CHAR(1) (or SQL_NULL for the second example) instead of a numerical type.

To fix this you need to:

1. Swap the columns in the second query
2. Explicitly cast the NULL value to the appropriate type (eg DECIMAL(18,2))

The resulting query will be

Select StudentID, ExamDate, 2 AS ExamType, '232-442' AS Account, Amounts AS Debit, CAST(NULL AS DECIMAL(18,2)) AS Credit 
From TableA
UNION ALL 
Select StudentID, ExamDate, 6 AS ExamType, '832-446' AS Account, NULL AS Debit, Amounts AS Credit
From TableA

I have left in the AS-clauses in the second query, but they only serve as documentation: Firebird itself ignores them and only uses the labels defined in the first query.

However I am wondering if your current approach is the right one: instead of using a UNION ALL, this sounds more like a problem that asks for a join between a student table and some cost/finances table. But given the lack of details in your question that is hard to know for sure.

I have not addressed the necessary ordering in my answer, take a look at Arioch'The's answer for that.

Answer 2

You seem to be making few mistakes with your UNION. Read Martin Gruber's "Essential SQL" or at least read the Firebird (much more brief) documentation: https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-dml-select.html#fblangref25-dml-select-union

You simplified query is here:

Select StudentID, 2 AS ExamType, NULL  AS Credit, Amounts AS Debit  
    From TableA
UNION ALL 
Select StudentID, 6 AS ExamType, NULL AS Debit, Amounts AS Credit, 
    From TableA 
ORDER BY 1

1) columns naming and the desired outcome

Select ' ' AS Credit, Amounts AS Debit  
    From TableA
UNION ALL 
Select ' ' AS Debit, Amounts AS Credit, 
    From TableA

You expect it to produce cross-checkered data like

Debit  Credit 
        56.90 
        46.91 
56.90
46.91

The real output would be like

Debit  Credit 
        56.90
        46.91
        56.90 
        46.91 

You do have to read the documentation on UNION clause!

The names and datatypes of the column only matter on the FIRST leading query in the union. All subsequent queries do accommodate (or do fail to accommodate) to the names and datatypes already set before them. For all the queries after the first one all that matters is the ORDERING of their columns.

Since there in your query #1 the column #4 was already called Credit - it would be still so with the column #4 in the query #2, and your as Debit in the second query is discarded. The columns of the second and further queries are just mapped to the leading query by their order and nothing else.

2) column data types - adding to the names issue.

After you would read the above and fix the names issue you would probably end with something like (minimized example):

Select NULL AS Credit, Amounts AS Debit  
    From TableA
UNION ALL 
Select Amounts, NULL -- no names here, they do not matter
    From TableA

And here you finally would get the data type mismatch error. Why? Because - you read it above - the types are set by the LEADING query, and thus the type of the 1st column is NULL. When the Firebird starts executing your second query it would fetch a not-NULL number as Amounts and would be demanded by your leading query to convert number to NULL. But that is not possible. Thus the error.

What you have to do here is to explicitly tell Firebird WHICH column data type you want in that query regardless the column values.

For example,

Select StudentID, 2 AS ExamType, 
  CAST( NULL AS NUMERIC(10,2)) AS Credit,
  Amounts AS Debit  
    From TableA
UNION ALL 
Select StudentID, 6, Amounts, NULL
    From TableA

3) Ordering - unions do daisy-chain the complete queries, with potentially very different data sources and filtering. Thus ordering is most probably done after the fact, when all the records fetched, this would most probably be natural sorting, ignoring all the indexes you could had have on the tableA, thus should be slow and memory-consuming on the big tables.

Frankly, w.r.t efficiency on big queries you better replace UNION with something like EXECUTE BLOCK or STORED PROCEDURE as was outlined just few days ago: Display two unrelated select queries with no mutual fields in one firebird procedure

On small queries though the extra burden of post-fetch natural sorting would be small and the simplicity of the query can have more weight. Make your choice.