Newbie question: Code line ends with column name null. How to add comment at the end of this line?

user_10309495 0
Viewed 85

Below we have simple query. 

select job_id, employee_id, null --comment here
from employees 
union all
select job_id, employee_id, end_date
from job_history
order by 2

  • When I'm trying to write some comment at the end of the first line, comment becomes part of the column alias.

  • Result of this example is column name of third column that looks like this: NULL--COMMENTHERE

  • Doesn't matter if I use -- or /* */ before comment.

I'm curious what is the reason for this?.

comments alias oracle sql
2 Answers

Lets strip the question back to the bare minimum and remove the UNION, the other columns and use the DUAL table:

SELECT DUMMY -- comment
FROM DUAL;

This outputs:

| DUMMY |
|-------|
|     X |

The statement is referencing a column in a table so that column's name will be used as the alias in the statement's output.

However, if we do not reference a column and use a literal:

SELECT NULL -- comment
FROM   DUAL;

This will output:

| NULL--COMMENT |
|---------------|
|             X |

and Oracle will generate the column name from the text of the SQL statement and the alias for the column in the output will be the text of the query between the SELECT and FROM keywords so the name will be NULL -- comment with the whitespace stripped out.

So SELECT NULL FROM DUAL would have a column name of NULL.

A slightly more complicated version with other literal values:

SELECT NULL --comment here
       , NULL /* other comment */,
       'LITERAL' -- third comment
       , 0 /* fourth comment */
FROM   DUAL

None of the generated columns reference a named column of a table so Oracle will generate the name from the SQL and outputs:

| NULL--COMMENTHERE | NULL/*OTHERCOMMENT*/ | 'LITERAL'--THIRDCOMMENT | 0/*FOURTHCOMMENT*/ |
|-------------------|----------------------|-------------------------|--------------------|
|            (null) |               (null) |                 LITERAL |                  0 |

and you can see the column names are generated from the SQL statement select clause list delimited by commas.

The SQL statement with a UNION will take the column names from the first SELECT clause (before the UNION) so:

SELECT NULL -- comment
FROM   DUAL
UNION ALL
SELECT DUMMY
FROM   DUAL;

Outputs:

| NULL--COMMENT |
|---------------|
|        (null) |
|             X |

The column name will be from the first statement and not from the second (below the UNION) which does reference a column.

If you want to override the default column name then use an alias:

SELECT NULL "NULL" --comment here
       , NULL AS other /* other comment */,
       'LITERAL' AS literal -- third comment
       , 0 AS "0" /* fourth comment */
FROM   DUAL

Outputs:

| NULL   | other  | LITERAL | 0 |
|--------|--------|---------|---|
| (null) | (null) | LITERAL | 0 |

and the aliases are used and the comments have been ignored.

user_15092640

One issue I encountered with Oracle, is that if you put a comment before stating FROM table, it breaks the syntax and throws an error "Expecting FROM keyword". So I would suggest you moved the comment to be after your FROM table statement, or use the /* comment */, or add a multi-line comment at some point, giving a more in-depth explanation instead of adding the comment directly to the select statement.

select job_id, employee_id, null --comment here
from employees 
union all
select job_id, employee_id, end_date
from job_history
order by 2

The above code is from your question, and breaks the Oracle syntax.

select job_id, employee_id, null from employees -- comment here
union all
select job_id, employee_id, end_date
from job_history
order by 2

This works.

SQL fiddle: http://sqlfiddle.com/#!4/7dd91d/19

And since you tagged oracle,

source: https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/comment.htm#LNPLS01308

user_75350140
Related