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

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

<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

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

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

<<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

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

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

<<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