Group,
I'm extracting some month and year values from dates. I need to ensure they are NUMBERS as I'm going to multiply, add and subtract. I'm assuming I need to DECLARE these as NUMBERs. However I'm not sure how to do this correctly as I'm getting errors preventing me to run this. Here's my SQL code I've written so far:
DECLARE
Period_Out_Month_Number NUMBER;
Period_Out_Year_Number NUMBER;
Open_Date_Month_Number NUMBER;
Open_Date_Year_Number NUMBER;
Select Distinct b.prop_master_id,
a.periods_out_desc,
b.Open_Date,
Case
When (SUBSTR(a.periods_out_desc,0,3)) in 'Jan' then 1
when (SUBSTR(a.periods_out_desc,0,3)) in 'Feb' then 2
when (SUBSTR(a.periods_out_desc,0,3)) in 'Mar' then 3
when (SUBSTR(a.periods_out_desc,0,3)) in 'Apr' then 4
when (SUBSTR(a.periods_out_desc,0,3)) in 'May' then 5
when (SUBSTR(a.periods_out_desc,0,3)) in 'Jun' then 6
when (SUBSTR(a.periods_out_desc,0,3)) in 'Jul' then 7
when (SUBSTR(a.periods_out_desc,0,3)) in 'Aug' then 8
when (SUBSTR(a.periods_out_desc,0,3)) in 'Sep' then 9
when (SUBSTR(a.periods_out_desc,0,3)) in 'Oct' then 10
when (SUBSTR(a.periods_out_desc,0,3)) in 'Nov' then 11
when (SUBSTR(a.periods_out_desc,0,3)) in 'Dec' then 12
end as Period_Out_Month_Number,
(SUBSTR(a.periods_out_desc,-4,4)) Period_Out_Year_Number,
EXTRACT(MONTH FROM b.Open_Date) Open_Date_Month_Number,
EXTRACT(YEAR FROM b.Open_Date) Open_Date_Year_Number
from dmart.F_ST_PERIOD_WKLY a,
crmmart.d_prop b
where b.prop_master_id in ('123') and
(SUBSTR(a.periods_out_desc,-4,4) in ('2008', '2009', '2010', '2011', '2012', '2013', '2014', '2015')) and
(SUBSTR(a.periods_out_desc,0,3) in ('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'))
I've tried adding BEGIN after the DECLARE statements. However that still left me with an error message. My question is: How do I correctly write my code to DECLARE that I want my "extractions" to be stored in the form of a number?
In advance, thanks for your help.
Don