Follow Slashdot stories on Twitter

 


Forgot your password?
Close
typodupeerror
Programming

Journal TechnoLust's Journal: SQL question for you query-jockeys. 24

Journal by TechnoLust
Ok, here's the deal. I'm interfacing with an AS400 using JDBC, and I have a file with all these records. What happens is, when the people manufacturing do a quality test they sign off using the computer and it writes a record that looks like this: Order#, Employee#, QA_Test_Type, Date, Time, etc. So they are supposed to perform four types of tests on every order. So there will be 4 records for order X. One each of QA_Test_Type MFGS, MFG1, MFG2, and MFG3. I need a query that will return the Order#s that DON'T have all 4 of those (i.e. they didn't do test MFG2). I tried using Grouping, but that didn't work. The closest I came was getting a list the orders that did have all of tests done by using UNION. Then I was going to make that a subquery and say Where QA_Test_Type NOT IN (This list), but it won't let me use a UNION query as a subquery for some reason. Any thoughts? Basically need a list of all Order#s that don't have all four QA_Test_Type records. And I can't do anything with returned row counts because there are optional rows that could be there.

UPDATE: Ok, thanks to all your help, and combining ideas, I came up with an SQL query that reports only the ones that don't have all 4 MFG? records. MFG3 is the final check, so we don't want to include the ones that don't have that in the list, since they are still being manufactured. Here it is:

SELECT * FROM SFMP00 t0 WHERE t0.Type = 'MFG3' AND t0.OrdNo NOT IN (SELECT t1.OrdNo FROM SFMP00 t1 JOIN SFMP00 t2 ON t1.OrdNo = t2.OrdNo JOIN SFMP00 t3 ON t1.OrdNo = t3.OrdNo JOIN SFMP00 t4 ON t1.OrdNo = t4.OrdNo WHERE t1.Type = 'MFGS' AND t2.Type = 'MFG1' AND t3.Type = 'MFG2' AND t4.Type = 'MFG3') ORDER BY OrdNo

Special thanks to johndiii, since his was fastest as worked best, and became the base of the above. I'll try to pull some strings and get you some more DragonPointses... ;-)

This discussion has been archived. No new comments can be posted.

SQL question for you query-jockeys. More

SQL question for you query-jockeys.

