5 Replies Latest reply: Feb 2, 2016 3:27 AM by Bill Markham RSS

    Field cannot find error?

    haijunw wang

      Hi,

       

      I am using the following script to load my data. The query runs ok in Server, but the load script always give me 'Field not found <EncounterAge>' error. It looks like the renamed or calculated fields cannot be used in load script. Is this true? If yes, what is the solution?

       

      Thanks

       

      Longmatch

       

       

       

      Encounters:

      LOAD *,

      if(BMI<=18.5, 'Underweight', if(BMI<=24.9, 'Normal', if(BMI<=29.9, 'Overweight', 'Obesity'))) as BMI2,

      if(BP_SYSTOLIC<140, '<140', '>=140') as Systolic,

      if(BP_DIASTOLIC<90, '<90', '>=90') as Diastolic,

      if(EncounterAge<45, '0-44', if(EncounterAge<65, '45-64',if(EncounterAge<75, '65-74', '75+'))) as Age2

      ;

      sql

      with DiabetesPatientList as (     

      select distinct a.pat_id

      from problem_list a          

      where a.icd9_code like '250.%'

      )

      select a.pat_id, a.PAT_ENC_CSN_ID,a.Contact_date, a.PCP_PROV_ID, a.FIN_CLASS_C,  a.VISIT_PROV_ID, a.VISIT_PROV_TITLE,

      a.BMI, a.BP_SYSTOLIC, a.BP_DIASTOLIC,  e.DEPARTMENT_NAME,

      trunc ((a.contact_date - b.birth_date)/365.25) as EncounterAge,

      c.Prov_name as PCP,

      d.prov_name as VisitPhysician,

      e.Department_NAME as Department,

      extract(Year from a.contact_date) as intYear, extract(Year from a.contact_date)*100+extract(month from a.contact_date) as IntYearMonth

      from pat_enc a 

      inner join patient b on a.pat_id = b.pat_id

      INNER JOIN DiabetesPatientList f on f.pat_id=a.pat_id

      LEFT JOIN clarity_SER c on a.PCP_PROV_ID =c.prov_id    

      left join clarity_ser d on d.prov_id = a.visit_prov_id

      left join clarity_dep e on a.DEPARTMENT_ID = e.DEPARTMENT_ID       

      where a.CONTACT_DATE >= to_date('01-Jan-2011', 'dd-mon-yy')

        • Re: Field cannot find error?
          Bill Markham

          Many varieties of sql will capitilise field names unless enclosed in double quotes.  Try this :

           

           

          Encounters:

          LOAD *,

          if(BMI<=18.5, 'Underweight', if(BMI<=24.9, 'Normal', if(BMI<=29.9, 'Overweight', 'Obesity'))) as BMI2,

          if(BP_SYSTOLIC<140, '<140', '>=140') as Systolic,

          if(BP_DIASTOLIC<90, '<90', '>=90') as Diastolic,

          if(EncounterAge<45, '0-44', if(EncounterAge<65, '45-64',if(EncounterAge<75, '65-74', '75+'))) as Age2

          ;

          sql

          with DiabetesPatientList as (

          select distinct a.pat_id

          from problem_list a      

          where a.icd9_code like '250.%'

          )

          select a.pat_id, a.PAT_ENC_CSN_ID,a.Contact_date, a.PCP_PROV_ID, a.FIN_CLASS_C,  a.VISIT_PROV_ID, a.VISIT_PROV_TITLE,

          a.BMI, a.BP_SYSTOLIC, a.BP_DIASTOLIC,  e.DEPARTMENT_NAME,

          trunc ((a.contact_date - b.birth_date)/365.25) as "EncounterAge",

          c.Prov_name as PCP,

          d.prov_name as VisitPhysician,

          e.Department_NAME as Department,

          extract(Year from a.contact_date) as intYear, extract(Year from a.contact_date)*100+extract(month from a.contact_date) as IntYearMonth

          from pat_enc a

          inner join patient b on a.pat_id = b.pat_id

          INNER JOIN DiabetesPatientList f on f.pat_id=a.pat_id

          LEFT JOIN clarity_SER c on a.PCP_PROV_ID =c.prov_id

          left join clarity_ser d on d.prov_id = a.visit_prov_id

          left join clarity_dep e on a.DEPARTMENT_ID = e.DEPARTMENT_ID  

          where a.CONTACT_DATE >= to_date('01-Jan-2011', 'dd-mon-yy')