Click here to Skip to main content
15,882,152 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am try to update an existing query to include 2 subqueries each computing a different count. The main query runs fine on its own and I have been able to get the 2 subqueries to run on their own, however, when I try to join them, I am getting a syntax error "missing keyword".

Here is my main query. It takes the inspection data from 3 separate tables and summarizes it by the job number to show each time the inspection program was ran. The inspection data is split up into the different tables based on the feature type (holes, zaxis or corner). The results for each job number can be in found in just 1 of these 3 tables or multiple tables. Because of this, that is why there is a MIN function in the main query so I end up with one entry per job number. Another thing to note is each table can then have one or more probe features of the same type with the difference being in a different location.
SQL


SELECT MIN(t.timeofentry) as timeofentry, t.machine, t.tape, t.partnumber, t.spindle, t.jobnumber 
from (
     SELECT timeofentry, machine, tape, partnumber, spindle, jobnumber
      FROM PROBEVALIDATIONHOLE
      WHERE HOLENUMBER = 1 AND TIMEOFENTRY between '01/01/2024 12:00:00 AM' and '01/12/2024 11:59:59 PM' and Machine = 'NC-537'
      UNION
      SELECT timeofentry, machine, tape, partnumber, spindle, jobnumber
      FROM PROBEVALIDATIONZAXIS
      WHERE ZHIT = 1 AND TIMEOFENTRY between '01/01/2024 12:00:00 AM' and '01/12/2024 11:59:59 PM' and Machine = 'NC-537'
      UNION
      SELECT timeofentry, machine, tape, partnumber, spindle, jobnumber
      FROM PROBEVALIDATIONCORNER
      WHERE TIMEOFENTRY between '01/01/2024 12:00:00 AM' and '01/12/2024 11:59:59 PM' and Machine = 'NC-537'
      )t 
      GROUP BY t.machine, t.tape, t.partnumber, t.spindle, t.jobnumber
      ORDER BY timeofentry


This returns data like:
TimeofEntry, Machine, Tape, PartNumber, Spindle, JobNumber
01/02/2024 11:13:50 PM, NC-537, 4479, ABC, LH, 75576
01/02/2024 11:25:50 PM, NC-537, 4479, ABC, RH, 75577

Now I want to add 2 counts to the query to show both the total number of features inspected for the job number and the total number of features that were out of tolerance for that job number. Something like this:

TimeofEntry, Machine, Tape, PartNumber, Spindle, JobNumber, Failed, Total
01/02/2024 11:13:50 PM, NC-537, 4479, ABC, LH, 75576, 0, 4
01/02/2024 11:25:50 PM, NC-537, 4479, ABC, RH, 75577, 2, 4

Here are the subqueries for each. On the one that includes the failed features, I think I will need a COALESCE in there somewhere as not every job has failed features so the subquery may return null sometimes

This one includes the number of failed features:
SQL


select jobnumber, sum(fails)as TotalFails
from(
select jobnumber, count(*) as fails from probevalidationhole where TIMEOFENTRY between '01/01/2024 12:00:00 AM' and '01/12/2024 11:59:59 PM' and Machine = 'NC-537' and (xoutoftol = 'Yes' or youtoftol = 'Yes') group by jobnumber
union
select jobnumber, count(*) as fails from probevalidationzaxis where TIMEOFENTRY between '01/01/2024 12:00:00 AM' and '01/12/2024 11:59:59 PM' and Machine = 'NC-537' and Zoutoftol = 'Yes' group by jobnumber
union
select jobnumber, count(*) as fails from probevalidationcorner where TIMEOFENTRY between '01/01/2024 12:00:00 AM' and '01/12/2024 11:59:59 PM' and Machine = 'NC-537' and (xoutoftol = 'Yes' or youtoftol = 'Yes') group by jobnumber)
group by jobnumber


This one includes the total number of features:
SQL


select jobnumber, sum(features)as TotalFeatures
from(
select jobnumber, count(*) as features from probevalidationhole where TIMEOFENTRY between '01/01/2024 12:00:00 AM' and '01/12/2024 11:59:59 PM' and Machine = 'NC-537' group by jobnumber
union
select jobnumber, count(*) as features from probevalidationzaxis where TIMEOFENTRY between '01/01/2024 12:00:00 AM' and '01/12/2024 11:59:59 PM' and Machine = 'NC-537' group by jobnumber
union
select jobnumber, count(*) as features from probevalidationcorner where TIMEOFENTRY between '01/01/2024 12:00:00 AM' and '01/12/2024 11:59:59 PM' and Machine = 'NC-537' group by jobnumber)
group by jobnumber


What I have tried:

Here is what I was trying when I got the syntax error "missing keyword". I was just trying to add the total features first and then do the failed features but I can't seem to get just the total features to work.

SQL


SELECT MIN(t.timeofentry) as timeofentry, t.machine, t.tape, t.partnumber, t.spindle, t.jobnumber, TF.totalfeatures
from (
     SELECT timeofentry, machine, tape, partnumber, spindle, jobnumber
      FROM PROBEVALIDATIONHOLE
      WHERE HOLENUMBER = 1 AND TIMEOFENTRY between '01/01/2024 12:00:00 AM' and '01/12/2024 11:59:59 PM' and Machine = 'NC-537'
      UNION
      SELECT timeofentry, machine, tape, partnumber, spindle, jobnumber
      FROM PROBEVALIDATIONZAXIS
      WHERE ZHIT = 1 AND TIMEOFENTRY between '01/01/2024 12:00:00 AM' and '01/12/2024 11:59:59 PM' and Machine = 'NC-537'
      UNION
      SELECT timeofentry, machine, tape, partnumber, spindle, jobnumber
      FROM PROBEVALIDATIONCORNER
      WHERE TIMEOFENTRY between '01/01/2024 12:00:00 AM' and '01/12/2024 11:59:59 PM' and Machine = 'NC-537'
      )t
      left join
      (select jobnumber, sum(features)as TotalFeatures
      from(
      select jobnumber, count(*) as features from probevalidationhole where TIMEOFENTRY between '01/01/2024 12:00:00 AM' and '01/12/2024 11:59:59 PM' and Machine = 'NC-537' group by jobnumber
      union
      select jobnumber, count(*) as features from probevalidationzaxis where TIMEOFENTRY between '01/01/2024 12:00:00 AM' and '01/12/2024 11:59:59 PM' and Machine = 'NC-537' group by jobnumber
      union
      select jobnumber, count(*) as features from probevalidationcorner where TIMEOFENTRY between '01/01/2024 12:00:00 AM' and '01/12/2024 11:59:59 PM' and Machine = 'NC-537' group by jobnumber)
      group by jobnumber) as TF
      on t.jobnumber = TF.jobnumber
      GROUP BY t.machine, t.tape, t.partnumber, t.spindle, t.jobnumber, tf.totalfeatures
      ORDER BY timeofentry
Posted

1 solution

I figured it out. It was a stupid typo. On the 4th line from the bottom I had "as TF", should have just been just "TF"
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900