initializing column to NULL in SELECT

Moderator: NorbertKrupa

Beg1nner
Newbie
Newbie
Posts: 20
Joined: Fri Jul 24, 2015 8:46 pm

initializing column to NULL in SELECT

Post by Beg1nner » Fri Oct 23, 2015 10:00 pm

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?

Beg1nner
Newbie
Newbie
Posts: 20
Joined: Fri Jul 24, 2015 8:46 pm

Re: initializing column to NULL in SELECT

Post by Beg1nner » Sat Oct 24, 2015 4:17 pm

This problem occurs when I run the code in DBVisualizer. ON VSQL it runs fine.

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: initializing column to NULL in SELECT

Post by JimKnicely » Sat Oct 24, 2015 4:18 pm

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 :?
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: initializing column to NULL in SELECT

Post by JimKnicely » Sat Oct 24, 2015 4:23 pm

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?
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Beg1nner
Newbie
Newbie
Posts: 20
Joined: Fri Jul 24, 2015 8:46 pm

Re: initializing column to NULL in SELECT

Post by Beg1nner » Mon Oct 26, 2015 7:48 pm

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"

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: initializing column to NULL in SELECT

Post by JimKnicely » Tue Oct 27, 2015 4:54 pm

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

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

roger
Newbie
Newbie
Posts: 2
Joined: Sat Oct 31, 2015 10:00 am

Re: initializing column to NULL in SELECT

Post by roger » Sat Oct 31, 2015 10:02 am

What DbVisualizer version are you using? I've tested your SELECT with 9.2 and later versions and there is no variable window displayed.

Post Reply

Return to “New to Vertica SQL”