Insert SQL Query in Data load editor

[Anonymous]

Hi, please help with my issue.

I had this DB with lots of tables such as :
'list_item' table

'list_item_storage' table

'list_item_tx' table

'list_warehouse' table

and so on.

Currently I'm using Smart data load in Qlik Sense to create report, but now i have to insert data with this kinds of SQL Query :

SELECT t2.tx_id, t2.storage_id, t2.tx_date, t2.end_qty, t3.item_id, t4.item_name, t5.location_name

FROM (SELECT storage_id, MAX(tx_date) AS tx_date FROM web_pcm.list_item_tx GROUP BY storage_id) t1

INNER JOIN web_pcm.list_item_tx t2 ON t1.storage_id = t2.storage_id AND t1.tx_date = t2.tx_date

LEFT JOIN web_pcm.list_item_storage t3 ON t2.storage_id = t3.storage_id

LEFT JOIN web_pcm.list_item t4 ON t3.item_id = t4.item_id

LEFT JOIN web_pcm.list_warehouse t5 ON t3.location_id = t5.location_id

From Smart data load table selection is ended in Auto-generated section. I tried to insert my Query on Data load editor in Main tab, but it keeps telling me this error.

The following error occurred:

Connector reply error: SQL##f - SqlState: 3D000, ErrorCode: 1046, ErrorMsg: [ma-3.0.3][10.1.35-MariaDB]No database selected

Data has not been loaded. Please correct the error and try loading again.

How do I insert that SQL Query and create a report on it? Do I still need to use Smart data load along with SQL Query, or just need the SQL Query to load the data I need?

[agni_gold]

Better will be , create ODBC connection for your database in sense server, and then use this query directly to fetch data.

[Anonymous]

Hi Agnivesh, I've already created ODBC connection. If I try to query without JOIN function, it able to fetch data. But if I use INNER JOIN or LEFT JOIN, Qlik Sense Data load editor output with unable to find t2.tx_id in field data.

[agni_gold]

Is this query running on sql editior ?

[Anonymous]

yes, it runs well in SQLYog

[Anil_Babu_Samineni]

Error Code : 1046 error - When ever we don't have Database.

[Anonymous]

Hi Loveisfail, It's kinda weird actually, because if I don't insert the query or if I don't using JOIN, it can reload the data. How can it shows "we don't have Database"? It puzzled me

[Anonymous]

Hello,

If "web_pcm" id name of database You should add schema "dbo" or left empty.

Try:

SELECT t2.tx_id, t2.storage_id, t2.tx_date, t2.end_qty, t3.item_id, t4.item_name, t5.location_name

FROM (SELECT storage_id, MAX(tx_date) AS tx_date FROM web_pcm.list_item_tx GROUP BY storage_id) t1

INNER JOIN web_pcm..list_item_tx t2 ON t1.storage_id = t2.storage_id AND t1.tx_date = t2.tx_date

LEFT JOIN web_pcm..list_item_storage t3 ON t2.storage_id = t3.storage_id

LEFT JOIN web_pcm..list_item t4 ON t3.item_id = t4.item_id

LEFT JOIN web_pcm..list_warehouse t5 ON t3.location_id = t5.location_id

[Anonymous]

Hello,
yes, web_pcm is the name of the database. I tried run your query in both SQLYog and Qliksense and both return with error,

Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '.list_item_tx t2 ON t1.storage_id = t2.storage_id AND t1.tx_date = t2.tx_date