topic Re: SQL Help in QlikView

SQL Help

danielnevitt — Fri, 06 Nov 2015 12:59:44 GMT

Hi,

I am having issues with the below SQL. Any help with pointing me in the direction of the obvious mistake I have made will be much appreciated.

Regards,

Daniel

l.location_cd as trade_location_cd,
    l.location_name,
    l.[reference] as trade_location_reference,
    tp.*,
    c.*
FROM tempest_tier1.p_edw_trade_physical('%','%','%','%','%','%','Create Dt','2015-01-01','2050-01-01','%','Today','Trade',0) tp
left outer join tempest_tier1.v_LOCATION l on (l.location_cd = tp.trade_location_cd)
left outer join tempest_tier1.p_dcf_acl_cost('%','%','%','%','%','%','%','Today','Trade','End Of Day',0,'Create Dt','2015-01-01','2050-01-01') c on (tp.strategy_num = c.strategy_num)
where (tp.co_cd in ('A')
       or l.[reference] in ('GBR')
       or tp.trade_cmdty_cd in ('Crude')
       or tp.trade_location_cd in ('B'))
and c.cost_cd not in ('Freight')

Re: SQL Help

Anonymous — Fri, 06 Nov 2015 13:01:15 GMT

What error you are getting?

Re: SQL Help

danielnevitt — Fri, 06 Nov 2015 13:03:56 GMT

Hi,

It say ODBC read failed

Regards,

Daniel

Re: SQL Help

Anonymous — Fri, 06 Nov 2015 13:09:44 GMT

that may be becoz of you are using wrong credentials...or connection timeout like that..

Re: SQL Help

maxgro — Fri, 06 Nov 2015 13:16:26 GMT

Try to reduce the number of field (remove tp.*, c.*)

Sometimes I got the same error (ODBC read failed) for duplicated field names

Re: SQL Help

danielnevitt — Fri, 06 Nov 2015 13:43:30 GMT

Hi,

I tried to reduce the fields and I still get the same error. I think it might be related to the 'left outer join'?

Regards,

Daniel

Re: SQL Help

Anonymous — Fri, 06 Nov 2015 13:45:24 GMT

if it is SQL query, remove this Outer keyword

Re: SQL Help

Anonymous — Fri, 06 Nov 2015 13:51:01 GMT

Break your query and try to reload it one by one, that might be helpful for you to know where is the problem?

Re: SQL Help

danielnevitt — Fri, 06 Nov 2015 14:47:02 GMT

I tried to break the query down and all parts work ok.

The left outer join works between tempest_tier1.p_edw_trade_physical and tempest_tier1.v_LOCATION. However the script fails when trying to left outer join to tempest_tier1.p_dct_acl_cost

Regards,

Daniel