Field cannot find error?

[baylor2016]

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')

[Anonymous · Accepted solution]

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')

[baylor2016]

Thank you for the trick!

[Anonymous]

No worries, glad you are sorted.

[baylor2016]

There is another question. The script below does now work for the newly renamed field in load statement.

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

The len(EncounterAge)=0 already return false, since I cannot get Null even though there are a lot of blanks in this field.

Do I need start another thread?

Thanks.

[Anonymous]

Probably best to start a new thread with some sample data.