topic Re: SQL Load in QlikView

SQL Load

evansabres — Mon, 16 Oct 2017 18:24:29 GMT

I am attempting to load in data via a SQL connection. The connection is good and I can load in some queries, however, when I attempt to load in this query, the load is successful, however no data populates.

Is there an error in my script? Seems to get stuck after the 'SELECT' portion.

select

po.created_date, es.last_updated_date, e.event_code,

e.event_date, a.agency_code, bt.buyer_type_code, bt.description buyer_type_desc,

c.channel_code, c.description channel_desc, ps.price_scale_code,

ps.description price_scale_desc, es.price, sec.section_code, s.row_, s.seat_number

from

agency a, buyer_type bt, channel c, event e, event_category ec, event_seat es, order_line_item oli,

patron_order po, price_scale ps, seat s, section sec

where

oli.order_id = po.order_id

and oli.usage_event_id = e.event_id

and es.event_id = e.event_id

and es.order_id = po.order_id

and es.order_line_item_id = oli.order_line_item_id

and po.created_by_agency_id = a.agency_id

and a.channel_id = c.channel_id

and e.event_category_id = ec.event_category_id

and sec.section_id = es.section_id

and s.seat_id = es.seat_id

and es.buyer_type_id = bt.buyer_type_id

and ps.price_scale_id = es.price_scale_id

and trunc(po.created_date) >= ('01-JAN-2017')

and trunc(po.created_date) <= ('08-APR-2018')

and oli.market_type_code = 'P'

and oli.transaction_type_code in ( 'SA', 'ES', 'CS' ) -- sale, claimsale or exchangesale

and c.channel_code in ( 'BOXOFF', 'INTERNET', 'PHONE' )

and ec.event_category_code = 'HOCKEY'

and e.event_id >= '4461' and e.event_id <= '4502'

Re: SQL Load

tomasz_tru — Mon, 16 Oct 2017 19:16:34 GMT

It should be "SQL SELECT ..."

Re: SQL Load

evansabres — Mon, 16 Oct 2017 19:19:01 GMT

I have tried to add this in front of SELECT, however I still get the same result. My other queries do not have SQL in front of the select and they still produce data.

Re: SQL Load

tomasz_tru — Mon, 16 Oct 2017 19:24:36 GMT

Maybe missing ";" somewhere?

Have you checked this query returns any results in other ODBC client?

Tomasz

Re: SQL Load

vishsaggi — Mon, 16 Oct 2017 19:53:51 GMT

Does this script give any results when you run in SQL environment i mean Management Studio?

Re: SQL Load

evansabres — Mon, 16 Oct 2017 19:55:44 GMT

When I run the query in 'Oracle SQL Developer' the query produces the results that I expect.

Re: SQL Load

maxgro — Mon, 16 Oct 2017 19:59:46 GMT

if you get an error, please post the error

if the query ends without returning rows, check the filters (where .....)

if the query doesn't end, as you have many tables in the query maybe it's a problem on the database side; you can start with 2 or 3 tables and then add one table at a time or as Tomasz already answered, try with another tool (client)

Re: SQL Load

vishsaggi — Mon, 16 Oct 2017 20:03:11 GMT

Hmmm. Nothing seems to be wrong with the script. Can you try like running only this script. I mean just use your ODBC connection string and just run this single script.

ODBC CONNECT32 to ......;

LOAD *;

SQL SELECT ......yourSQLscriptHere;

Re: SQL Load

evansabres — Mon, 16 Oct 2017 20:08:19 GMT

When I run the query, I do not get an error message, but the re-load script indicates '0 Lines fetched'

Re: SQL Load

evansabres — Mon, 16 Oct 2017 20:08:23 GMT

When I run the query, I do not get an error message, but the re-load script indicates '0 Lines fetched'

Re: SQL Load

maxgro — Mon, 16 Oct 2017 20:13:44 GMT

starts with a select count(*) of 2 tables, oli and po with the join

how many rows?

add the filters on oli and po

how many rows?

repeat: add one table, add one table, .....

Re: SQL Load

evansabres — Mon, 16 Oct 2017 20:30:46 GMT

Apologies, I am very new to SQL scripting. Can you explain what you mean?

Re: SQL Load

maxgro — Mon, 16 Oct 2017 20:44:18 GMT

I think you can start with 2 tables and check if you get a result

select count(*)

from

order_line_item oli,

patron_order po

where

oli.order_id = po.order_id

if you get a result (count > 0) add the filter (bold) on the 2 tables

select count(*)

from

order_line_item oli,

patron_order po

where

oli.order_id = po.order_id

and trunc(po.created_date) >= ('01-JAN-2017')

and trunc(po.created_date) <= ('08-APR-2018')

and oli.market_type_code = 'P'

and oli.transaction_type_code in ( 'SA', 'ES', 'CS' )

if you get a result add another table and the join with the previous 2 tables

maybe

table: agency a

join: and po.created_by_agency_id = a.agency_id

and so on until you have all the tables in the query; when you get 0 rows as result you should understand where is the problem

Re: SQL Load

evansabres — Mon, 16 Oct 2017 20:45:20 GMT

Thank you, I will attempt this