How to alias a column in Unidata query with U2 provider

Question

In SQL Server, I can create a column alias like so:

select id as NewID from sometable

How can I accomplish this in the U2 provider?

I've tried:

select id NewID    --returns Column0
select id as NewID --returns Column0
select id "NewID"  --returns Column0
select id 'NewID'  --returns Column0

EDIT:

It looks like Unidata handles ODBC and U2 connections differently. Here is a repo:

using System;
using System.Data.Odbc;
using U2.Data.Client;

namespace Demo
{
    class Program
    {
        static void Main(string[] args)
        {
            var csU2 = "Database=XXX;User ID=XXX;Password=XXX;Server=XXX;ServerType=UNIDATA;RpcServiceType=udserver;FirstNormalForm=True";
            var csOdbc = "DSN=XXX";

            var sql = "select ID, ID as NewID from SYS_INSTALL_HISTORY_NF where 1=0";

            Console.WriteLine("U2Command:");

            using (var cnn = new U2Connection(csU2))
            using (var cmd = cnn.CreateCommand())
            {
                cmd.CommandText = sql;
                cnn.Open();
                var reader = cmd.ExecuteReader();

                for (int i = 0; i < reader.FieldCount; i++)
                {
                    Console.WriteLine(reader.GetName(i));
                }
            }

            Console.WriteLine();
            Console.WriteLine("OdbcCommand:");

            using (var cnn = new OdbcConnection(csOdbc))
            using (var cmd = cnn.CreateCommand())
            {
                cmd.CommandText = sql;
                cnn.Open();
                var reader = cmd.ExecuteReader();

                for (int i = 0; i < reader.FieldCount; i++)
                {
                    Console.WriteLine(reader.GetName(i));
                }
            }
        }
    }
}

Result:

U2Command:
ID
Column1

OdbcCommand:
ID
NewID

u2 odbc driver version: 7.140.01.8004 u2 .net provider version: 2.2.0.0

Answer 1

I think you have some sort of bug in the .net provider there. I was not able to replicate it using your code (with a slightly munged query), but I have a UniVerse back end and while they are very similar, they are certainly differences.

Still, according to Rocket's Using UniDataSQL the Syntax that SHOULD work is what you appear to have tried first.

select ID, ID NewID from SYS_INSTALL_HISTORY_NF where 1=0

The "Column0" thing seems weird to me as that is most likely coming from the .net provider itself and not from UniData. It is like the query has determined how many columns there will be, but not yet assigned them names. If that were true I would expect the Query simply to fail and an exception to be thrown. You aren't doing any other exception catching on the actual call are you? Have you tried this with a non-id column?

You can get around all this by adding another dictionary item, but that seems heavy for something that ought to work. I would pitch it to support and see what they think.

Good Luck.

Answer 2

You may be able to accomplish what you want using the COL.HDG command

some examples:

SELECT ID COL.HDG "NewID" FROM SYS_INSTALL_HISTORY_NF WHERE 1=0
SELECT ID COL.HDG 'NewID' FROM SYS_INSTALL_HISTORY_NF WHERE 1=0