SQL query to QlikView with Joins - ODBC

[Anonymous]

Hello guys,

I'm having some problems with QlikView, I'm new at it, SQL SELECT it's working good but... i have a query in SQL that works perfectly but i need QlikView to execute it and I'm having no succes at it, the query in SQL is:

SELECT     a.userid,

                   d.id as cursoid,

                   c.visible,

                   c.id as disciplinaid,

                   e.roleid

FROM

     tb_log a INNER JOIN tb_user b ON a.userid=b.id

     INNER JOIN tb_course c ON a.course=c.id

     INNER JOIN tb_course_categories d ON c.category=d.id

     INNER JOIN tb_role_assignments e ON a.userid=e.userid

     INNER JOIN tb_context f ON e.contextid=f.id AND f.instanceid=c.id AND f.contextlevel=50

WHERE

    b.firstname <> 'suporte'

GROUP BY

a.userid,

d.id,

c.id,

d.name,

c.shortname

ORDER BY

a.userid,

d.id,

c.id,

d.name,

c.shortname";

I think I should use the comand ALIAS a as tb_log; ... but still, i couldn't execute the query because of the Joins and I'm using ODBC conection.

[vishsaggi]

What is the error you are getting and in your SQL script why you using Group By clause when you do not have any aggregation functions used?

[Anonymous]

the script works,but i need QlikView to execute it, and then i will save the select result in a .qvd

so i need this SQL script in QlikView

[vishsaggi · Accepted solution]

Yes, what is the error you are getting? Did you connect to your data base from QV using ODBC or OLEDB?

Use like

Connection String.

LOAD *;

SQL Select ....

FROM .....;

[Anonymous]

i'm connecting with

ODBC CONNECT TO...

[Anonymous]

I Tried

ODBC CONNECT TO ...;

LOAD*;

SQL SELECT     a.userid,

                   d.id as cursoid,

                   c.visible,

                   c.id as disciplinaid,

                   e.roleid

FROM

     tb_log a INNER JOIN tb_user b ON a.userid=b.id

     INNER JOIN tb_course c ON a.course=c.id

     INNER JOIN tb_course_categories d ON c.category=d.id

     INNER JOIN tb_role_assignments e ON a.userid=e.userid

     INNER JOIN tb_context f ON e.contextid=f.id AND f.instanceid=c.id AND f.contextlevel=50

WHERE

    b.firstname <> 'suporte'

it works THANKS A LOT i'm reeeeaally new at QlikView, so now I need to save the result of this select using

STORE ??? INTO Query1.qvd (qvd);

[vishsaggi]

Ok good. Give a path when you store into like

Just above the Load *; give a table name like

TableNamehere:

LOAD *;

SQL Select.....;

Store TableNamehere INTO C:\Folder1\QV\TableNamehere.qvd(qvd);

[Anonymous]

is there a way that I can store the result of the select in a variable ? like an to use WHILE comand and chek one by one ?

[Anonymous]

Hi Leonardo,

Check the following points

• If there is any syntax error as I have observed a unnecessary " (double quote)
• Group By without an Aggregation function

Please share log file and data source if possible.

Regards

Chandan

[vishsaggi]

Not sure what you mean why you want to store this in a variable? If you can tell me what your analysis is we can suggest accordingly..