Quick PL/SQL Translator needed 1

[Legions]

I'm hoping some PL/SQL guru will take pity on me and help me with this translation.

I'm not a SQL guru but what little I do know has been mostly Microsoft-SQL server / Access queries.

I'm trying to translate a complex PL/SQL query.

The line I'm having trouble with is:

...
AND to_DATE(Decodes(sign(to_Number(to_Char(Res_Date,'YYYYMMDD'))-20021229),-1,decodes(sign(to_number(to_char(Close_date,'YYYYMMDD'))-20021228),1,to_char(nvl(res_date,close_date),'YYYYMMDD'),to_Char(Close_date,'YYYYMMDD')), to_char(close_date,'YYYYMMDD')),'YYYYMMDD')
Between Pay_start_date and Pay_end_date
AND
Pay_year || Pay_period_num > = '200501'


Ok I'm just wondering if someone can just help me lay this one out command by command.. I know how to use to_char but I guess I'm not getting how the to_number works, and why the saved date format is negative?

Any help is appreciated.

- L

 

[sem]

I think that this code was written by an author who didn't knew Orale either

If Decodes is just a typo rather than some unknown user-defined function then the same on pl/sql may be rewritten as:

--if your Oracle is 8 and below
...
   AND Decode(sign(Res_Date -
                   to_date('20021229', 'YYYYMMDD')),
              -1,
              decode(sign(Close_date -
                          to_date('20021228', 'YYYYMMDD')),
                     1,
                     res_date,
                     Close_date),
              close_date) BETWEEN Pay_start_date AND
       Pay_end_date
   AND Pay_year || Pay_period_num > = '200501'


--if your Oracle is 9 and above
...
   AND (CASE WHEN
        Res_Date < to_date('20021229', 'yyyymmdd') AND
        Close_date > to_date('20021228', 'yyyymmdd') THEN
        res_date ELSE close_date END) BETWEEN Pay_start_date AND Pay_end_date
   AND Pay_year || Pay_period_num > = '200501'

I should also mention that I replaced nvl(res_date, close_date) with res_date as the first condition forces Res_Date to be not null and nvl makes no sense.

Does this (especially 9+ version) look more clear to you?

Regards, Dima

http://seminihin.narod.ru

 

[Legions]

Thanks sem. That was my fault. I meant to write DECODE not decodes.

We are using Oracle 8.

That helps alot - thanks.

- L

 

[Legions]

I was also wondering another very simple stupid question :/

Using PL/SQL

When you see:

C.Version_Design = D.Version_design(+)

What does the (+) mean? I'm going to assume it is a weighted type of relationship.

Thanks!

-L

 

[sem]

This is an outer join. BTW PL/SQL is procedural extension of sql. Your code is not PL/SQL but rather Oracle dialect of pure SQL

Regards, Dima

http://seminihin.narod.ru