Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 4 of 4 total |
UNION and complexity |
Wed, Mar 1 2006 1:55 PM | Permanent Link |
I'm looking to convert a load of code into SQL, but I'm wondering if I'm
mad or hoping for too much. Basically, I have a table with various linked tables. I'll try to keep it simple, so let's assume I have a table with ID, NAME and AMOUNT values, and a linked table that uses just LINKED_ID, VALUE. That linked table might perhaps have a defined number of rows - known to my code anyway. So my output will show a grid in the following format: NAME(1) AMOUNT(1) VALUE(1/1) VALUE(1/2) VALUE (1/3) NAME(2) AMOUNT(2) VALUE(2/1) VALUE(2/2) VALUE (2/3) NAME(3) AMOUNT(3) VALUE(3/1) VALUE(3/2) VALUE (3/3) Is this even possible in SQL? Or should I go back to my old code. Thanks for your thoughts! /Matthew Jones/ | |
Mon, Apr 24 2006 10:09 AM | Permanent Link |
FWIW, I've been tackling this issue for another purpose, and someone
(thanks Neil!) pointed me at http://www.kever.com/index.htm?strUrl=%2Fcontent%2FEzSpecials.htm which is a proxy data set which I've used successfully with a certain grid to allow me to display the results from multiple DBISAM queries in what appears to the grid to be a single table with fields that I populate accordingly. This may be a way around this "limitation" of SQL. /Matthew Jones/ | |
Mon, May 15 2006 6:29 AM | Permanent Link |
adam | You can do something like this using multiple joins on a single parent table repeating
the link the the child table & using the WHERE clause to segregate the data that returns from the child tables. Imagine simple DB Structure: Customer table Order table 1 customer can have many orders. This SQL: -- SELECT C.Name, SUM(O1.OrderValue) as ThisMonthsOrders, SUM(O2.OrderValue) as LastMonthsOrders, SUM(O3.OrderValue) as TwoMonthOldOrders FROM Customer C LEFT JOIN Orders O1 ON O1.CustomerID=C.ID LEFT JOIN Orders O2 ON O2.CustomerID=C.ID LEFT JOIN Orders O3 ON O3.CustomerID=C.ID WHERE O1.DateOfOrder >= Current_Date - 30 AND O2.DateOfOrder < Current_Date - 30 AND O2.DateOfOrder >= Current_Date - 60 AND O3.DateOfOrder < Current_Date - 60 AND O3.DateOfOrder >= Current_Date - 60 GROUP BY C.Name ORDER BY C.Name -- This SQL gives a result which is something like what you seemed to want in your message ... note that with different data structures & thinking about the WHERE clause carefully you can generate a lot of different result sets of this type, but note that the GROUP BY clause effects how the numbers SUM, as it is a cartesian join. *************************************************************** ANOTHER WAY: Select a set of tables into temporary tables & then rejoin them SELECT INTO "C:\Temp.dat" C.ID, SUM(O.OrderValue) as ThisMonthsOrders FROM Customer C LEFT JOIN Orders O1 ON O1.CustomerID=C.ID WHERE O1.DateOfOrder >= Current_Date - 30 ; /* note this semi-colon is required to separate the SQLScript. SELECT INTO "C:\Temp1.dat" C.ID, SUM(O.OrderValue) as LastMonthsOrders FROM Customer C LEFT JOIN Orders O1 ON O1.CustomerID=C.ID WHERE O1.DateOfOrder < Current_Date - 30 AND O1.DateOfOrder >= Current_Date - 60 ; SELECT INTO "C:\Temp2.dat" C.ID, SUM(O.OrderValue) as TwoMonthOld FROM Customer C LEFT JOIN Orders O1 ON O1.CustomerID=C.ID WHERE O1.DateOfOrder < Current_Date - 60 AND O1.DateOfOrder >= Current_Date - 90 ; /* then we select all these temporary results into a final result we show the user. */ SELECT C.Name, T1.ThisMonthsOrders, T2,LastMonthsOrders, T3.TwoMonthOld FROM Customer C LEFT JOIN "C:\Temp1.dat" T1 ON T1.ID = C.ID LEFT JOIN "C:\Temp2.dat" T2 ON T1.ID = C.ID LEFT JOIN "C:\Temp3.dat" T3 ON T1.ID = C.ID WHERE C.ID IN ( SELECT CustomerID FROM ORDERS WHERE DateOrder >= Current_Date - 90 ) /* Note that you need the final WHERE clause to ensure that you get all the customers you need for ALL THREE temp tables ... as some customers may not have made an order in every month. */ ---- I hope this is Useful! |
Mon, May 15 2006 10:05 AM | Permanent Link |
Oooh - thanks for that. I'll have to print it and study, but it would be a
big advance for me. /Matthew Jones/ |
This web page was last updated on Sunday, May 5, 2024 at 10:18 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |