An SQL problem

An SQL problem

Post by Fran » Thu, 17 Jul 2003 04:39:37



This is my requirement: In a table each record represents a meter reading. I
want to know the month total. I tried the following SQL statement:

select id, extract(month from Date_reading) as MM, sum(Reading)
from MyDB
group by extract(month from Date_reading)

in this case I get "Capability not supported".

using:
group by MM           I get "MM"  "invalid field name".

using:
group by 2               I get "Capability not supported".

I am using the BDE and MyDB is a dBase table. May be this is the problem,
but: There any way to get what I want ?

 
 
 

An SQL problem

Post by John Kebe » Thu, 17 Jul 2003 10:18:23



> This is my requirement: In a table each record represents a meter reading. I
> want to know the month total. I tried the following SQL statement:

> select id, extract(month from Date_reading) as MM, sum(Reading)
> from MyDB
> group by extract(month from Date_reading)

> in this case I get "Capability not supported".

> using:
> group by MM           I get "MM"  "invalid field name".

> using:
> group by 2               I get "Capability not supported".

> I am using the BDE and MyDB is a dBase table. May be this is the problem,
> but: There any way to get what I want ?

Haven't been using dBase or BDE for awhile, but maybe you could try:

group by extract(month from Date-reading)

Just a guess.

John

 
 
 

An SQL problem

Post by Richard Biff » Thu, 17 Jul 2003 11:45:16


You can't include the id in the result set, because which id would represent
all the records in the month? So in standard SQL you would use:

select extract (month from Date_reading) as MM, sum (Reading) as Reading_Sum
from MyDB
group by extract (month from Date_reading)

Since you're using Local SQL you may still get the capability-not-supported
error, because Local SQL may not be able to group on a function result. In
that case, break the query into two parts and create a view. See "View" in
the index of LocalSql.hlp in the BDE directory. The first query would be
this:

select extract (month from Date_reading) as MM, Reading
from MyDB

Save this query as, e.g., MonthReadings.sql.

Your second query looks like this:

select MM, sum (Reading) as Reading_Sum
from "MonthReadings.sql"
group by MM

I don't know the purpose of your query, but you might need to select and
group by the year as well as month.

  Richard


Quote:> This is my requirement: In a table each record represents a meter reading.
I
> want to know the month total. I tried the following SQL statement:

> select id, extract(month from Date_reading) as MM, sum(Reading)
> from MyDB
> group by extract(month from Date_reading)

> in this case I get "Capability not supported".

> using:
> group by MM           I get "MM"  "invalid field name".

> using:
> group by 2               I get "Capability not supported".

> I am using the BDE and MyDB is a dBase table. May be this is the problem,
> but: There any way to get what I want ?

 
 
 

An SQL problem

Post by Fran » Fri, 18 Jul 2003 17:42:12


Thanks for your suggestion. I will soon try. I was not aware I could store
the result of a query and query the latter...
I hope this is feasible, since it will solve my problem. And yes, you are
right, I should also group by the year..

"Richard Biffl" <usenet a t removvethiisphrraseblacklettersoftware.com> ha

Quote:> You can't include the id in the result set, because which id would
represent
> all the records in the month? So in standard SQL you would use:

> select extract (month from Date_reading) as MM, sum (Reading) as
Reading_Sum
> from MyDB
> group by extract (month from Date_reading)

> Since you're using Local SQL you may still get the

capability-not-supported
> error, because Local SQL may not be able to group on a function result. In
> that case, break the query into two parts and create a view. See "View" in
> the index of LocalSql.hlp in the BDE directory. The first query would be
> this:

> select extract (month from Date_reading) as MM, Reading
> from MyDB

> Save this query as, e.g., MonthReadings.sql.

> Your second query looks like this:

> select MM, sum (Reading) as Reading_Sum
> from "MonthReadings.sql"
> group by MM

> I don't know the purpose of your query, but you might need to select and
> group by the year as well as month.

>   Richard



> > This is my requirement: In a table each record represents a meter
reading.
> I
> > want to know the month total. I tried the following SQL statement:

> > select id, extract(month from Date_reading) as MM, sum(Reading)
> > from MyDB
> > group by extract(month from Date_reading)

> > in this case I get "Capability not supported".

> > using:
> > group by MM           I get "MM"  "invalid field name".

> > using:
> > group by 2               I get "Capability not supported".

> > I am using the BDE and MyDB is a dBase table. May be this is the
problem,
> > but: There any way to get what I want ?

 
 
 

1. SQL problem, MSDTC Problem or VB.NET problem?

Hello,

I have a very strange behaviour of one of ours web applications.

I have the user interface in VB.NET, I have the business logic layer as
VB6 COM components running as COM+ applications(W2K SP3) and the data
layer
SQL 2K SP2.
I have two SQL Servers one live and one for test the only difference is
that the live server has some more security updates so the versions of
SQL differs slightly
Live: 8.00.578
Test: 8.00.534

I have a form in VB.NET(ASP.NET) that updates some information
concerning an user(last name, first name and email).
When I want to update this information on the live server I can see in
the database the lines that were modified (I hold theese values in the
database as attributes so I have a line for each name and one for email)
but if I wait for 5-15 seconds those lines diappear from the database.
It is a strange behaviour as if I use the test server this doesn't
happen.

I tried using profiler but I had no stetemen that executes a delete in
that table.

Any idea where I should check?
Thanks
Florian

2. 2140 error on service start, Sql7.0

3. datagrid problem or SQL problem?

4. Security question

5. SQL Problem: DISTINCT problem

6. Admin from Client - Question

7. sql problem SA problem urgent

8. ADO- How ????

9. Cool SQL problem - SQL for Temporal Aggregation

10. Dynamic SQL Problems with CT-client on SQL Server 10.02

11. Access -> SQL Server SQL Problem

12. Oracle/SQL-Server SQL-Problem

13. SQL or T-SQL problem