Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 4 of 4 total |
qry problem |
Wed, Aug 20 2008 1:16 PM | Permanent Link |
David | I have a query which won't work for me. I have the following in the query :-
SELECT MAX(m.inspectionid), e.systemid, firsttested, m.inspDate, e.equipid, e.equipDesc, e.swl, m.firstexamyn, m.firstexamYcorrectInstallyn, m.examWithin6MthYN, m.examWithin12MthYN, m.InAccWithExamSchemeYN, m.AfterExcepCircsYN, m.IDofDefectPart, m.DefectOfImmDangerYN, m.couldbedangerby, m.repairtoremedydefect, m.testdetailsaspartofexam, m.inspby, m.authenticatedby, m.mncehistid, m.nextinsp, m.safetoOperate, m.couldbecomedangeryn, e.print FROM Equipment e LEFT OUTER JOIN mnceHistory m ON e.SystemID = m.MnceHistID WHERE e.print = true GROUP BY e.systemid I get the message inspectionid field not found. If I cut the text and run it on the sql page in the database system utility I get a result table. No doubt I am missing the blindingly obvious somewhere if anyone can assist I'll be very grateful. Regards David |
Thu, Aug 21 2008 10:41 AM | Permanent Link |
David | Penny dropped.
It was a query which I was altering, when I used the field editor and deleted all fields and then added them back the query worked. |
Fri, Aug 22 2008 4:35 AM | Permanent Link |
"John Hay" | David
> SELECT MAX(m.inspectionid), e.systemid, firsttested, m.inspDate, e.equipid, e.equipDesc, > e.swl, m.firstexamyn, > m.firstexamYcorrectInstallyn, m.examWithin6MthYN, m.examWithin12MthYN, > m.InAccWithExamSchemeYN, m.AfterExcepCircsYN, m.IDofDefectPart, m.DefectOfImmDangerYN, > m.couldbedangerby, m.repairtoremedydefect, m.testdetailsaspartofexam, m.inspby, > m.authenticatedby, m.mncehistid, m.nextinsp, m.safetoOperate, m.couldbecomedangeryn, e.print > FROM Equipment e LEFT OUTER JOIN mnceHistory m ON e.SystemID = m.MnceHistID > WHERE e.print = true > GROUP BY e.systemid I know you have fixed the field definition problem with this query but I suspect it may not give you the results you expect. Presuming that the relationship from Equipment to mnceHistory is 1 to many selecting MAX(m.inspectionid) does not ensure that the other non aggregate fields from mnceHistory will be from the same row as MAX(m.inspectionid). In a lot (most?) of SQL implementations you are not allowed to include non aggregate fields which are not part of the GROUP BY clause. I'm sure Tim will correct me if I'm wrong John |
Fri, Aug 22 2008 11:58 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | John,
<< In a lot (most?) of SQL implementations you are not allowed to include non aggregate fields which are not part of the GROUP BY clause. I'm sure Tim will correct me if I'm wrong >> No, you're correct. DBISAM and EDB are fairly unique/non-standard in that they allow this, warts and all. -- Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Saturday, April 27, 2024 at 08:52 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |