Hi,
We just went live on Oracle a couple of weeks ago. I have a legacy process that includes running a script that was coded for Sybase. I have most of it converted to Oracle, but I'm having trouble with the Age field (it's the last piece I need to get working). I thought about just including the Age piece... then thought to include the entire script for context if nothing else.
Thanks in advance for the assist!
-Denise

Current legacy code

SELECT DISTINCT 
   meme.MEME_MEDCD_NO,
   meme.MEME_BIRTH_DT,
   AGE =
      CASE WHEN 
         (
         month(convert(datetime, meme.MEME_BIRTH_DT, 103))*100)+
           day(convert(datetime, meme.MEME_BIRTH_DT, 103)) - 
              ((month(getdate())*100)+day(getdate())) <= 0 THEN 
              DATEDIFF(YEAR,convert(datetime, meme.MEME_BIRTH_DT, 103),getdate())</b>
      ELSE 
         DATEDIFF(YEAR,convert(datetime, meme.MEME_BIRTH_DT, 103),getdate())-1
      END,
   sbsb.SBSB_ID, 
   mepe.MEPE_EFF_DT, 
   mepe.MEPE_TERM_DT, 
   mepe.MEPE_ELIG_IND, 
   mepe.CSPI_ID,
   sbad.SBAD_COUNTY AS 'Member_County',
   pdpd.LOBD_ID
FROM 
   dbo.CMC_MEME_MEMBER meme
   INNER JOIN dbo.CMC_MEPE_PRCS_ELIG mepe ON
      mepe.MEME_CK =meme.MEME_CK
   INNER JOIN dbo.CMC_SBSB_SUBSC     sbsb ON
      sbsb.SBSB_CK = meme.SBSB_CK
   INNER JOIN CMC_PDPD_PRODUCT       pdpd ON 
      mepe.PDPD_ID = pdpd.PDPD_ID
   INNER JOIN CMC_SBAD_ADDR          sbad ON
      sbsb.SBSB_CK = sbad.SBSB_CK AND
      sbsb.SBAD_TYPE_MAIL = sbad.SBAD_TYPE
WHERE 
   mepe.GRGR_CK IN (1,3,8)       AND
   mepe.MEPE_ELIG_IND = 'Y'      AND
   mepe.MEPE_EFF_DT  <= '09/01/2020' AND  -- Match file date
   mepe.MEPE_TERM_DT >= '09/01/2020' AND  -- Match file date
   meme.MEME_MEDCD_NO IN   
   (   
   )

trunc (
 (to_number ( to_char ( sysdate, 'YYYYMMDD' ) ) -
  to_number ( to_char ( birth_dt, 'YYYYMMDD' ) )
 ) / 10000
) calculated_age