Help with a LOAD followed by Oracle ODBC SQL SELECT

[#1]

Hi,

I'm having difficulty getting this to work.

It keeps coming up with a message like "field <portfolio> not found". If I move, say, the consumerid expression first, it says "field <consumerid> not found". Is there anything obvious I'm doing wrong here?

The SQL expression works just fine in Oracle, but for some reason QlikView doesn't like the combination of the LOAD and SQL SELECT

Help!

---

CM_Isolations:

LOAD
portfolio,
mpr1, lastbillrd1, lastbilldt1,
TEXT(consumerid) AS consumerid,
TEXT(meter_sn1) AS meter_sn1,
meter_location1, meter_size1,
site_address1,
site_address2,
site_address3,
site_address4,
site_postcode,
mpr2, lastbillrd2, lastbilldt2,
TEXT(meter_sn2) AS meter_sn2,
meter_location2, meter_size2,
mpr3, lastbillrd3, lastbilldt3,
TEXT(meter_sn3) AS meter_sn3,
meter_location3, meter_size3,
mpr4, lastbillrd4, lastbilldt4,
TEXT(meter_sn4) AS meter_sn4,
meter_location4, meter_size4,
mpr5, lastbillrd5, lastbilldt5,
TEXT(meter_sn5) AS meter_sn5,
meter_location5, meter_size5,
mpr6, lastbillrd6, lastbilldt6,
TEXT(meter_sn6) AS meter_sn6,
meter_location6, meter_size6;

// LOAD to ensure certain fields come out as string

SQL SELECT DISTINCT
consumer.portfolio AS portfolio,
ml_outer1.mp_refno AS mpr1, ml_outer1.lastbillrd AS lastbillrd1, ml_outer1.lastbilldt AS lastbilldt1,
consumer.consumerid AS consumerid,
ml_outer1.meter_sn AS meter_sn1,
mri1.m_loc_desc AS meter_location1, mri1.meter_model AS meter_size1,
TRIM(TRIM(mprdb1.sub_build) || ' ' || TRIM(mprdb1.build_name) || ' ' || TRIM(mprdb1.build_no) || ' ' || TRIM(mprdb1.princ_str))
AS site_address1,
TRIM(TRIM(mprdb1.dep_street) || ' ' || TRIM(mprdb1.dbl_dep_lc)) AS site_address2,
TRIM(mprdb1.dep_local) AS site_address3,
TRIM(mprdb1.post_town) AS site_address4,
TRIM(TRIM(mprdb1.out_code) || ' ' || TRIM(mprdb1.in_code)) AS site_postcode,
ml_outer2.mp_refno AS mpr2, ml_outer2.lastbillrd AS lastbillrd2, ml_outer2.lastbilldt AS lastbilldt2,
ml_outer2.meter_sn AS meter_sn2,
mri2.m_loc_desc AS meter_location2, mri2.meter_model AS meter_size2,
ml_outer3.mp_refno AS mpr3, ml_outer3.lastbillrd AS lastbillrd3, ml_outer3.lastbilldt AS lastbilldt3,
ml_outer3.meter_sn AS meter_sn3,
mri3.m_loc_desc AS meter_location3, mri3.meter_model AS meter_size3,
ml_outer4.mp_refno AS mpr4, ml_outer4.lastbillrd AS lastbillrd4, ml_outer4.lastbilldt AS lastbilldt4,
ml_outer4.meter_sn AS meter_sn4,
mri4.m_loc_desc AS meter_location4, mri4.meter_model AS meter_size4,
ml_outer5.mp_refno AS mpr5, ml_outer5.lastbillrd AS lastbillrd5, ml_outer5.lastbilldt AS lastbilldt5,
mri5.m_loc_desc AS meter_location5, mri5.meter_model AS meter_size5,
ml_outer5.meter_sn AS meter_sn5,
ml_outer6.mp_refno AS mpr6, ml_outer6.lastbillrd AS lastbillrd6, ml_outer6.lastbilldt AS lastbilldt6,
ml_outer6.meter_sn AS meter_sn6,
mri6.m_loc_desc AS meter_location6, mri6.meter_model AS meter_size6
FROM ESI_USER.consumer
LEFT JOIN (SELECT * FROM ESI_USER.v_meter_latest_ranked WHERE rnk = 1) ml_outer1
ON consumer.consumerid = ml_outer1.consumerid
LEFT JOIN ESI_USER.v_mridata_latestbympr mri1
ON ml_outer1.mp_refno = mri1.mpr
LEFT JOIN ESI_USER.mprdbase mprdb1
ON ml_outer1.mp_refno = TO_NUMBER(mprdb1.mpo_ref)
LEFT JOIN (SELECT * FROM ESI_USER.v_meter_latest_ranked WHERE rnk = 2) ml_outer2
ON consumer.consumerid = ml_outer2.consumerid
LEFT JOIN ESI_USER.v_mridata_latestbympr mri2
ON ml_outer2.mp_refno = mri2.mpr
LEFT JOIN (SELECT * FROM ESI_USER.v_meter_latest_ranked WHERE rnk = 3) ml_outer3
ON ml_outer1.consumerid = ml_outer3.consumerid
LEFT JOIN ESI_USER.v_mridata_latestbympr mri3
ON ml_outer3.mp_refno = mri3.mpr
LEFT JOIN (SELECT * FROM ESI_USER.v_meter_latest_ranked WHERE rnk = 4) ml_outer4
ON consumer.consumerid = ml_outer4.consumerid
LEFT JOIN ESI_USER.v_mridata_latestbympr mri4
ON ml_outer4.mp_refno = mri4.mpr
LEFT JOIN (SELECT * FROM ESI_USER.v_meter_latest_ranked WHERE rnk = 5) ml_outer5
ON consumer.consumerid = ml_outer5.consumerid
LEFT JOIN ESI_USER.v_mridata_latestbympr mri5
ON ml_outer5.mp_refno = mri5.mpr
LEFT JOIN (SELECT * FROM ESI_USER.v_meter_latest_ranked WHERE rnk = 6) ml_outer6
ON consumer.consumerid = ml_outer6.consumerid
LEFT JOIN ESI_USER.v_mridata_latestbympr mri6
ON ml_outer6.mp_refno = mri6.mpr
ORDER BY consumer.consumerid;

[markmccoid · Accepted solution]

I've found that when using Oracle, it returns all field names in uppercase regardless of the case used in the select statement. So I have made it a policy to always write my Oracle select statements in uppercase.

In your example if you just change your load statement reference to the field names in your select statement to upper case it should work.

[#3]

Thanks, that's exactly the issue.

[#4]

This is true

For any reason if someone need to to change the case of field names do it in QLikview load

[ItemList]:

Load

I_PART,

I_PART_HASH as I_PART_Hash;

SQL Select

I_PART,

Row_Number() Over() as I_PART_Hash

From(Select Distinct I_PART from PART_SUMMRY ) ;

Eventhough I used I_PART_Hash in Mixed case in SQL select  I have to refer to it as uppercase in the QLikview load following the SQL