I would like to initialize a column to NULL in select statement.
E.g.
Select
column1,
NULL :: timestamp as test_column,
NULL :: int as s_no,
column 2
from tablename;
Error: Syntax error at or near "$1"
How should I overcome this?
initializing column to NULL in SELECT
Moderator: NorbertKrupa
Re: initializing column to NULL in SELECT
This problem occurs when I run the code in DBVisualizer. ON VSQL it runs fine.
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: initializing column to NULL in SELECT
Not sure why you are getting an error.
Works for me
Code: Select all
dbadmin=> create table tablename (column1 int, column2 int);
CREATE TABLE
dbadmin=> insert into tablename values (1, 2);
OUTPUT
--------
1
(1 row)
dbadmin=> Select
dbadmin-> column1,
dbadmin-> NULL :: timestamp as test_column,
dbadmin-> NULL :: int as s_no,
dbadmin-> column2
dbadmin-> from tablename;
column1 | test_column | s_no | column2
---------+-------------+------+---------
1 | | | 2
(1 row)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: initializing column to NULL in SELECT
oops, I just saw your post:
Is the problem with the space? column 2. Should it be column2?This problem occurs when I run the code in DBVisualizer. ON VSQL it runs fine.
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: initializing column to NULL in SELECT
I think this dbvis tool takes :: to be a variable and pops up a window. When i "continue", it gives an error. So annoying!
[SELECT - 0 row(s), 0.009 secs] [Error Code: 4856, SQL State: 42601] [Vertica][VJDBC](4856) ERROR: Syntax error at or near "$1"
[SELECT - 0 row(s), 0.009 secs] [Error Code: 4856, SQL State: 42601] [Vertica][VJDBC](4856) ERROR: Syntax error at or near "$1"
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: initializing column to NULL in SELECT
Maybe try the CAST function?
Code: Select all
dbadmin=> Select
dbadmin-> column1,
dbadmin-> cast(NULL AS timestamp) as test_column,
dbadmin-> cast(NULL AS int) as s_no,
dbadmin-> column2
dbadmin-> from tablename;
column1 | test_column | s_no | column2
---------+-------------+------+---------
(0 rows)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: initializing column to NULL in SELECT
What DbVisualizer version are you using? I've tested your SELECT with 9.2 and later versions and there is no variable window displayed.