Translate a CASE statement into a Stage Variable

[dmotoc]

Hi there

I have the following Case statement that I need to translate in a Stage Variable

Code: Select all

case
            when substr(in_date_numeric, 7, 2) between '01' and
                
                 to_char(last_day(to_date(substr(in_date_numeric, 1, 6),
                                          'YYYYMM')),
                         'DD') 
             then
             to_date(in_date_numeric, 'YYYYMMDD')      
            else
             null        
          end      
         else
          null  
       end in_date

So far I manage to get something like that:

If string(in_date_numeric, 1, 4) between '0001' and '9999' and string(in_date_numeric, 5, 2) between '01' and '12'
then
(If string(in_date_numeric, 7, 2) between '01' and char(last_day(iconv(string(in_date_numeric, 1, 6),'YYYYMM')),'DD')
then iconv(in_date_numeric, 'YYYYMMDD') else null )
else null

I am not sure how the function last_day from Oracle will work in DataStage. Is something equivalent that can be used?

Thanks for your help

[ray.wurlod]

Code: Select all

svYear          Left(InLink.TheDate, 4)
svIsLeapYear    Mod(svYear,4) = 0 And (Mod(svYear,100) <> 0 Or Mod(svYear,400) = 0)
svFebDays       If svIsLeapYear Then 29 Else 28
svMonthDays     "31|" : svFebDays : "|31|30|31|30|31|31|30|31|30|31"
svMonthNo       InLink.TheDate[5,2]
svLastDay       Field(svMonthDays, "|", svMonthNo, 1)

[dmotoc]

it works, thanks!