Page 1 of 2
initializing column to NULL in SELECT
Posted: Fri Oct 23, 2015 10:00 pm
by Beg1nner
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?
Re: initializing column to NULL in SELECT
Posted: Sat Oct 24, 2015 4:17 pm
by Beg1nner
This problem occurs when I run the code in DBVisualizer. ON VSQL it runs fine.
Re: initializing column to NULL in SELECT
Posted: Sat Oct 24, 2015 4:18 pm
by JimKnicely
Not sure why you are getting an error.
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)
Works for me
Re: initializing column to NULL in SELECT
Posted: Sat Oct 24, 2015 4:23 pm
by JimKnicely
oops, I just saw your post:
This problem occurs when I run the code in DBVisualizer. ON VSQL it runs fine.
Is the problem with the space? column 2. Should it be column2?
Re: initializing column to NULL in SELECT
Posted: Mon Oct 26, 2015 7:48 pm
by Beg1nner
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"
Re: initializing column to NULL in SELECT
Posted: Tue Oct 27, 2015 4:54 pm
by JimKnicely
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)
Re: initializing column to NULL in SELECT
Posted: Sat Oct 31, 2015 10:02 am
by roger
What DbVisualizer version are you using? I've tested your SELECT with 9.2 and later versions and there is no variable window displayed.