Jump to content

Archived

This topic is now archived and is closed to further replies.

AV1611

Delayed Writing Of Records

Recommended Posts

ok,

got a table with several field I'll show three for this illustration:

SerNo step passed
11111 1 1
11111 2 1
11111 3 0
11111 4 0
11112 1 1
11112 2 1
11113 1 1
11113 2 1
11113 3 1
etc...

I am generating a csv file, but my problem is that I need to exclude any serial number that didn't pass on ANY step.

so, above, all 4 records for serialno 1111 would be excluded, but the other two would not.

here is my full script thus far, but it includes all serial numbers. the field above are serialno step passed

[code]
<?php
$i=0;
$t='\t';
echo "line".$t."it_date".$t."insptype".$t."process".$t."product".$t."serialno".$t."step".$t."step_desc".$t."hi".$t."lo".$t."units".$t."actual".$t."gonogo".$t."passed\n";
$connect = odbc_connect("QES9000", "", "");
$query = "SELECT
    itdatshta.`datshtid`, itdatshta.`sample`, itdatshta.`it_date`, itdatshta.`insptype`, itdatshta.`process`, itdatshta.`product`, itdatshta.`descr`, itdatshta.`serialno`, itdatshta.`failed`,
    itdatshtda.`datshtid`, itdatshtda.`sample`, itdatshtda.`descr`, itdatshtda.`hi`, itdatshtda.`lo`, itdatshtda.`units`, itdatshtda.`actual`, itdatshtda.`gonogo`, itdatshtda.`passfail`, itdatshtda.`step`, itdatshtda.`failed`, itdatshtda.`passed`
FROM
    `itdatshta` itdatshta INNER JOIN `itdatshtda` itdatshtda ON
        itdatshta.`datshtid` = itdatshtda.`datshtid` AND
    itdatshta.`sample` = itdatshtda.`sample`
WHERE
    (itdatshta.`process` = 'VQ Functional Outputs' OR
    itdatshta.`process` = 'VQ Functional Inputs' OR
    itdatshta.`process` = 'QA Evaluation' OR
    itdatshta.`process` = 'Functional Test') AND
    (itdatshta.`product` = 'TEKOS3UNIT1' OR
    itdatshta.`product` = 'TEKOS3UNIT' OR
    itdatshta.`product` = 'TEK.OS3.UNIT1' OR
    itdatshta.`product` = 'TEK.OS3.UNIT')
ORDER BY
    itdatshta.`it_date` ASC,
    itdatshta.`serialno` ASC,
    itdatshta.`process` ASC";

$result = odbc_do($connect, $query);
WHILE($row=odbc_fetch_array($result))
    {
    if($row['step']==1){
        $sn=$row['serialno'];
        }
    if($row['serialno']=='$sn' && $row['passed']==1)
    $i++;
    echo $i."\t".$row['it_date']."\t".$row['insptype']."\t".$row['process']."\t".$row['product']."\t".$row['serialno']."\t".$row['step']."\t".$row['descr']."\t".$row['hi']."\t".$row['lo']."\t".$row['units']."\t".$row['actual']."\t".$row['gonogo']."\t".$row['passed'];
    echo "\n";
    }
odbc_close($connect);

?>
[/code]

Share this post


Link to post
Share on other sites
SELECT a.* FROM mytable a WHERE NOT EXISTS
(SELECT * FROM mytable b WHERE b.serialno = a.serialno
AND b.passed = 0)

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.