CODEWITHSUNDEEP
oracle-databaseoracle11g
Domnic
Domnic

Reputation: 3867

Define date variable in Oracle

How to define date variable and assign that variable from the table in oracle?

I tried below

DEFINE EXTRACTION_DATE = SELECT DATA_EXTRACTION_DATE FROM tbl1

SELECT PA.PERSON_ID,A.PERIOD_END_DATE,PA.OPT_OUT_OF_PLAN_DATE,A.TERMINATION_DATE,
        CASE WHEN A.PERIOD_END_DATE <= ADD_MONTHS(TRUNC(&EXTRACTION_DATE), -3) THEN 'Y'  ELSE 'N' END 
    FROM 
    tbl2

it throwing invalid statement error.

its not duplicate of another. Because I need to get variable from the table not by static variable.

Upvotes: 0

Views: 8717

Answers (3)

XING
XING

Reputation: 9886

Try something like:-

declare

var1 <dtatatype same as your table column>;
var2 <dtatatype same as your table column>;    
var3 <dtatatype same as your table column>;  
var4 <dtatatype same as your table column>;    
var5 <dtatatype same as your table column>;

var_dt date;

begin


SELECT EXTRACTION_DATE 
into var_dt
FROM tbl1;


SELECT PA.PERSON_ID,
       A.PERIOD_END_DATE,
       PA.OPT_OUT_OF_PLAN_DATE,
       A.TERMINATION_DATE,
       CASE WHEN A.PERIOD_END_DATE <= ADD_MONTHS(var_dt), -3) 
        THEN 'Y'  
       ELSE 'N' END
     into
       var1,
       var2,
       .
       .
       var5
    FROM     tbl2;

end;

Upvotes: 0

Boneist
Boneist

Reputation: 23578

I'm going to assume that this is required for some sort of script. If so, you can do this using the COLUMN command and its new_value parameter in SQL*Plus:

SQL> column my_val new_value my_val_subst_param
SQL> define my_val_subst_param
Symbol my_val_subst_param is UNDEFINED

SQL> select 'hello!' my_val from dual;
MY_VAL
------
hello!

SQL> select '&my_val_subst_param' some_val from dual;
'HELLO!'
--------
hello!

I highly recommend you read all of this article, and specifically this section.

ETA: Bear in mind that if you use this method, DATEs are output as strings, so you will have to ensure that you do the appropriate conversion in your select statement, eg.:

SQL> column dt new_value dt_val
SQL> select to_char(sysdate, 'dd/mm/yyyy') dt from dual;
DT
----------
29/09/2016

SQL> define dt_val;
DEFINE DT_VAL           = "29/09/2016" (CHAR)

SQL> select * from dual where trunc(sysdate) = to_date('&dt_val', 'dd/mm/yyyy');
DUMMY
-----
X

Upvotes: 1

FreeMan
FreeMan

Reputation: 1457

I dont fully understand what you are tyring to do but as far as I understand you can set a variable such a way as follows

1 - ) if you write a procedure,

declare
EXTRACTION_DATE date;

begin
  SELECT DATA_EXTRACTION_DATE into EXTRACTION_DATE FROM tbl1;
end;

2 - ) If your data reference to a table, you can user cursor

declare
cursor crs is SELECT * into EXTRACTION_DATE FROM tbl1;

then use crs in for loop

3 - ) You can write upper query in you second query.

SELECT PA.PERSON_ID,A.PERIOD_END_DATE,PA.OPT_OUT_OF_PLAN_DATE,A.TERMINATION_DATE,
        CASE WHEN A.PERIOD_END_DATE <= ADD_MONTHS(TRUNC(SELECT DATA_EXTRACTION_DATE FROM tbl1), -3) THEN 'Y'  ELSE 'N' END 
    FROM 
    tbl2

Upvotes: 1

Related Questions