trouble with this select

[Thorsten Hohage]

Hi Joseph,

On 2007-10-31, at 02:49, Joseph Morgan wrote:

> SELECT departmentName as  
> 'myDepartmentName',sum(itemsales.itemsalesamount) as 'itemsalesAMT'
> FROM departments,itemsales where departments._rowID =  
> itemsales.itemsalesdepartmentID
> group by itemsalesdepartmentID
> order by itemsalesAMT Desc
>
> and of course I get this error:
> "Kernel error: 0x42508. In the SELECT clause you can use only fields  
> listed in the
> GROUP BY and aggregative functions."

> I see that it is illegal to mix aggregate and non aggregates but in  
> mysql this select works.
> Any ideas on how to cheat and work around this?

Yes, try the statement

SELECT itemsalesdepartmentID , departmentName as 'myDepartmentName',
sum(itemsales.itemsalesamount) as 'itemsalesAMT'
FROM departments,itemsales
where departments._rowID = itemsales.itemsalesdepartmentID
group by itemsalesdepartmentID, departmentName
order by itemsalesAMT Desc


depending on the used Valentina version I'm not sure if you're able to  
use aggregate function in an order by clause. If this will make any  
difficults, define a table method for the calculation.


> For me the changes required to switch from mysql to valentina have  
> been somewhat difficult.
> Am I the only one? It would be encouraging to know that I am not the  
> only one struggling.


Changing from one DB-vendor to another will never be straight forward  
and I even remember a lot of situations where even a product upgrade  
or product switch from one vendor will cause issues.

For the migration process itself I dump the structure to sql first,  
use BBEdit and some find-replace to change everthing, that it will not  
only fit in Valentina, but uses some advantages of Valentina. Then I  
define the db and in a second step I dump the data (complete inserts,  
no hex) and import it.

Average time of DB migration for rather complex systems (50+ tables)  
is for me < 1 day - for "known" solutaions (because some clients use  
the same structure) and not to much data I'm below an hour now.


Different story is the given query as an example. But I use my own ORM  
so there are only a few places where I need to change code and adopt  
my own "ValentinaContext". For reporting I use a similar system, where  
I can manually change things.

Comparing e.g. a (former) Oracle->Sybase,  FrontBase -> mySQL  
migration I feel very comfortable on migrating to Valentina.


regards

Thorsten Hohage
--
objectmanufactur.com - Hamburg,Germany