CODEWITHSUNDEEP
sqloracle-databaseselect
tryingToLearn
tryingToLearn

Reputation: 17

Query to retrieve custom columns from select statement

Let's say that within the customers table there are the following columns : customer_id and customer_name.

From a select statement that I am trying to perform, I need to add in a custom sysdate column that is not associated with the customer's table and give it a value. I tried using an inner query, but that only led to the creation of the current_date column but it has no value.

Example -

SELECT CUSTOMER_ID, CUSTOMER_NAME, (SELECT SYSDATE FROM DUAL) AS CURRENT_DATE
FROM customers

OUTPUT

CUSTOMER_ID || CUSTOMER_NAME || CURRENT_DATE
-----------    -------------    ------------
1              JOHN DOE          
2              JANE DOE

The expected output I am trying to get is down below - Could anyone please provide me a solution to this problem?

OUTPUT

CUSTOMER_ID || CUSTOMER_NAME || CURRENT_DATE
-----------    -------------    ------------
1              JOHN DOE          08/07/2020
2              JANE DOE          08/07/2020

Upvotes: 0

Views: 482

Answers (2)

gelfiusm
gelfiusm

Reputation: 66

You can get the current date using SYSDATE, so you could do something like

SELECT 
  CUSTOMER_ID, 
  CUSTOMER_NAME,
  to_char(cast(SYSDATE as date), 'MM/DD/YYYY') AS cust_date
FROM 
  customers

which should give you the output formatted how you want.

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142743

In my database, it is as simple as

SQL> select empno, ename, sysdate
  2  from emp
  3  where rownum < 4;

     EMPNO ENAME      SYSDATE
---------- ---------- -------------------
      7369 SMITH      07.08.2020 22:13:22
      7499 ALLEN      07.08.2020 22:13:22
      7521 WARD       07.08.2020 22:13:22

SQL>

Your query works as well (although, why making it more complex than it should be?):

SQL> select empno, ename, (select sysdate from dual) current_date
  2  from emp
  3  where rownum < 4;

     EMPNO ENAME      CURRENT_DATE
---------- ---------- -------------------
      7369 SMITH      07.08.2020 22:14:35
      7499 ALLEN      07.08.2020 22:14:35
      7521 WARD       07.08.2020 22:14:35

SQL>

Why didn't you get the result? No idea.

Upvotes: 2

Related Questions