Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 9 of 9 total |
Error in second SELECT statement when using UNION, EXIST or INTERSET |
Wed, Nov 29 2017 3:11 PM | Permanent Link |
Richard Harding Wise Nutrition Coaching | Tim
An error occurs in the second SELECT statement. The same SELECT statement used as the first SELECT works fine. An example is listed below. SELECT ID, Status FROM Tasks AS T1 WHERE EXISTS (SELECT * FROM Tasks AS T2 WHERE (T1.ID = T2.ID) AND (T2.Status = 'Recorded') -- OK UNION SELECT * FROM Tasks AS T3 WHERE (T1.ID = T3.ID) AND (T3.Status = 'Assigned')) -- Error -- Error found:line 11: Expected column name express but instead found "T1"."ID" Richard |
Thu, Nov 30 2017 3:33 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Richard
Without testing it out I suspect its trying to apply the WHERE clause to the result set of the two provious statements and having trouble Whilst waiting for Tim this should work SELECT ID, Status FROM Tasks AS T1 WHERE EXISTS (SELECT ID, Status FROM Tasks AS T2 WHERE (T1.ID = T2.ID) AND (T2.Status = 'Recorded') )-- OK OR EXISTS (SELECT ID, Status FROM Tasks AS T3 WHERE (T1.ID = T3.ID) AND (T3.Status = 'Assigned')) )-- Error It may be something you want to use elsewhere but I do wonder about the whole construct. What about something much simpler like SELECT ID, Status FROM Tasks WHERE Status IN ('Recorded,',Assigned') Roy Lambert |
Thu, Nov 30 2017 5:51 AM | Permanent Link |
Richard Harding Wise Nutrition Coaching | <Roy Lambert wrote:
Richard Without testing it out I suspect its trying to apply the WHERE clause to the result set of the two provious statements and having trouble Whilst waiting for Tim this should work.> Thank you Roy, This was just a simple example of a construct that was not working. I saw an example in a book solving a different type of problem. I have completely different existing code to show all patients who have clinical tests that do not have a result.. The book suggested an alternative method is to use EXCEPT but it did not work because of the error. I have never used EXCEPT or INTERSECT before - I thought it would be worthwhile to see how it compared with my original code. Richard |
Thu, Nov 30 2017 6:59 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Richard
Had a bit of a play and it seems that either sub-select is happy by itself its only when you try and combine with a UNION that it goes pear shaped. Roy Lambert |
Thu, Nov 30 2017 7:03 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Richard
Had a bit of a play and it seems that either sub-select is happy by itself its only when you try and combine with a UNION that it goes pear shaped. I'd be interested in seeing the example you're trying to follow. Roy Lambert |
Fri, Dec 1 2017 2:16 AM | Permanent Link |
Richard Harding Wise Nutrition Coaching | <<I'd be interested in seeing the example you're trying to follow.>>
I guess you are at the start of a Glasgow winter - which is going to be a lot different to an Australian winter. I did visit the Burroughs HQ in Scotland, somewhere between Edinburgh and Glasgow, in early January years ago which was not much fun. The sun "rose" at 0900 and set at 1500 - not that you actually saw the sun. Instead of showing patient episodes that do not have tests results, the examples below are from my test DB which has tasks that are assigned to projects and people. CREATE TABLE "Tasks" ( "ID" INTEGER NOT NULL, "ProjectName" VARCHAR(16) COLLATE "ANSI_CI" NOT NULL, "Status" VARCHAR(16) COLLATE "ANSI_CI" DEFAULT 'Entered' NOT NULL, "AssignedTo" VARCHAR(16) COLLATE "ANSI_CI", CONSTRAINT "taCheckStatus" CHECK (Status IN ('Assigned', 'Commenced', 'Completed'), CONSTRAINT "taPrimaryIND" PRIMARY KEY ("ID"), CONSTRAINT "taProjectTasksID" UNIQUE ("ProjectName", "AssignedTo", "ID") ) ===================================== To find out which projects have tasks that are not complete, the following two queries work. SELECT ProjectName, COUNT(*) AS CntAllTasks, COUNT(DateCompleted) CntCompletedTasks FROM Tasks AS T1 GROUP BY ProjectName HAVING COUNT(*) <> COUNT(DateCompleted) SELECT ProjectName, COUNT(*) FROM Tasks AS T1 WHERE EXISTS (SELECT * FROM Tasks AS T2 WHERE (T1.ProjectName = T2.ProjectName) AND (T2.Status <> 'Completed')) GROUP BY ProjectName ===================================== I tried the following which should give the same results. I was just trying things out to see how it works. SELECT ID, ProjectName, AssignedTo, Status FROM Tasks AS T1 WHERE EXISTS (SELECT * FROM Tasks AS T2 WHERE (T1.ProjectName = T2.ProjectName) EXCEPT SELECT * FROM Tasks AS T3 WHERE (T1.ProjectName = T3.ProjectName) AND (T3.Status = 'Completed')) ORDER BY ProjectName, AssignedTo, Status Richard |
Fri, Dec 1 2017 4:46 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Richard
Blows my theory about it being something to do with UNION out of the water. I'll be interested to see what the cause is. Roy Lambert |
Mon, Dec 4 2017 12:37 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Richard,
<< An error occurs in the second SELECT statement. The same SELECT statement used as the first SELECT works fine. An example is listed below. >> I think the issue is that the UNION operation is making ElevateDB think that the T1 correlation name is "out of reach" when it shouldn't. I'll look into this further and let you know what I find out. Tim Young Elevate Software www.elevatesoft.com |
Mon, Dec 4 2017 9:20 PM | Permanent Link |
Richard Harding Wise Nutrition Coaching | <<I think the issue is that the UNION operation is making ElevateDB think that the T1 correlation name is "out of reach" when it shouldn't. I'll look into this further and let you know what I find out. >>
Thank you Richard |
This web page was last updated on Saturday, May 4, 2024 at 09:18 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |