Field Aliasing in queries, nzsql

问题:

I'm working in Netezza -- or, you know, pure data for Analytics -- nzsql, but I think this is an ANSI SQL question. The question is so basic, I don't even know how to search for it.

CREATE TEMPORARY TABLE DEMO1 AS SELECT 'SMORK' AS SMORK, 'PLONK' AS PLONK, 'SPROING' AS SPROING;

SELECT SMORK AS PLONK, PLONK, SPROING AS CLUNK, CLUNK
FROM DEMO1;

This returns 'SMORK, PLONK, SPROING, SPROING', which is to say, the query is fine reusing the CLUNK alias, but the PLONK alias is overwritten by the column from the source table. Now, if I really wanted the column from the source table, I could write SELECT SMORK AS PLONK, DEMO1.PLONK et c, but I don't know how to specify that I would prefer the alias I've defined earlier in same the SELECT clause.

Does anybody know a way?

回答1:

In Netezza, when selecting a column, Netezza will search for table column first, and then alias.

Example: Suppose we have the following statements:

CREATE TEMPORARY TABLE EMPLOYEES AS 
SELECT 1001 AS EMPLOYEE_ID
      ,'Alex' AS FIRST_NAME
      ,'Smith' AS LAST_NAME
      ,'Alex J. Smith' AS FULL_NAME;

SELECT 
     EMPLOYEE_ID
    ,FIRST_NAME
    ,LAST_NAME
    ,LAST_NAME||', '||FIRST_NAME AS FULL_NAME
    ,'My full name is :'||FULL_NAME AS DESCRIPTION
  FROM EMPLOYEES;

It will return

EMPLOYEE_ID  FIRST_NAME  LAST_NAME   FULL_NAME      DESCRIPTION
   1001        Alex        Smith     Smith, Alex    My full name is :Alex J. Smith

Notice in DESCRIPTION, the FULL_NAME value is picked from table column, not from alias.

If you want DESCRIPTION column use value from alias FULL_NAME, you can do it in two steps:

Step 1. Create a sub-query includes all columns you want. For all alias names you want to reuse, you need to name them as names not exist in any table columns on your FROM clause;

Step 2. SELECT only column you want from the subquery.

CREATE TEMPORARY TABLE EMPLOYEES AS SELECT 1001 AS EMPLOYEE_ID, 'Alex' AS FIRST_NAME, 'Smith' AS LAST_NAME, 'Alex J. Smith' AS FULL_NAME;

WITH EMPLOYESS_TMP AS (
SELECT 
     EMPLOYEE_ID
    ,FIRST_NAME
    ,LAST_NAME
    ,LAST_NAME||', '||FIRST_NAME AS FULL_NAME2
    ,FULL_NAME2 AS FULL_NAME
    ,'My full name is :'||FULL_NAME2 AS DESCRIPTION
  FROM EMPLOYEES)
SELECT 
     EMPLOYEE_ID
    ,FIRST_NAME
    ,LAST_NAME
    ,FULL_NAME
    ,DESCRIPTION
 FROM EMPLOYESS_TMP;   

This will return what you want:

EMPLOYEE_ID  FIRST_NAME  LAST_NAME   FULL_NAME      DESCRIPTION
   1001        Alex        Smith    Smith, Alex     My full name is :Smith, Alex

回答2:

Just change the order of your columns. Netezza tries to use your alias so you can either rename the column or change the order.

SELECT SMORK AS PLONK, PLONK, CLUNK, SPROING AS CLUNK
FROM DEMO1;