CODEWITHSUNDEEP
sqloracle-database
tomaz
tomaz

Reputation: 522

define a variable as a result of a query

I have a variable date_from that I can use in my queries:

define date_from = '01.11.2019';

Is it possible to define a new variable as a result of a query? The following statement doesn't work:

define month_from = (select month_yyyymm from t_calendar where date_orig = '&date_from');

I'm using Oracle SQL Developer and I don't want to go into PL/SQL.

Upvotes: 1

Views: 372

Answers (1)

user5683823
user5683823

Reputation:

It is not possible to do what you want, directly. Note also that substitution variables (those created / assigned to with the define command and called with a leading &) are a SQL*Plus concept; they are pre-processed by the client software (in your case SQL Developer, which understands and honors most of, even though not all of, SQL*Plus).

You can do almost that, though, with the SQL*Plus option new_value to the column command. It goes something like this (not tested since you didn't provide sample data):

define date_from = '01.11.2019'
column month_yyyymm new_value month_from
select month_yyyymm from t_calendar where date_orig = '&date_from';

This is it - at this point the variable month_from stores the value of month_yyyymm returned by your query. Note that the first two commands are SQL*Plus (scripting) commands; only the last statement, the select, is ever seen by the database itself.

Upvotes: 3

Related Questions