Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 5 of 5 total |
Different results for 2 different versions of the HAVING clause |
Sun, Jun 3 2012 8:57 PM | Permanent Link |
Richard Harding Wise Nutrition Coaching | Greetings . . .
I am using Elevate DB 2.05b6. I have a table CREATE TABLE "Tests" ("TestGroup" VARCHAR(5) COLLATE "ANSI_CI" NOT NULL, "Test" VARCHAR(5) COLLATE "ANSI_CI" NOT NULL, CONSTRAINT "indPrimaryKey" PRIMARY KEY ("TestGroup", "Test")) The table stores pathology tests that are grouped into TestGroups. Sample data is: TestGroup, Test ------------------- FAT, CHOL FAT, HDL FAT, LDL FAT, TRIG FATS, CHOL FATS, HDL FATS, LDL FATS, TRIG LIPID, HDL LIPID, LDL I want to find which groups are duplicated. In the above list, FATS and FAT contain the same tests. The following query gives a list of the TestGroups that have a common Test. This works properly. SELECT T1.TestGroup, T2.TestGroup, T1.Test FROM Tests AS T1 INNER JOIN Tests AS T2 ON T1.Test = T2.Test AND T1.TestGroup < T2.TestGroup GROUP BY T1.TestGroup, T2.TestGroup, T1.Test TestGroup1, TestGroup2, Test FAT, FATS, CHOL FAT, FATS, HDL FAT, FATS, LDL FAT, FATS, TRIG FAT, LIPID, HDL FAT, LIPID, LDL FATS, LIPID, HDL FATS, LIPID, LDL Next step: ------------ Grouping the results on T1.TestGroup, T2.TestGroup and counting the number of rows, I get different results for 2 different versions of the HAVING clause. SELECT T1.TestGroup, T2.TestGroup, Count(*) AS C1, Count(T2.Test) AS C2, (SELECT COUNT(*) FROM Tests AS T3 WHERE T3.TestGroup = T1.TestGroup) AS C3, (SELECT COUNT(*) FROM Tests AS T4 WHERE T4.TestGroup = T2.TestGroup) AS C4 FROM Tests AS T1 INNER JOIN Tests AS T2 ON T1.Test = T2.Test AND T1.TestGroup < T2.TestGroup GROUP BY T1.TestGroup, T2.TestGroup HAVING --count(*) = (SELECT COUNT(*) FROM Tests AS T5 WHERE T5.TestGroup = T1.TestGroup) AND --count(*) = (SELECT COUNT(*) FROM Tests AS T6 WHERE T6.TestGroup = T2.TestGroup) C1 = C3 AND C2 = C4 Using HAVING C1 = C3 AND C2 = C4 gives the correct results. That is, FATS, FAT Using HAVING COUNT(*) = (SELECT COUNT(*) FROM Tests AS T5 WHERE T5.TestGroup = T1.TestGroup) AND COUNT(*) = (SELECT COUNT(*) FROM Tests AS T6 WHERE T6.TestGroup = T2.TestGroup) does not return any rows. I think they should be the same or is there something that I am missing? |
Mon, Jun 4 2012 8:16 AM | Permanent Link |
John Hay | Richard,
As far as I can see the syntax looks equivalent. It looks like the correlated subquery in the having clause is not being evaluated correctly. John |
Mon, Jun 4 2012 10:02 PM | Permanent Link |
Richard Harding Wise Nutrition Coaching | Thank you John.
<<As far as I can see the syntax looks equivalent. It looks like the correlated subquery in the having clause is not being evaluated correctly.>> Richard Harding |
Wed, Jun 20 2012 2:01 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Richard,
<< I think they should be the same or is there something that I am missing? >> Did you try this with the latest version to see if the results are different ? If they aren't, please send me the database catalog and relevant tables via email, and I'll take a look. Thanks, Tim Young Elevate Software www.elevatesoft.com |
Thu, Jun 28 2012 8:56 PM | Permanent Link |
Richard Harding Wise Nutrition Coaching | <<Did you try this with the latest version to see if the results are different? If they aren't, please send me the database catalog and relevant tables via email, and I'll take a look.>>
Thanks Tim I'll wait until the next release and give it a go. Richard Harding |
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 |