Comments Filter:
  • In something simple, like Access, if you wanted a table of the stuff that is missing various entries you would build a Make Table Query based on:

    MFGS is NULL OR MFG1 is NULL OR MFG2 is NULL or MFG3 is NULL

    The SQL might look something like this

    SELECT Table1.MFGS, Table1.MFG1, Table1.MFG2, Table1.MFG3 INTO table2
    FROM Table1
    WHERE (((Table1.MFGS) Is Null)) OR (((Table1.MFG1) Is Null)) OR (((Table1.MFG2) Is Null)) OR (((Table1.MFG3) Is Null));

    Depending on the data in the fields you might have to testr for

    • You misunderstand... There is not a field called MFGS or MFG1, there is a field called TYPE, that contains, MFGS, MFG1, MFG2, MFG3, or one of several othere QA Test Type codes. I need to look at the ones where the ORDERNO has one of each of those in different records. So I would have:
      ORDER# - TYPE - EMPLOYEE# - DATE - TIME
      12 - MFGS - 00598 - 10122003 - 135403
      13 - MFGS - 00315 - 10122003 - 135445
      12 - MFG1 - 00598 - 10122003 - 140145
      12 - MFG2 - 00598 - 10122003 - 142205
      etc.
  • YMMV but this is the way I see it:
    Perl/php aren't strongly typed (by default), a boolean, a string, and a scalar are all one in the same, so you can just do a bool test on all four fields, if they are all there, all are true, if one is null/empty, it would be false, that could be your flag.
  • Rather than a union. Something like this (assuming the table is called QATest):

    select t0.OrderNum from QATest t0 where t0.OrderNum not in
    (select t1.OrderNum from QATest t1
    join QATest t2 on t1.OrderNum = t2.OrderNum
    join QATest t3 on t1.OrderNum = t2.OrderNum
    join QATest t4 on t1.OrderNum = t2.OrderNum
    where t1.QA_Test_Type = 'MFGS'
    and t2.QA_Test_Type = 'MFG1'
    and t3.QA_Test_Type = 'MFG2'
    and t4.QA_Test_Type = 'MFG3')

    Or something like that, anyway. Since I don't

    • Oops:
      select t0.OrderNum from QATest t0 where t0.OrderNum not in
      (select t1.OrderNum from QATest t1
      join QATest t2 on t1.OrderNum = t2.OrderNum
      join QATest t3 on t1.OrderNum = t3.OrderNum
      join QATest t4 on t1.OrderNum = t4.OrderNum
      where t1.QA_Test_Type = 'MFGS'
      and t2.QA_Test_Type = 'MFG1'
      and t3.QA_Test_Type = 'MFG2'
      and t4.QA_Test_Type = 'MFG3')

      • But isn't he looking for nulls in in the MFGx fields? I thought all of the rows/records he wants had a OrderNum entry but were missing one or more entries in the MFG fields.
      • That is a good strategy. I like it better than the one I proposed.

        One comment: NOT EXISTS is frequently a better performing operator than NOT IN. Of course, my experience is strictly in the Oracle world, so that may not translate well outside of Oracle.

        From the description, it seems that OrderNum and QA_Test_Type form a composite primary key? Are they declared as such? (Do they have a unique constraint and and index?) If not this is going to result in some hellacious full table scans.

        • My implementation doesn't allow NOT EXISTS (or at least not there). I'd have to talk to the DBA, but I belive OrderNum is a primary key. In any case, it's pretty freaking fast. Of course, it's running on one of the beefiest AS/400s available...
      • That nailed it. I just had to add some things to get the information I need for the screen, and now we're golden. I updated the JE to include the final string. I'll talk to Sol about getting you a title befitting of SQL royalty. ;-)
        • A question to pose to others, if they're still listening:

          Would it be faster to perform four discreet subqueries instead of dealing with the relationships? The additional overhead of comparing the four tables in the subquery is avoided this way, but I suppose it depends on the overhead of IN as well. Something like this:

          SELECT * FROM SFMP00
          WHERE OrdNo NOT IN
          (SELECT OrdNo FROM SFMP00
          WHERE type = "MFGS")
          AND OrdNo NOT IN
          (SELECT OrdNo FROM SFMP00
          WHERE type = "MFG1")
          AND OrdNo NOT IN
          (SELECT OrdNo FROM SFMP00
          • I'll do some time trials on it tomorrow if I have a chance.

          • I think you want to use ORs instead. That query will return OrdNo where no test have been perfomred.

            The real answer to your question is "It depends!" Ideally, your SQL engine should optimize that expression just as well as the one that johndiii proposed. Back here in the real world, you'd probably need to benchmark to be sure. Different SQL products will work differently. Within a single SQL product like Oracle, it would depend on your selection of Cost or Rule based optimization, the accuracy of the

            • Oops is brought to you by erasers.
              Don't make a mistake without one.

              I had another layer of subquery in there, and thought that it was irrelevant, so I just trimmed it. Ah, well. :)
          • Actually, this one takes considerably longer than the one I put in the update. 5s vs. 60+s.
            • Hrm. Probably the combination of five queries rather than one and the ORs instead of ANDs, which require more processing. Record count also probably comes into play. With five comparisons of the entire record set instead of one and a bit of matching, I shouldn't have been suprised.
  • This is in an Oracle Dialect of SQL but...

    SELECT DISTINCT t.order_id
    FROM tests t
    , tests ts
    , tests t1
    , tests t2
    , tests t3
    WHERE t.order_id = ts.order_id(+)
    AND t.order_id = t1.order_id(+)
    AND t.order_id = t2.order_id(+)
    AND t.order_id = t3.order_id(+)
    AND NVL(ts.test_type, 'MFGS') = 'MFGS'
    AND NVL(t1.test_type, 'MFG1') = 'MFG1'
    AND NVL(t2.test_type, 'MFG2') = 'MFG2'
    AND NVL(t3.test_type, 'MFG3') = 'MFG3'
    AND ( ts.test_type IS NULL
    OR t1.test_type IS NULL
    OR t1.test_type IS NULL
    OR t1.test_type IS NUL

  • Maybe not the best way, but:

    NOTE: assumes the table is called "file".

    SELECT file.order
    FROM file
    WHERE file.order NOT IN
    ( SELECT f1.order
    FROM file f1, file f2, file f3, file f4
    WHERE f2.order = f1.order
    AND f3.order = f1.order
    AND f4.order = f1.order
    AND f1.test_type = "MFGS"
    AND f2.test_type = "MFG1"
    AND f3.test_type = "MFG2"
    AND f4.test_type = "MFG3" );

  • SELECT order# FROM tests
    GROUP BY order#
    HAVING count(order#) 4;

    Done. :)


    • Bah, I didn't read your whole post.

      Sorry!

      Well, this might work:

      SELECT order# from tests
      WHERE TEST in (MFG1, ...)
      GROUP BY order#
      HAVING count(order#) < 4.
      • Whoa! Hold on! Yes, this is simple but do something like this:

        SELECT order# from tests
        WHERE TEST Like (MFG*)
        GROUP BY order#
        HAVING count(order#) 4.

        Not sure if that is the proper syntax for the wildcard in what you use.
        • This is the approach I have used in many situations and it tends to not only be much simpler code, but it is typically a lot faster than some of the alternative methods.

          However, the TEST IN (MFG1, MFG2, ...) version could be faster than your LIKE version unless an appropriate index is available on TEST. Test both methods to see which works best for the particular set of data.

Slashdot Top Deals

Time is the most valuable thing a man can spend. -- Theophrastus

Close