I'm using DBISAM 2. I have to get a SUM from a table and calculate with a SUM of another table. The problem is, that I also need two different GROUP BYs.
Table 1: Devision, No_of_Customers, Date
Sum of Customers per Month (or Quarter of Year)

Table 2: Devision, No_of_Sold_Item, Date
Sum of each Item per Month

Calculation: No_of_Sold_Item per 100 Customers per Month

How can I do it in tDbisamQuery?

HeiNi


I don't remember what sql is in DBISAM 2 but if it has the necessary commands you'll need to create a script. In outline the process will be

1. create a memory table for Sum of Customers per Month (or Quarter of Year)
2. create a memory table for Sum of each Item per Month
3. join them together and carry out the calculation

I know the syntax for memory tables changed but roughly

>>Table 1: Devision, No_of_Customers, Date
Sum of Customers per Month (or Quarter of Year)>>

SELECT SUM(No_of_Customers) AS Customers, EXTRACT(MONTH FROM Date) AS Mnth INTO Memory\Temp1  FROM Table1 GROUP BY Mnth

<<Table 2: Devision, No_of_Sold_Item, Date
Sum of each Item per Month>>
SELECT SUM(No_of_Sold) AS Sales, EXTRACT(MONTH FROM Date) AS Mnth INTO Memory\Temp2  FROM Table2 GROUP BY Mnth

<<Calculation: No_of_Sold_Item per 100 Customers per Month>>

SELECT Customers,Sales, Sales / (Customers /100) AS Rate FROM Memory\Temp1 T1
JOIN Memory\Temp2 T2 ON T1.Mnth = T2.Mnth


You make it into a script by having a semicolon at the end of each statement

My code is pretty much guaranteed to be wrong for DBISAM 2 but it should get you started.

Don't forget to drop the memory tables when you've finished with them.

Roy Lambert

I tried something like that in Database System Utility -> New SQL Query and only got the results of the first Select.

I'm a bit desperated to get the sql script running.

Heiko

Roy Lambert wrote:

HeiNi


I don't remember what sql is in DBISAM 2 but if it has the necessary commands you'll need to create a script. In outline the process will be

1. create a memory table for Sum of Customers per Month (or Quarter of Year)
2. create a memory table for Sum of each Item per Month
3. join them together and carry out the calculation

I know the syntax for memory tables changed but roughly

>>Table 1: Devision, No_of_Customers, Date
Sum of Customers per Month (or Quarter of Year)>>

SELECT SUM(No_of_Customers) AS Customers, EXTRACT(MONTH FROM Date) AS Mnth INTO Memory\Temp1  FROM Table1 GROUP BY Mnth

<<Table 2: Devision, No_of_Sold_Item, Date
Sum of each Item per Month>>
SELECT SUM(No_of_Sold) AS Sales, EXTRACT(MONTH FROM Date) AS Mnth INTO Memory\Temp2  FROM Table2 GROUP BY Mnth

<<Calculation: No_of_Sold_Item per 100 Customers per Month>>

SELECT Customers,Sales, Sales / (Customers /100) AS Rate FROM Memory\Temp1 T1
JOIN Memory\Temp2 T2 ON T1.Mnth = T2.Mnth


You make it into a script by having a semicolon at the end of each statement

My code is pretty much guaranteed to be wrong for DBISAM 2 but it should get you started.

Don't forget to drop the memory tables when you've finished with them.

Roy Lambert

Hi Roy,

I tried this:
1. Put 3 DBISAMQuery on a form.
2. Each Select Statment into one DBISAMQuery.SQL
3. ButtenClick-Event:
 qry3.Close;
 qry1.Open;
 qry1.Close;
 qry2.Open;
 qry2.Close;
 qry3.Open;

Then you can see the result in the Grid.

*******************************
* Is there an easier way?  *
*******************************
Heiko
-----------
HeiNi wrote:

I tried something like that in Database System Utility -> New SQL Query and only got the results of the first Select.

I'm a bit desperated to get the sql script running.

Heiko

Roy Lambert wrote:

HeiNi


I don't remember what sql is in DBISAM 2 but if it has the necessary commands you'll need to create a script. In outline the process will be

1. create a memory table for Sum of Customers per Month (or Quarter of Year)
2. create a memory table for Sum of each Item per Month
3. join them together and carry out the calculation

I know the syntax for memory tables changed but roughly

>>Table 1: Devision, No_of_Customers, Date
Sum of Customers per Month (or Quarter of Year)>>

SELECT SUM(No_of_Customers) AS Customers, EXTRACT(MONTH FROM Date) AS Mnth INTO Memory\Temp1  FROM Table1 GROUP BY Mnth

<<Table 2: Devision, No_of_Sold_Item, Date
Sum of each Item per Month>>
SELECT SUM(No_of_Sold) AS Sales, EXTRACT(MONTH FROM Date) AS Mnth INTO Memory\Temp2  FROM Table2 GROUP BY Mnth

<<Calculation: No_of_Sold_Item per 100 Customers per Month>>

SELECT Customers,Sales, Sales / (Customers /100) AS Rate FROM Memory\Temp1 T1
JOIN Memory\Temp2 T2 ON T1.Mnth = T2.Mnth


