0

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.

Domnic
  • 3,817
  • 9
  • 40
  • 60
  • 2
    Where in the Oracle manual did you find that syntax? –  Sep 29 '16 at 13:16
  • I'm new to Oracle I just refer some website ......I need the syntax exact same like sql DECLARE statement – Domnic Sep 29 '16 at 13:18
  • 1
    Possible duplicate of [How to declare variable and use it in the same SQL script? (Oracle SQL)](http://stackoverflow.com/questions/3564283/how-to-declare-variable-and-use-it-in-the-same-sql-script-oracle-sql) – hemalp108 Sep 29 '16 at 13:24

3 Answers3

1

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
FreeMan
  • 1,417
  • 14
  • 20
1

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
Boneist
  • 22,910
  • 1
  • 25
  • 40
0

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;
XING
  • 9,608
  • 4
  • 22
  • 38