topic Error in code in QlikView

Error in code

Thu, 23 Apr 2015 21:19:14 GMT

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;

Re: Error in code

petter — Thu, 23 Apr 2015 22:01:28 GMT

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 ..

Re: Error in code

maxgro — Thu, 23 Apr 2015 23:18:43 GMT

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;

Re: Error in code

Fri, 24 Apr 2015 18:23:45 GMT

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