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

Did you check https://docs.rocketsoftware.com/nxt/gateway.dll/RKBnew20/unidata/previous%20versions/v7.2/unidata_sqlcommandsrefguide_v72.pdf for SELECT statement, alias is named as attribute_label here — Eralper, Sep 28 '18 at 06:08
I was using the .net provider. Changing it to odbc shows the column alias as you describe. — Jeff, Sep 28 '18 at 06:39
as much as i'd rather not use odbc, it'll have to do. if you want to write up an answer, i'll accept. thanks! — Jeff, Sep 28 '18 at 06:55
Could you pop in some of your .net provider code? Are you sending that as U2Command? — Van Amburg, Sep 28 '18 at 13:49

score 1 · Answer 1 · answered Jun 21 '19 at 14:47

1

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

answered Jun 21 '19 at 14:47

Lemon

36
4

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

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.

How to alias a column in Unidata query with U2 provider

2 Answers2