SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Below is the Oracle query needs to be converted to ANSI SQL

chanduboNov 1 2019 — edited Nov 1 2019

Here is SQL .

TB_CI_OZQ_DTM_ARGMT_GRP.YR_MTH_NBR = decode(substr(to_char(TB_CI_OZQ_DTM_ARGMT_GRP.MOST_RECNT_PRCS_MTH_END_DTE,'YYYYMM'),5,2),'12',to_char(TB_CI_OZQ_DTM_ARGMT_GRP.MOST_RECNT_PRCS_MTH_END_DTE,'YYYYMM'),

to_char(to_number(substr(to_char(TB_CI_OZQ_DTM_ARGMT_GRP.MOST_RECNT_PRCS_MTH_END_DTE,'YYYYMM'),1,4))-1) || '12')

I have tried this as below

TB_CI_OZQ_DTM_ARGMT_GRP.YR_MTH_NBR = Case When (substr(to_char(TB_CI_OZQ_DTM_ARGMT_GRP.MOST_RECNT_PRCS_MTH_END_DTE,'YYYYMM'),5,2)='12')

then to_char(TB_CI_OZQ_DTM_ARGMT_GRP.MOST_RECNT_PRCS_MTH_END_DTE,'YYYYMM')

Else

to_char(cast(substr(to_charTB_CI_OZQ_DTM_ARGMT_GRP.MOST_RECNT_PRCS_MTH_END_DTE,'YYYYMM'),1,4) as number)-1) || '12'

End

Please let me know what is the ANSI SQL function we can replace with to_char

Added on Nov 1 2019

6 comments

1,016 views