Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Insert SQL Query in Data load editor

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?

8 Replies
agni_gold
Specialist III
Specialist III

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

Anonymous
Not applicable
Author

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
Specialist III
Specialist III

Is this query running on sql editior ?

Anonymous
Not applicable
Author

yes, it runs well in SQLYog

Anil_Babu_Samineni

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Anonymous
Not applicable
Author

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
Not applicable
Author

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
Not applicable
Author

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