November 7, 2005 at 4:21 pm
Hi, I am having difficulty with my SQL statement. Here is what I have:
SELECT Stands.StandID, SPEVersion, LastCallTime, Stores.StoreID, Stores.CompanyID, StoreName, NoteJammed, StackerFull, MainsOff, LowVolume, NoStandData, NoSPEData, Stands.IdleAlarmSent AS StandIdleAlarmSent, Fault, Stores.IdleAlarmSent AS StoreIdleAlarmSent, TManStandID, StandVersion, CompanyName, min(Version) AS Version
FROM Stands
INNER JOIN Stores ON Stands.StoreID = Stores.StoreID AND Stands.CompanyID = Stores.CompanyID
INNER JOIN ManagersRegions ON Stores.RegionID = ManagersRegions.RegionID
INNER JOIN Managers ON ManagersRegions.ManagerID = Managers.ManagerID
INNER JOIN Companies ON Stores.CompanyID = Companies.CompanyID
INNER JOIN ManagersCompanies ON Managers.ManagerID = ManagersCompanies.ManagerID AND Companies.CompanyID = ManagersCompanies.CompanyID
INNER JOIN StandConfig ON Stands.StandID = StandConfig.StandID
INNER JOIN Karts ON Stores.StoreID = Karts.StoreID AND Stores.CompanyID = Karts.CompanyID
WHERE UserName = 'admin' AND Karts.Active = 1
GROUP BY Stands.StandID, SPEVersion, LastCallTime, Stores.StoreID, Stores.CompanyID, StoreName, NoteJammed, StackerFull, MainsOff, LowVolume, NoStandData, NoSPEData, Stands.IdleAlarmSent, Fault, Stores.IdleAlarmSent, TManStandID, StandVersion, CompanyName
ORDER BY Version
Version is a column in the Karts table.
This works ok if I only want to see stands with active karts. Now I want to see ALL stands but if I remove 'AND Karts.Active = 1' then the ordering doesn't work any more because I still want to order by only active kart minimum versions (and set minimum version to 0 for stands with no active karts).
In other words I only want the Karts.Active to apply to the ordering.
If I could I would put 'ORDER BY Version WHERE Karts.Active = 1' but of course this is not allowed.
Let me know if you need any more info.
November 7, 2005 at 4:32 pm
If Version id itd datatype, use this:
ORDER BY Karts.Active * Version
Is version is varchar,
ORDER BY case when Karts.Active = 1 then Version else '0' end
_____________
Code for TallyGenerator
November 7, 2005 at 5:18 pm
Thanks for the quick reply.
The type is smallint so I tried your first suggestion but I get the following errors:
Column name 'Karts.Active' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
Column name 'Karts.Version' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
I tried adding these 2 to the end of the GROUP BY clause but then it gives me too many rows.
I tried adding the DISTINCT keyword but then I get the error:
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
So I tried adding Karts.Active * Version to the select list but I still get too many rows.
I think for each stand it returns a row for each kart version rather than just the minimum version.
November 7, 2005 at 5:29 pm
Seems your status column is placed into wrong table.
But anyway try to replace
INNER JOIN Karts ON Stores.StoreID = Karts.StoreID AND Stores.CompanyID = Karts.CompanyID
with
INNER JOIN (select distinct StoreId, CompanyId, Active from Karts ) Karts ON Stores.StoreID = Karts.StoreID AND Stores.CompanyID = Karts.CompanyID
If you have different values of Active for the same set of (StoreId, CompanyId) try this:
INNER JOIN (select StoreId, CompanyId, MAX(Active) as Active from Karts group by StoreId, CompanyId) Karts ON Stores.StoreID = Karts.StoreID AND Stores.CompanyID = Karts.CompanyID
Choose aggregate function according to your needs.
_____________
Code for TallyGenerator
November 7, 2005 at 6:05 pm
Thanks, I tried the first one but now I get this error twice:
Invalid column name 'Version'.
Not sure what you mean by status column is in wrong table.
November 7, 2005 at 6:36 pm
Add Versions to select:
INNER JOIN (select StoreId, CompanyId, MAX(Active) as Active, MIN(Version) as Version from Karts group by StoreId, CompanyId) Karts ON Stores.StoreID = Karts.StoreID AND Stores.CompanyID = Karts.CompanyID
By "wrong table" I mean you select Store detais based on and ordered by Karts details. But according to your description you have multiple Karts per Store.
If there are Karts with both statuses for the same store - should this store to be selected?
In such cases you must use aggregate functions in HAVING clause instead of WHERE.
Or try to use in your original select
, min(case when Active = 1 then Version else 0 end ) AS Version
_____________
Code for TallyGenerator
November 8, 2005 at 4:39 pm
Ok thanks, I am getting closer but still not quite there. Here is what I have now (I have changed company to change by the way):
SELECT Stands.StandID, SPEVersion, LastCallTime, Stores.StoreID, Stores.ChainID, StoreName, NoteJammed, StackerFull, MainsOff, LowVolume, NoStandData, NoSPEData, Stands.IdleAlarmSent AS StandIdleAlarmSent, Fault, Stores.IdleAlarmSent AS StoreIdleAlarmSent, TManStandID, StandVersion, ChainName, min(case when Karts.Active = 1 then Version else 0 end) AS Version
FROM Stands
INNER JOIN Stores ON Stands.StoreID = Stores.StoreID AND Stands.ChainID = Stores.ChainID
INNER JOIN ManagersRegions ON Stores.RegionID = ManagersRegions.RegionID
INNER JOIN Managers ON ManagersRegions.ManagerID = Managers.ManagerID
INNER JOIN Chains ON Stores.ChainID = Chains.ChainID
INNER JOIN ManagersChains ON Managers.ManagerID = ManagersChains.ManagerID AND Chains.ChainID = ManagersChains.ChainID
INNER JOIN StandConfig ON Stands.StandID = StandConfig.StandID
INNER JOIN Karts ON Stores.StoreID = Karts.StoreID AND Stores.ChainID = Karts.ChainID WHERE UserName = 'harvest'
GROUP BY Stands.StandID, SPEVersion, LastCallTime, Stores.StoreID, Stores.ChainID, StoreName, NoteJammed, StackerFull, MainsOff, LowVolume, NoStandData, NoSPEData, Stands.IdleAlarmSent, Fault, Stores.IdleAlarmSent, TManStandID, StandVersion, ChainName ORDER BY Version
It seems to order them ok but it seems a lot of minversions are set to 0 as though it is including inactive karts also.
If I can't get it to work soon then I might do it the easy way: put another field in the stands table called MinKartVersion which would be updated automatically by my backend program.
November 8, 2005 at 4:44 pm
Better to have computed column with UDF inside. You will not have to worry about background programs.
_____________
Code for TallyGenerator
November 8, 2005 at 9:58 pm
I have to have a backend program running anyway that inserts data into the database from our units in the field. It also sends text alerts and removes old data etc.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply