2665: Invalid Date error in Teradata 15.10.01.01

General
Enthusiast

2665: Invalid Date error in Teradata 15.10.01.01

I am trying to pull four columns using following query but getting invalid date error.

Does anyone know a solution for it?

Is it something to do with using between?? if so then with what should i replace it?

 

thank you,

 

Format:HTML Format Version:1.0 StartHTML: 165 EndHTML: 20690 StartFragment: 314 EndFragment: 20658 StartSelection: 314 EndSelection: 314SyntaxEditor Code Snippet

CREATE MULTISET VOLATILE TABLE MLR_BY_NTWRK AS (
 SELECT 
                     PRER_I.IPA AS "IPA",
                     PRER_G.GRP AS "GRP",
                     **bleep**.PRV_NAME,
                     **bleep**.NPI,SUM("Member Months") AS "Member Months",SUM(Premium) AS "Premium",SUM("Medical Expense") AS "Medical Expense",SUM (IP) AS "IP",SUM(OP) AS "OP",SUM(**bleep**) AS "**bleep**",SUM(SPEC) AS "Specialist", SUM("**bleep** CAP") AS "**bleep** Capitation",SUM("Other PHY CAP") AS "Other Physician Cap",
     SUM("NURSING FACILITY") AS "Nursing Facility", SUM(RX) AS "Rx", SUM("ANCILLIARY SERVICES") AS "Ancillary Services", SUM("OTHER MEDICAL") AS "Other Medical",

     CASE WHEN SUM("Rg 5 Rev")=0 THEN 0 ELSE SUM("Rg 5 Exp")/SUM("Rg 5 Rev") END as "Rg 5 MLR",
     CASE WHEN SUM("Rg 6 Rev")=0 THEN 0 ELSE SUM("Rg 6 Exp")/SUM("Rg 6 Rev")  END as "Rg 6 MLR",
     CASE WHEN SUM("Rg 7 Rev")=0 THEN 0 ELSE SUM("Rg 7 Exp")/SUM("Rg 7 Rev") END as "Rg 7 MLR",
     CASE WHEN SUM("Rg 11 Rev")=0 THEN 0 ELSE SUM("Rg 11 Exp")/SUM("Rg 11 Rev") END as "Rg 11 MLR",
     CASE WHEN SUM("NON-MMA Rev")=0 THEN 0 ELSE SUM("NON-MMA Exp")/SUM("NON-MMA Rev")  END as "NON-MMA MLR"                    

                     
                     FROM  PCP_TBL_CMPRSS **bleep**
 
 LEFT JOIN PROD_MME.CMC_PRPR_PROV PRPR
  ON **bleep**.SRC_PRV_ID = PRPR.PRPR_ID
  
 LEFT JOIN  PROVIDER_GRP PRER_G
      ON **bleep**.SRC_PRV_ID = PRER_G.SRC_SYS_PROVIDER_NBR
      AND     **bleep**.SVC_DT between  PRER_G.GRP_EFF  AND PRER_G.TERM_DT 

          
LEFT JOIN PROVIDER_IPA PRER_I
      ON **bleep**.SRC_PRV_ID = PRER_I.SRC_SYS_PROVIDER_NBR
      AND **bleep**.SVC_DT between  PRER_I.IPA_EFF  AND PRER_I.TERM_DT 


GROUP BY 1,2,3,4--,5,6,7--,8,9,10,11
--ORDER BY 1,2,3,4 desc)WITH DATA
ON COMMIT PRESERVE ROWS;

 

1 REPLY
rjg
Supporter

Re: 2665: Invalid Date error in Teradata 15.10.01.01

Stemetha,

 

the between is valid, 

please share the DDL for your tables