SELECT vs. LOAD Statements

[#1]

I've been using QV for 40 hours a week for the last 6 months so I like to think that I have some idea what I'm doing. However, I have VERY little SQL experience or knowledge. I don't have access to the huge database tables at work yet so I've been getting all of my data mainly from .csvs and other sources where you use LOAD statements.

Pretty soon I'll be getting access to the huge tables and I'll need to use SELECT statements on the databases. Is everything going to change or is it going to be pretty much the same thing as what I've been doing? I know in SQL you have to do joins ON something and you can nest SELECT statements which is something I don't really understand. I'm assuming (and hoping) that I can just do 2 different SELECT statements and then do the joins on resident tables like I've been doing so I can bypass all of this join ON and nested SELECT statement stuff... is this an accurate assumption or do I just need to learn to do it the right way? Are there certain situations where nesting SELECT statements are a must?

Thanks in advance.

[Anonymous]

I typically do a simple SQL select * from table right below the LOAD statement. I rarely use resident tables. I let QlikView do the join based on the names. Why would you use nested SELECT? Maybe you meant sub-queries. QlikView will simply pass whatever SQL statement you put in without any validation and let the database decide if the SQL was correct. If you need to write advanced SQL statements, I would suggest that you write it outside of QlikVIew, make sure it works and then copy paste in QlikVIew SQL.

Regards,

Dinesh.

[#3]

Well like i said I know VERY little about anything SQL related so I don't really know what I'm talking about. I guess my question is, is there any reason for me to use stuff like JOIN ON, HAVING, nested SELECT statements, and stuff like that or should I be able to just load the raw tables into QV and do it all the same as I would if I were loading data from .csvs?

[erichshiino]

I'd say you can reproduce everything you want inside QlikView.

Sometimes, from a performance perspective, it should be better to make it on SQL.

For example, let us say that you need a specific join or sub-query to make a where clause.

In some cases you would have to bring whole tables to QlikView to perform a filter. This may consume a lot of network bandwidth, database time, QV Server may require extra memory and so on.

You can also leverage companies knowledge but re-using already created queries on the database.

[Anonymous]

I have build full blown applications and never had a need to use the JOIN, HAVING, SORT in the SQL statements. The only time I have deviated from getting all the raw data is when i know for sure I would not use majority of the data and in that case I have rarely used WHERE conditions on my SQL statements.

Regards,

Dinesh.

[rbecher]

Hi,

as a starting point I would suggest to ask your IT department or a database admin to give you the right SQL statements or to cover them into database views where you could use just SELECT * FROM theview; in QV.

Even if you have only a few joins you could make many mistakes on the combination of join and where conditions. Also, you could easily kill the database performace (hopefully not the production environment 😉 if you don't know how to use indices, plans etc.

- Ralf

[Anonymous]

The only think I would add to Ralf's statement is that sometimes use of indexes hurt when the conditions you put returns majority of the rows. In those cases, it is usually better to have tablespace scans rather than index scans. Explain plan always will guide you through, but in your case, if you do decide to do joins and where conditions in SQL, a review with your DBA would certainly be helpful.

Regards,

Dinesh.