Papa_Don 31 Posting Pro in Training


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:

                            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,

        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.


Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.