Issue 66588 - SQL-Statements within Calc (not using external data sources)
Summary: SQL-Statements within Calc (not using external data sources)
Alias: None
Product: Calc
Classification: Application
Component: code (show other issues)
Version: OOo 2.0.2
Hardware: All All
: P3 Trivial with 13 votes (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Keywords: oooqa
Depends on:
Reported: 2006-06-20 13:05 UTC by discoleo
Modified: 2013-08-07 15:12 UTC (History)
7 users (show)

See Also:
Issue Type: FEATURE
Latest Confirmation in: ---
Developer Difficulty: ---


Note You need to log in before you can comment on or make changes to this issue.
Description discoleo 2006-06-20 13:05:14 UTC
I often perform complex searches in Calc and therefore I would welcome a
powerful, yet simple search strategy.

I worked recently on a worksheet with 75 columns (A-BW) and some 500 rows. Every
row contained data that pointed to a second worksheet totalling over 5000 rows.
This might seem a huge worksheet, but it is actually the norm for serious work.

Most of the time, however, I do need only a portion of this data. The default
method to retrieve this specific data using Calc is to sort the data depending
on the first variable of interest, select the proper rows and copy them to a
second worksheet; sort again for the second variable of interest and so on. (The
filter does not offer substantial advantages.)

This method is unfortunately very laborious and when having to sort for more
than 3 variables (or perform more than 3-5 searches), it becomes very
impractical and I usually decide to create a mySQL database and perform the
searches in the database. This is highly time-consuming, too, as I need to
create a DB first and than reimport the data back into Calc.

What I really miss is a simple SQL SELECT statement within Calc, something like:

SELECT 'which columns/rows – range'
  FROM 'worksheet'.'which columns/rows – range'
  WHERE 'some conditions'
  INSERT INTO NEW 'new worksheet name'
(we could implement later additional options like ORDER BY, DISTINCT and so on)

This SELECT statement needs not to be of DB-grade, i.e. it isn't necessary to
optimize it “ad extremum” (for huge speed/ extreme DB sizes). It needs only to
perform a more complex search AND export the search results into a new worksheet
(we could later devise options to export the data into an existing worksheet,
but this would be more complicated). It could be implemented even as a function
or a menu item. And it would greatly enhance the power of the Ooo-Calc
application and offer some standardization, too (as SQL is a standard).
Comment 1 discoleo 2006-06-28 11:47:24 UTC
SQL-Statements in Calc (Additional Comments/Features)

There is another powerful advantage for such an SQL-statement. It will allow
(beyond the offered standardization) for an easy automation of complex processes
(this is indeed one of the reasons why SQL has been developed).

I also thought about an extension to this SELECT statement:

   INSERT INTO NEW 'new worksheet name'
   COPY AS 'value'/ 'original' / 'reference' / 'HARDLINK'

 - value: evaluate the original cell content (IF it contains a formula) and copy
the computed value
 - original: if the original cell contained a formula,  insert this formula into
the new cell
 - reference: insert a reference to the original cell (i.e. ='original
worksheet'.'original cell')
 - HARDLINK: similar to reference, but make a hard link (like in UNIX
environments, see also my issue about HARD LINKS ); this would copy any
changes made in this cell back into the original cell

Comment 2 frank 2006-06-30 10:46:16 UTC
one for requirements
Comment 3 pmike 2008-07-03 11:31:24 UTC
This is very welcome RFE indeed. All that pesky filters, advanced filters and
data pilot can be easily implemented on top of "build-in SQL" functionality. And
'raw' sql can be extremely useful for advanced users.
I would welcome target milestone for this RFE.
Comment 4 denis0 2010-01-22 06:40:33 UTC
Good idea! SQL = best platform for data manipulations. And it could be a good
bridge from Calc to Base.