An SQL problem 
Author Message
 An SQL problem

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 ?



Sun, 01 Jan 2006 03:39:37 GMT  
 An SQL problem

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 ?

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



Sun, 01 Jan 2006 09:18:23 GMT  
 An SQL problem
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 ?



Sun, 01 Jan 2006 10:45:16 GMT  
 An SQL problem
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
Quote:
> 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 ?



Mon, 02 Jan 2006 16:42:12 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Paradox - local SQL problem with insert

2. SQL problem

3. Tricky Local SQL Problem ?????

4. Local SQL problem

5. SQL Problem ...

6. SQL-problem

7. SQL Problem

8. Local SQL problem

9. SQL problem

10. ez sql problem...i think

11. SQL problem in D2 C/S

12. Serious SQL problem

 

 
Powered by phpBB® Forum Software