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
_______________________________________________
Valentina mailing list
Valentina (at) lists .D.OT macserve.net
http://lists.macserve.net/mailman/listinfo/valentina
©2007 Thorsten Hohage |
