SQL Generator

[Bit Byter]

Hi,

This may be a little OT, but I don't know exactly where else to post
it. I am writing a little parser that generates valid SQL using
"English like" text. I want to use this to allow non-technical users to
be able to quickly write their own queries, to search through a
database that stores information about the sales of different
companies. I can provide more information for anyone who wants to help.

Currently, the syntax is :

Select ALL PRODUCT_FILTER from COMPANY where funcname(params) conditon
.... and ITEM_DATE date_condition
Where:

product_filter specifies the product type to be included in the search
company specifies the company whose data is to be searched
funcname is an aggregate function
condition specifies the criteria for the aggregate function (i.e. a
HAVING clause)
date_condition specifies the criteria for the dates to be used in the
search

Note: there can be more than 1 aggregate function

A typical query then may look like this:

Select all 'toys' from 'Walmart' where average_sales(100) 100000 and
avaerage_cost(100,10) <= 1 and item_date between "01-Jan-00" and
"01-Jan-06"
I would like to know what the underlying SQL statement will look like,
so that I can use this knowlege to build a generic parser that creates
SQL statements from the "English like" text, using the syntax I
described above.

[Hugo Kornelis]

On 20 Jul 2006 04:45:02 -0700, Bit Byter wrote:

(snip)

>A typical query then may look like this:

Select all 'toys' from 'Walmart' where average_sales(100) 100000 and
avaerage_cost(100,10) <= 1 and item_date between "01-Jan-00" and
"01-Jan-06"
I would like to know what the underlying SQL statement will look like,
so that I can use this knowlege to build a generic parser that creates
SQL statements from the "English like" text, using the syntax I
described above.

Hi Bit,

That depends on what the tables look like. If you post the table
structure (as CREATE TABLE statements, including all constraints,
indexes and properties), some rows of sample data (as INSERT statements)
and expected output, I'm sure that you'll get several suggestions how to
write a query.

--
Hugo Kornelis, SQL Server MVP