Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2009
    Posts
    67

    Unanswered: Query masters, I bow to you: Matrix question

    I'm developing an application to test hazardous materials in products. It's kinda complex, but not.

    I'm not sure if this is a matrix/cross tab type query, but let me propose it to you anyway.

    I have a product report of it's contents stored in a normalized Access db:
    Code:
    +--------------+------------------------------+---------------+-----------+-------------+
    | Material     | CAS Material Name            | CAS Number    | Weight    | PPM         |
    +--------------+------------------------------+---------------+-----------+-------------+
    | pet    pet   | PET                          | 25038-59-9    | 0.8       | 914285.7143 |
    | pet    pet   | SiO2                         | 60676-86-0    | 0.05      | 57142.85714 |
    | pet    pet   | Acrylic polymer              | 37325-11-4    | 0.025     | 28571.42857 |
    +--------------+------------------------------+---------------+-----------+-------------+
    | opp    opp   | Polypropylene                | 9003-07-0     | 0.05      | 666666.6667 |
    | opp    opp   | Acrylic polymer              | 37325-11-4    | 0.025     | 333333.3333 |
    +--------------+------------------------------+---------------+-----------+-------------+
    | ink    ink   | Distillates (petroleum)      | 64742-46-7    | 0.0225    | 450000      |
    | ink    ink   | Rosin                        | 68082-96-2    | 0.0175    | 350000      |
    | ink    ink   | Epoxidized linseed oil       | 8016-11-3     | 0.004     | 80000       |
    | ink    ink   | Soybean-oil                  | 8001-22-7     | 0.005     | 100000      |
    | ink    ink   | PE                           | 9002-88-4     | 0.0005    | 10000       |
    | ink    ink   | Iron manganese zinc oxide    | 12645-49-7    | 0.00025   | 5000        |
    | ink    ink   |  Cobalt bis(2-ethylhexanoate)| 136-52-7      | 0.00025   | 5000        |
    +--------------+------------------------------+---------------+-----------+-------------+
    etc etc etc
    The is grouped to show the separation in "homogeneous materials". As in the 3 different groups. You will notice that the last column (parts per million) sums to a million.

    Then I have a list of hazardous substances contained in the same database. I need to know, even if its 0ppm if EACH homogeneous material has less than the limit imposed.

    Here is an example. It shows the 'group', the limit, and the material that makes the 'compound':
    Code:
    +-------------------------------+-------+-----------------------+------------+
    | Material                      | PPM   | CAS Material Name     | CAS Number |
    +-------------------------------+-------+-----------------------+------------+
    | Cadmium and its compounds     | 100   | Cadmium               | 7440-43-9  |
    | Cadmium and its compounds     | 100   | Cadmium oxide         | 1306-19-0  |
    | Cadmium and its compounds     | 100   | Cadmium sulfide       | 1306-23-6  |
    | Cadmium and its compounds     | 100   | Cadmium chloride      | 10108-64-2 |
    | Cadmium and its compounds     | 100   | Cadmium sulfate       | 10124-36-4 |
    +-------------------------------+-------+-----------------------+------------+
    | Chromium VI and its compounds | 0     | Chromium (VI) oxide   | 1333-82-0  |
    | Chromium VI and its compounds | 0     | Barium chromate       | 10294-40-3 |
    | Chromium VI and its compounds | 0     | Calcium chromate      | 13765-19-0 |
    | Chromium VI and its compounds | 0     | Chromic acetate       | 1066-30-4  |
    | Chromium VI and its compounds | 0     | Chromium trioxide     | 1333-82-0  |
    | Chromium VI and its compounds | 0     | Lead (II) chromate    | 7758-97-6  |
    | Chromium VI and its compounds | 0     | Sodium chromate       | 7775-11-3  |
    | Chromium VI and its compounds | 0     | Sodium dichromate     | 10588-01-9 |
    | Chromium VI and its compounds | 0     | Strontium chromate    | 7789-06-2  |
    | Chromium VI and its compounds | 0     | Potassium dichromate  | 7778-50-9  |
    | Chromium VI and its compounds | 0     | Potassium chromate    | 7789-00-6  |
    | Chromium VI and its compounds | 0     | Zinc chromate         | 13530-65-9 |
    +-------------------------------+-------+-----------------------+------------+
    | Mercury and its compounds     | 1000  | Mercury               | 7439-97-6  |
    | Mercury and its compounds     | 1000  | Mercuric chloride     | 33631-63-9 |
    | Mercury and its compounds     | 1000  | Mercury (II) chloride | 7487-94-7  |
    | Mercury and its compounds     | 1000  | Mercuric sulfate      | 7783-35-9  |
    | Mercury and its compounds     | 1000  | Mercuric nitrate      | 10045-94-0 |
    | Mercury and its compounds     | 1000  | Mercuric (II) oxide   | 21908-53-2 |
    | Mercury and its compounds     | 1000  | Mercuric sulfide      | 1344-48-5  |
    +-------------------------------+-------+-----------------------+------------+
    etc etc etc etc
    Basically, this test is pass or fail and I think this might be able to be done in a query. Also know that I will need to run this against up to an infinite number of product reports and I will have up to an infinite number of list of hazardous substances. But it passes or fails at homogeneous material (ie "pet" or "ink"), so I would have to test all the material in that homogeneous material against all of the list of materials in a compound. Make sense?

    If I could generate a query that would give me a result of comparing the product to the limitations simply passing or failing, that'd be great. I don't need to know if it passes one material or not, fails one material or not. I can imagine the results looking like this:
    Code:
    +-------------------------------------+
    |     | Cadmium  | Chromium | Mercury | etc -> 
    +-----|----------|----------|---------+
    | pet | True     | True     | True    | etc -> 
    +-----|----------|----------|---------+
    | opp | True     | True     | True    | etc ->
    +-----|----------|----------|---------+
    | ink | False    | True     | True    | etc ->
    +-------------------------------------+
      etc
       |
       V
    And my application would just check for any falses.

    Is this possible in queries alone or would it just be easier to do in my application (Classic ASP!)?
    Last edited by LAYGO; 01-18-10 at 13:50.

  2. #2
    Join Date
    Apr 2009
    Posts
    12
    I'm almost, but not completely, sure that I'm missing the point here. But just in case you want to have a query that will show you all Homogeneous Materials where a ingredient passes its limits, the following would do the trick:

    SELECT HomMat.Material, HazMat.Material, HazMat.PPM, HomMat.PPM
    FROM HomMat INNER JOIN HazMat ON HomMat.Cas_Number = HazMat.CAS_Number
    WHERE HazMat.PPM > HomMat.PPM;

    If this query returns 0 results, no substance in any homogeneous material will exceed limits

    First time I answer a question on SQL, so if I'm misunderstanding you, forgive me.

  3. #3
    Join Date
    Oct 2009
    Posts
    67
    Bottom line is, per homogeneous material, I need to find out if it passes or fails the hazardous material limit & generate a report and store the results.

    Thing is, a homogeneous material can be a several things & needs to be compared to a list of things. I can do this and extensively in code, I'm just wondering if there is a way to do it via SQL only.

    I've since come up with a query to give me ONLY the reports that have a CAS Number that's in the hazardous CAS list. That's the first "test" similar to yours. On the data I've presently stored in my DB, none of them have any material in the hazardous material list and on that token "passes" the test.

    Thinking about it now, I should modify my query to include the CAS Number and the amount in the homogeneous material of the product instead of the WHOLE product. Then compare if the sum of those are less than the limit.

    I'm just trying to simplify this as much as possible vs just looping through all the product reports & compare if the CAS is in the hazardous list & then compare if it's less than or greater than the allowable limit.

    But looking at your query example, I think I realized I've been missing the point & that I can query the results, but I still need to get my noodle around it more.
    Last edited by LAYGO; 01-18-10 at 18:27.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so I'm guessing you want a sub query which checks that the value of the constituent parts is not equal to your trigger level, and use that as a feed stock to the top level query which reports all the rows referring to that material
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Oct 2009
    Posts
    67
    How about this. Here's the schema for the relevant data.

    MDF* contains the product report data. MDF is the report, the MDFData is each line of the report with the weight/ppm & CAS Info.
    Standard* contains the hazardous material data.

    This query got me JUST the MDFs with a CAS from the Standards:
    Code:
    SELECT                                                                
          MDF.MDFid                                                       
        , Vendors.VendorID                                                
        , Vendors.VendorName                                              
        , Vendors.WebLink                                                 
        , MDF.DateAdded                                                   
        , MDF.DateMDF                                                     
        , MDF.ComponentType                                               
        , MDF.IsHalogenFree                                               
        , MDF.DfeInfoOnFile                                               
        , MDF.GramWeight                                                  
        , MDFData.MDFDataID                                               
        , MDFName_1.MDFName                                               
          AS CompFragName                                                 
        , MDFName.MDFName                                                 
          AS HomoMatName                                                  
        , CASNumber.CASNumber                                             
        , CASNumber.MaterialName                                          
        , MDFData.SubstanceMass                                           
        , MDFData.SubstancePPM                                            
        , MDF.MDFxlsPath                                                  
        , Series.SeriesID                                                 
        , Series.SeriesName                                               
        , MDF.MDFNote                                                     
        , MDFData.Exemption                                               
    FROM Vendors                                                          
    RIGHT JOIN (Series                                                    
    RIGHT JOIN (MDFName AS MDFName_1                                      
    RIGHT JOIN (MDFName                                                   
    RIGHT JOIN (CASNumber                                                 
    RIGHT JOIN (MDF                                                       
    RIGHT JOIN MDFData                                                    
    ON MDF.MDFid                                                          
       =                                                                  
       MDFData.MDFid)                                                     
    ON CASNumber.CASNumberID                                              
       =                                                                  
       MDFData.CASNumberID)                                               
    ON MDFName.MDFNameID                                                  
       =                                                                  
       MDFData.CompFragmentNameID)                                        
    ON MDFName_1.MDFNameID                                                
       =                                                                  
       MDFData.HomogeneousNameID)                                         
    ON Series.MDFid                                                       
       =                                                                  
       MDF.MDFid)                                                         
    ON Vendors.VendorID                                                   
       =                                                                  
       Series.VendorID                                                    
    WHERE MDF.MDFid                                                       
        IN (                                                              
            SELECT DISTINCT                                               
                MDFData.MDFid                                             
            FROM MDF INNER JOIN MDFData                                   
            ON MDF.MDFid=MDFData.MDFid                                    
            WHERE MDFData.CASNumberID                                     
                IN (                                                      
                    SELECT DISTINCT                                       
                        StandardMaterialList.CASNumberID                  
                    FROM CASNumber                                        
                    INNER JOIN (Standard                                  
                    INNER JOIN (StandardCompoundMaterial                  
                    INNER JOIN StandardMaterialList                       
                    ON StandardCompoundMaterial.StandardCompoundMaterialID
                       = StandardMaterialList.StandardCompoundMaterialID) 
                    ON Standard.StandardID                                
                       = StandardMaterialList.StandardID)                 
                    ON CASNumber.CASNumberID                              
                       = StandardMaterialList.CASNumberID                 
                    WHERE Standard.StandardID =      " & inSTDid & "      
                    ORDER BY StandardMaterialList.CASNumberID             
                    )                                                     
            )                                                             
    ORDER BY                                                              
          Series.SeriesID                                                 
        , MDFName_1.MDFName                                               
        , MDFName.MDFName                                                 
        , MDFData.SubstanceMass DESC                                      
        , CASNumber.MaterialName
    I guess getting from this to just a list of MDFids of ones that fail is the important thing. The remaining MDFs will pass by default. So, figuring out how to apply the standard limit to this query & trim the fat is where I'm at.

  6. #6
    Join Date
    Oct 2009
    Posts
    67
    Quote Originally Posted by healdem View Post
    so I'm guessing you want a sub query which checks that the value of the constituent parts is not equal to your trigger level, and use that as a feed stock to the top level query which reports all the rows referring to that material
    I am subquerying in the query I just provided. That alone gives me the MDFs with a CAS in the Standard. I guess figuring out the subquery to test if the reported material is >= to the limit is where my noodle is getting blended.

    And at the time of import of the standard, I'm running it against the entire DB of MDFs. I will only ever run 1 standard against the MDF at a time for now. Later I will be given a list of all products in a BOM (Bill of Materials) which could be on the order of a hundred or so parts, which could be 1:1 or many:1 MDFs and run against several standards. *NOODLE BOILING*
    Last edited by LAYGO; 01-18-10 at 18:57.

  7. #7
    Join Date
    Oct 2009
    Posts
    67
    This query gives me just the material that's found in the standard now. It gives me about 1/4th the results. How I could join on the standard data too to include the PPM limit? HMMM.

    Code:
    SELECT                                                                
          MDF.MDFid                                                       
        , Vendors.VendorID                                                
        , Vendors.VendorName                                              
        , Vendors.WebLink                                                 
        , MDF.DateAdded                                                   
        , MDF.DateMDF                                                     
        , MDF.ComponentType                                               
        , MDF.IsHalogenFree                                               
        , MDF.DfeInfoOnFile                                               
        , MDF.GramWeight                                                  
        , MDFData.MDFDataID                                               
        , MDFName_1.MDFName                                               
          AS CompFragName                                                 
        , MDFName.MDFName                                                 
          AS HomoMatName                                                  
        , CASNumber.CASNumber                                             
        , CASNumber.MaterialName                                          
        , MDFData.SubstanceMass                                           
        , MDFData.SubstancePPM                                            
        , MDF.MDFxlsPath                                                  
        , Series.SeriesID                                                 
        , Series.SeriesName                                               
        , MDF.MDFNote                                                     
        , MDFData.Exemption                                               
    FROM Vendors                                                          
    RIGHT JOIN (Series                                                    
    RIGHT JOIN (MDFName AS MDFName_1                                      
    RIGHT JOIN (MDFName                                                   
    RIGHT JOIN (CASNumber                                                 
    RIGHT JOIN (MDF                                                       
    RIGHT JOIN MDFData                                                    
    ON MDF.MDFid                                                          
       =                                                                  
       MDFData.MDFid)                                                     
    ON CASNumber.CASNumberID                                              
       =                                                                  
       MDFData.CASNumberID)                                               
    ON MDFName.MDFNameID                                                  
       =                                                                  
       MDFData.CompFragmentNameID)                                        
    ON MDFName_1.MDFNameID                                                
       =                                                                  
       MDFData.HomogeneousNameID)                                         
    ON Series.MDFid                                                       
       =                                                                  
       MDF.MDFid)                                                         
    ON Vendors.VendorID                                                   
       =                                                                  
       Series.VendorID                                                    
    WHERE MDF.MDFid                                                       
        IN (                                                              
            SELECT DISTINCT                                               
                MDFData.MDFid                                             
            FROM MDF INNER JOIN MDFData                                   
            ON MDF.MDFid=MDFData.MDFid                                    
            WHERE MDFData.CASNumberID                                     
                IN (                                                      
                    SELECT DISTINCT                                       
                        StandardMaterialList.CASNumberID                  
                    FROM CASNumber                                        
                    INNER JOIN (Standard                                  
                    INNER JOIN (StandardCompoundMaterial                  
                    INNER JOIN StandardMaterialList                       
                    ON StandardCompoundMaterial.StandardCompoundMaterialID
                       = StandardMaterialList.StandardCompoundMaterialID) 
                    ON Standard.StandardID                                
                       = StandardMaterialList.StandardID)                 
                    ON CASNumber.CASNumberID                              
                       = StandardMaterialList.CASNumberID                 
                    WHERE Standard.StandardID =    1
                    ORDER BY StandardMaterialList.CASNumberID             
                    )                                                     
            )                                                             
        AND
        MDFData.CASNumberID IN 
        (
        SELECT DISTINCT                                       
            StandardMaterialList.CASNumberID                  
        FROM CASNumber                                        
        INNER JOIN (Standard                                  
        INNER JOIN (StandardCompoundMaterial                  
        INNER JOIN StandardMaterialList                       
        ON StandardCompoundMaterial.StandardCompoundMaterialID
           = StandardMaterialList.StandardCompoundMaterialID) 
        ON Standard.StandardID                                
           = StandardMaterialList.StandardID)                 
        ON CASNumber.CASNumberID                              
           = StandardMaterialList.CASNumberID                 
        WHERE Standard.StandardID =    1
        ORDER BY StandardMaterialList.CASNumberID
        )
    ORDER BY                                                              
          Series.SeriesID                                                 
        , MDFName_1.MDFName                                               
        , MDFName.MDFName                                                 
        , MDFData.SubstanceMass DESC                                      
        , CASNumber.MaterialName

  8. #8
    Join Date
    Oct 2009
    Posts
    67
    Ok query masters, how do I figure out if the sum of the materials in a homogeneous material is greater than the sum for a compound material?

    This query will give me a material that's greater than the limit imposed by the compound material, but say there are 2 materials in a homogeneous material that fall with in a compound material limitation and the limit is say 100ppm, but they are both 60ppm? They would "pass" this query, but when summed up, they would not.

    Catch my drift?
    Code:
    SELECT 
          StandardLimit.StandardID
        , StandardMaterialList.CASNumberID
        , StandardLimit.StandardLimitAmount
        , MDF.MDFid
        , MDFData.CASNumberID
        , MDFData.SubstancePPM
    FROM (MDF 
    RIGHT JOIN MDFData 
    ON MDF.MDFid = MDFData.MDFid) 
    RIGHT JOIN (((StandardCompoundMaterial 
    RIGHT JOIN StandardLimit 
    ON StandardCompoundMaterial.StandardCompoundMaterialID = StandardLimit.StandardCompoundMaterialID) 
    LEFT JOIN StandardMaterialList 
    ON StandardCompoundMaterial.StandardCompoundMaterialID = StandardMaterialList.StandardCompoundMaterialID) 
    LEFT JOIN StandardMeasurementType 
    ON StandardLimit.StandardMeasurementTypeID = StandardMeasurementType.StandardMeasurementTypeID) 
    ON MDFData.CASNumberID = StandardMaterialList.CASNumberID
    WHERE 
        StandardLimit.StandardID = 1 
        AND 
        MDFData.CASNumberID Is Not Null
        AND
        MDFData.SubstancePPM >= StandardLimit.StandardLimitAmount

  9. #9
    Join Date
    Oct 2009
    Posts
    67
    I eventually got there!

    Code:
    SELECT 
          MDF.MDFid
        , MDFData.CompFragmentNameID
        , MDFData.HomogeneousNameID
        , StandardLimit.StandardCompoundMaterialID
        , SUM(MDFData.SubstanceMass) 
          AS SubMassSum
        , SUM(MDFData.SubstancePPM) 
          AS SubPPMSum
        , LAST(StandardLimit.StandardLimitAmount) 
          AS Limit
        , LAST(MDFData.Exemption)
          AS Exemption
    FROM (MDF 
    RIGHT JOIN MDFData 
    ON MDF.MDFid 
       = MDFData.MDFid) 
    RIGHT JOIN (((StandardCompoundMaterial 
    RIGHT JOIN StandardLimit 
    ON StandardCompoundMaterial.StandardCompoundMaterialID 
       = StandardLimit.StandardCompoundMaterialID) 
    LEFT JOIN StandardMaterialList 
    ON StandardCompoundMaterial.StandardCompoundMaterialID 
       = StandardMaterialList.StandardCompoundMaterialID) 
    LEFT JOIN StandardMeasurementType 
    ON StandardLimit.StandardMeasurementTypeID 
       = StandardMeasurementType.StandardMeasurementTypeID) 
    ON MDFData.CASNumberID 
       = StandardMaterialList.CASNumberID
    WHERE 
            StandardLimit.StandardID = 1
        AND MDFData.CASNumberID Is Not Null
        AND 
        (
            (
                MDFData.SubstancePPM >= StandardLimit.StandardLimitAmount
                AND
                StandardLimit.StandardMeasurementTypeID = 1
            )
            OR
            (
                MDFData.SubstanceMass >= StandardLimit.StandardLimitAmount
                AND
                StandardLimit.StandardMeasurementTypeID = 2
            )
        )
    GROUP BY 
          MDF.MDFid
        , MDFData.CompFragmentNameID
        , MDFData.HomogeneousNameID
        , StandardLimit.StandardCompoundMaterialID

  10. #10
    Join Date
    Oct 2009
    Posts
    67
    The only nagging issue left for me is if we institute a new MeasurementType for StandardLimit.StandardMeasurementTypeID. Current values are PPM, MG.

  11. #11
    Join Date
    Oct 2009
    Posts
    67
    As I'm reviewing this, I can't seem to use my alias here (SubPPMSum/SubMassSum) which is what I REALLY want to check against the limit. Am I missing something here?

    Code:
        (
            (
                MDFData.SubstancePPM >= StandardLimit.StandardLimitAmount
                AND
                StandardLimit.StandardMeasurementTypeID = 1
            )
            OR
            (
                MDFData.SubstanceMass >= StandardLimit.StandardLimitAmount
                AND
                StandardLimit.StandardMeasurementTypeID = 2
            )
        )
    Ideally should read as:

    Code:
        (
            (
                SubPPMSum >= StandardLimit.StandardLimitAmount        <----
                AND
                StandardLimit.StandardMeasurementTypeID = 1
            )
            OR
            (
                SubMassSum >= StandardLimit.StandardLimitAmount       <----
                AND
                StandardLimit.StandardMeasurementTypeID = 2
            )
        )
    Is this an instance where I have to bracket the name? EDIT: which did not work either.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •