CODEWITHSUNDEEP
amazon-redshift
Kranthi
Kranthi

Reputation: 11

How to use function column in select query

select now() as t1,t1;

error :- ERROR: 42703: column "t1" does not exist

Result should be :

select now() as t1,t1;

7/26/2016   7/26/2016

Upvotes: 0

Views: 267

Answers (1)

moertel
moertel

Reputation: 1559

Your query does not work because the moment you execute the select, the column t1 is not defined yet. Also, as you've tagged your question with Amazon Redshift, let me note that you won't be able to use now() but could use getdate() instead.

To solve your problem, you can either duplicate the now()/getdate() logic:

select getdate() as t1, getdate() as t1;

Or use it once from a subselect:

select t1, t1 from (select getdate() as t1);

Either one will give you:

              t1              |              t1
------------------------------+------------------------------
 2016-07-28 06:43:46.23357+00 | 2016-07-28 06:43:46.23357+00
(1 row)

If you need the output to look exactly like you stated in your question:

select
    t1
  , t1
from (
    select
        regexp_replace(
            to_char(CURRENT_DATE, 'MM/DD/YYYY') -- gives 07/26/2016
          , '0([0-9]{1}\/)'                     -- stores "7/" in $1
          , '$1'
        ) as t1
);

Giving:

    t1     |    t1
-----------+-----------
 7/28/2016 | 7/28/2016
(1 row)

Upvotes: 1

Related Questions