This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
- edited on
Need more information on how to use our visual query builder when choosing tables or queries to input? We’ve put together a quick video in v10.1 for your reference:
Find more information on our visual query builder below:
Hi Matt @MattD
I was doing some visual Query over an Exasol database with the Visual Query Builder. Generally, it is a very powerful tool, and taking advantage of the functionality of Active Query Builder https://www.activequerybuilder.com/hs15.html as described in the help document https://help.alteryx.com/current/index.htm#ChooseTableorSpecifyQuery.htm#Visual .
I have two questions emerged when using the tool, but I did not find answers in the community, hope this is a good place to get help.
My questions are:
1. According to the Active Query Builder information, there is a query tree view besides the Main, Grid and Table list views. https://www.activequerybuilder.com/hs120.html Since the Visual Query Builder was introduced 2 years ago, is there any plan to update it and incorporate with more features, such as the query tree?
2. I find the linked object function is not working. Although I have defined primary key and foreign key in my Exasol database, only primary key is recognised by the query builder. Is it because the database I am using not support the functionality? Or do I need to do something extra to get the linked object function working? As the linked object suppose to be able to get related table according to the primary key and foreign key relationship. If the function can work properly, it will be easier to build the linkage of data sets, with less chance to make mistakes.
Hi @MattD, thank you for the video! I started playing with the VQB this week as part of the 2014.4 Beta testing program. Up to now, I've always just used the SQL Editor to construct my queries. I can see that the VQB would be really useful where the relationships between tables are nice and straightforward and where the WHERE clauses, etc. are nice and straightforward, but I wanted to put the VQB through its paces with some meatier SQL queries. I started by pasting an SQL query into the editor and then looked at how it is represented in the VQB to figure out if I'd be able to build it in the VQB. From what I can see, it would be very difficult to build a query like this in the VQB. Do you agree or am I missing something, i.e. is there a more intuitive way to put queries like this together in the VQB?
With uris As (Select Distinct stasks.uri
From (Select Array_Agg(data.artefact.id) As ids,data.artefact.uri From data.artefact
Where data.artefact.uri In (Select a.uri
From data.artefact a Left Join task.task tOn t.id = a.id
Left Join service.expense_audit_process snpOn snp.image_qualification_task_id = a.id
Where (t.id Is Null Or snp.id Is Null) And a.external_id Like '%Linoma%') And data.artefact.expiration_date Is Null
Group By data.artefact.uri) As stasks
From task.task tWhere t.id = Any(stasks.ids)) = 0)Or (
From service.expense_audit_process snpWhere snp.image_qualification_task_id = Any(stasks.ids)) = 0))
Select Distinct a.uri
From data.artefact a
Inner Join uris On a.uri = uris.uri
I have an issue i've been experiencing when using VQB and it looks lik you have similar instance where your column names have an 'a.' added to the front. I'm having to manually edit my queries in the free text (workaround) to strip every a. from column header in order for it to execute properly. Does anyone in this community have an idea as to why and is there an alternate/difference ODBC driver that i should be using that will eliminate this behavior?
Here's an example of end of text from the SQL Editor: (additional fields here via Select.....) raw_sfdc_c_account.`a.global_hq_account_name__c` From raw_sfdc_c.account raw_sfdc_c_account Inner Join raw_sfdc_c.accountcodec raw_sfdc_c_accountcodec On raw_sfdc_c_account.`a.account_code_value__c` = raw_sfdc_c_accountcodec.`a.account_code_c`
When i connect with my db and pull in entire table no trouble AND if i strip all the 'a.' in front of column names i've had a bit of success in at least running the test query but need to spend more time fixing formatting.