Error in code

[#1]

When i'm trying to run the following code, I'm getting lse_s not found. But lse_s is present in lpr_prod.rha. Can some one please help me with this.

Thank you in advance

Load

trim(lse_s) as id,

invo_s as [invoice num],

ptax_desc_s as tax_desc,

a_typ_s as type ,

aamt_p_d as amount;



SQL



select lse_s

     ,a. invo_s

     , '20' || substr(ptax_desc_s,1,2) tax_year

      , sum(case when a_typ_s = 'PRTX' then aamt_p_d end) property_tax

  from

   lpr_prod.rha a

     , lpr_prod.rhab b

where a.invo_s = b.invo_s    

  and a_typ_s = 'PRTX' 

group by lse_s

     ,a. invo_s

     , ptax_desc_s;

[petter]

By looking at the correspondence between the SQL and the LOAD I would expect other error messages than the one you say you get.

The LOAD should only be able to find columns with a name mentioned in the column-list part of the SELECT.

The three last columns that the LOAD refers to is ptax_desc, a_typ_s and aamt_p_d which very well might be present in the underlying SQL tables but is not mention in the column-list part of the SQL ... however the lse_s is present there.

Try to fix the reference to the three missing columns and see if the error message disappears ..

[maxgro]

1) missing fields in the sql part

2) maybe case (it seems an oracle db)

run this (reload) and in table viewer (ctrl+t) look at case of fields

maybe they are uppercase and not lowercase as in your preceding load

SQL
select lse_s
     ,a. invo_s
     , '20' || substr(ptax_desc_s,1,2) tax_year
      , sum(case when a_typ_s = 'PRTX' then aamt_p_d end) property_tax
  from
   lpr_prod.rha a
     , lpr_prod.rhab b
where a.invo_s = b.invo_s    
  and a_typ_s = 'PRTX' 
group by lse_s
     ,a. invo_s
     , ptax_desc_s;

[#4]

Thank you Grossi, the column names should have been in upper case.