You make it into a script by having a semicolon at the end of each statement

My code is pretty much guaranteed to be wrong for DBISAM 2 but it should get you started.

Don't forget to drop the memory tables when you've finished with them.

Roy Lambert

HeiNi

>I tried this:
>1. Put 3 DBISAMQuery on a form.
>2. Each Select Statment into one DBISAMQuery.SQL
>3. ButtenClick-Event:
> qry3.Close;
> qry1.Open;
> qry1.Close;
> qry2.Open;
> qry2.Close;
> qry3.Open;
>
>Then you can see the result in the Grid.
>
>*******************************
>* Is there an easier way? *
>*******************************

You should be able to create a script. I think that was available in V2 but I'm not sure.

Can you try putting all the sql from the queries into a single query - it should go like

line1;
line2;
line3;

essentially DBISAM will then run each query one after the other. NOTE you must have the semicolon at the end of each line. If that doesn't work then maybe V2 didn't support scripts.

Roy Lambert

Hi Roy,

I found this in the help of TDBISAMQuery.SQL:
The SQL property may contain only one complete SQL statement at a time. Multiple batch statements are not allowed. Please use the SQLScript property for this purpose.

So it won't work.Additionally I tried this with semicolons at each end.
Unfortunately it's no good option for me to switch to version 4 because I do direct imports from a third-party-software which uses version 2. And it isn't possible to run two versions in the same Delphi on the same computer.

Do you have another idea?

Thank you again

Heiko

Roy Lambert wrote:

HeiNi

>I tried this:
>1. Put 3 DBISAMQuery on a form.
>2. Each Select Statment into one DBISAMQuery.SQL
>3. ButtenClick-Event:
> qry3.Close;
> qry1.Open;
> qry1.Close;
> qry2.Open;
> qry2.Close;
> qry3.Open;
>
>Then you can see the result in the Grid.
>
>*******************************
>* Is there an easier way? *
>*******************************

You should be able to create a script. I think that was available in V2 but I'm not sure.

Can you try putting all the sql from the queries into a single query - it should go like

line1;
line2;
line3;

essentially DBISAM will then run each query one after the other. NOTE you must have the semicolon at the end of each line. If that doesn't work then maybe V2 didn't support scripts.

Roy Lambert

HeiNi


I've just downloaded the DBISAM V2 and installed it in a VM.

From the OLH

------------------------------------------------------------------------------------------------------------------------------------
property SQLScript: TStrings

Description
Contains the text of an SQL script to execute. Use SQLScript to provide a series of SQL statements that a query component executes when it's ExecSQLScript method is called. At design time the SQLScript property can be edited by invoking the String List editor in the Object Inspector.
The SQLScript property may contain multiple SQL statements such as INSERT, DELETE, UPDATE, CREATE TABLE, ALTER TABLE, etc. in one batch. However, you cannot use any SQL statement that returns a result set, such as SELECT, and each SQL statement must be terminated with a semi-colon (in order to properly distinguish one statement from the next. The SQLScript property may also contain as many properly formatted SQL comments as necessary.
------------------------------------------------------------------------------------------------------------------------------------

So try putting nothing in the SQL property and put the statements into SQLScript instead and then call query.ExecSQLScript.


Roy Lambert

Hi Roy,

the Description says:
...However, you cannot use any SQL statement that returns a result set, such as SELECT...
So it should not work. Actually, I tried that unsuccessfully.

Heiko

Roy Lambert wrote:

HeiNi


I've just downloaded the DBISAM V2 and installed it in a VM.

From the OLH

------------------------------------------------------------------------------------------------------------------------------------
property SQLScript: TStrings

Description
Contains the text of an SQL script to execute. Use SQLScript to provide a series of SQL statements that a query component executes when it's ExecSQLScript method is called. At design time the SQLScript property can be edited by invoking the String List editor in the Object Inspector.
The SQLScript property may contain multiple SQL statements such as INSERT, DELETE, UPDATE, CREATE TABLE, ALTER TABLE, etc. in one batch. However, you cannot use any SQL statement that returns a result set, such as SELECT, and each SQL statement must be terminated with a semi-colon (in order to properly distinguish one statement from the next. The SQLScript property may also contain as many properly formatted SQL comments as necessary.
------------------------------------------------------------------------------------------------------------------------------------

So try putting nothing in the SQL property and put the statements into SQLScript instead and then call query.ExecSQLScript.


Roy Lambert

HeiNi


Sorry, I missed that bit

I don't have any version 2 tables so I can't play, all I'm doing is guessing and relying on ancient memories. If not to big or confidential you could post the tables and the queries so far to the binaries and I'll be able to actually try things.

My next guesses are:

1. what happens if you have the preparation queries in the script property and the final select in the sql property call ExecSQLScript and then Open
2. make the final piece of sql into an INSERT statment and then open the table you create
3. subclass the query component to do the whole job
4. create a small function that will do the job using dynamically created queries, or a query passed in as a parameter
5. live with three queries

Depending on how Tim wrote the code for the query back in V2 number 3 should be easy. If its not then number 4 certainly is.

Roy Lambert

HeiNi


Another question - which version of Delphi are you using?

Roy Lambert