One of the shortcomings which has always bothered me with MS Excel, and then Calc, is that it puts the ability to apply complex queries to data far away from the user. While you can kinda, sorta, somehow, make it happen (there is some sort of embedded DBMS in there after all, right?) - it is certainly not easily accessible. It would be quite useful if a user could select a range of cells, and considering it as a relational table - apply an SQL query to it, with the results populating a new sheet or someplace else on the same sheet. There can be a lot of bikeshedding here: How this table should be named, whether to consider the first row to contain column names, what to do with repeated records, what dialect of SQL to use, whether the query is remembered after use or not, whether the results are dynamically linked to the source or not (maybe both options are interesting) and so on and so forth. But we could put ourselves squarely ahead of MS Excel for this aspect of data anlysis if we were to offer this functionality.
SQL query in Calc? And how do you think is Calc blocking Database/Database-Queries? Cannot wrap my mind around this idea. You mean something like <FUNCTION> at <RANGE> for <TARGET> like "SUM at A1:A10 for B1". Why would SQL be suitable at all, thinking of Python/Basic etc.
(In reply to Heiko Tietze from comment #1) > SQL query in Calc? We have tables in calc, and we often want to generate new tables with derivative data. Pivot Tables work for simple cases, but not beyond that. But pivot tables have a lot of code supporting their dynamism; if we stick to just generating contents of cells, it should not be difficult - UI-wise at least - to allow for complex SELECT queries. > And how do you think is Calc blocking Database/Database-Queries? My "database" in calc is my selection. Tell me, how do I query it? > Cannot wrap my mind around this idea. You mean something like <FUNCTION> at > <RANGE> for <TARGET> like "SUM at A1:A10 for B1". No, not a function. That might be an interesting direction, but I mean something more straightforward than that. Example: You enter the following values +---------+------------------+ | name | favourite color | +---------+------------------+ | Alice | blue | +---------+------------------+ | Bob | green | +---------+------------------+ | Charlie | pink | +---------+------------------+ | Daria | green | +---------+------------------+ | Eve | green | +---------+------------------+ Then, you select this cell range, then invoke an UNO command for applying an SQL query. In the dialog that opens up, you enter: SELECT t1.name, t2.name FROM selection_ AS t1, selection_ AS t2 WHERE t1.'favourite color' = t2.'favourite color' AND t1.name < t2.name; and you also toggle the checkboxes "use first row as column names", and "results as new sheet" and press ok. You now get a new sheet which contains +---------+------------+ | t1.name | t2.name | +---------+------------+ | Bob | Daria | +---------+------------+ | Daria | Eve | +---------+------------+ | Bob | Eve | +---------+------------+
The user can work with the embedded database as source instead of the spreadsheet. Then he can link to the tables and queries in the spreadsheet document. That gives the user the full SQL ability of a database and the complex calculations and charts abilities of a spreadsheet at the same time. I do not support to extend the misuse of spreadsheets as databases.
(In reply to Regina Henschel from comment #3) > The user can work with the embedded database as source instead of the > spreadsheet. The user has their data in a spreadsheet. They should not have to give that up just to run a query. > Then he can link to the tables and queries in the spreadsheet > document. But that's redundant and doesn't help the user who just wants to execute a query and get a table. > That gives the user the full SQL ability of a database and the > complex calculations and charts abilities of a spreadsheet at the same time. But it's not necessary. > I do not support to extend the misuse of spreadsheets as databases. I'm not suggesting people use spreadsheets as databases, I just want to apply a query or a transformation to data in a table, that's all.
(In reply to Eyal Rozenberg from comment #2) > SELECT t1.name, t2.name > FROM selection_ AS t1, selection_ AS t2 > WHERE t1.'favourite color' = t2.'favourite color' AND t1.name < t2.name; Why a complex SELECT statement where a simple filter (function or database range feature) is sufficient?
(In reply to Regina Henschel from comment #5) > (In reply to Eyal Rozenberg from comment #2) > > > SELECT t1.name, t2.name > > FROM selection_ AS t1, selection_ AS t2 > > WHERE t1.'favourite color' = t2.'favourite color' AND t1.name < t2.name; > > Why a complex SELECT statement where a simple filter (function or database > range feature) is sufficient? A simple filter is not sufficient, since I'm producing _pairs_ of records/rows: All pairs of people who like the same color.
This sounds like Excel's PowerQuery function ? I see from a cursory glance in the corresponding menu in Excel that you can choose an Excel sheet as the datasource with this functionality.
(In reply to Alex Thurgood from comment #7) > This sounds like Excel's PowerQuery function ? I see from a cursory glance > in the corresponding menu in Excel that you can choose an Excel sheet as the > datasource with this functionality. PowerQuery seems to involve a lot of UI rather than a simple dialog; and it's not contextual and on-the-fly'ish. And TBH I've not actually explored everything it can do. Let's call this feature low-key query :-)