UNION ALL converts datatype for column to LONGVARCHAR

[Former Member]

SELECT NAME FROM xxx WHERE xxx
UNION ALL
SELECT 'BOB' FROM xxx WHERE xxx

Results in an OLE DB data type of LONGVARCHAR instead of the VARCHAR(50) for the column NAME

Is there anyway to correct this in the database without affecting the sql statement as it runs against other databases and there are many other instances of this type of code.

EDIT:

C# code that demonstrates the problem

        string queryString =
            @" select parent_account_id from chart_of_accounts union all select 'Bob' from chart_of_accounts";
        using (OleDbConnection connection =
                   new OleDbConnection("Provider=SAOLEDB.12;UID=xxx;PWD=xxx;BF=xxx;Persist Security Info=True;"))
        {
            OleDbCommand command = connection.CreateCommand();
            command.CommandText = queryString;

            try
            {
                connection.Open();

                OleDbDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
// break here and was able to view the internal schema structure which indicated LONGVARCHAR
                    Console.WriteLine("\\t{0}\\t{1}",
                        reader[0].ToString(), reader[1]);
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }

            Console.ReadKey();
        }
    }

[MCMartin]

you can try to cast('bob' as varchar(50))

[VolkerBarth · Accepted solution]

I don't think this is a general problem - the result type of a column in a UNION query should be derived in a reasonable way from the column types of the underlying query blocks, and a string literal will be specified with its minimal type - which can be inspected with the EXPRTYPE function:

select exprtype('SELECT ''BOB''', 1)

returns "char(3)" in my case.

So, with a sample query against the system catalog, 'BOB' is converted in a meaningful way:

SELECT EXPRTYPE(
  'SELECT table_name FROM systable WHERE table_id = 1
   UNION ALL
   SELECT ''BOB''', 1)

returns "char(128)" - which is the type of systable.table_name.

And the similar query against another column of that table

SELECT EXPRTYPE(
  'SELECT view_def FROM systable WHERE table_id = 1
   UNION ALL
   SELECT ''BOB''', 1)

returns "long varchar" - which is the type of systable.view_def.

Therefore, I would think that

• this is bound to the OLEDB interface or
• the column "parent_account_id" is using a LONG VARCHAR itself, or this is the smallest common type that can contain both values.

[Former Member · Accepted solution]

Thanks for replying. The type of parent_account_id is varchar(50) and if in both select statements I have parent_account_id the type returned is SQL_VARCHAR.

I ran that second exprtype using the my the union statement above and it returns a LONG VARCHAR.

[Former Member · Accepted solution]

So...Apparently I was wrong about the data type I am using. The column is NVARCHAR(80) not VARCHAR(80).

The exprType returns varchar(80) when the column data type is a varchar but long varchar when the data type if nvarchar.

Go figure.

[VolkerBarth · Accepted solution]

Does this happen, too, when you run the query in DBSIQL (or dbisqlc)?

What v12 build are you using? (I've tested with 12.0.1.3554.)

[VolkerBarth · Accepted solution]

Hm, so it seems to be due to mixing CHAR and NCHAR.

However, when I use the sample queries above with the NCHAR string literal "N'BOB'" instead of the CHAR string literal "BOB", I get LONG NVARCHAR as type - which seems to be the typical type returned by functions like UNISTR...

So using a NCHAR string literal might help.