Database query problem

[john7]

I use default TestStand Table Schema:

• UUT_RESULT
• STEP_RESULT
• STEP_SEQCALL
• PROP_RESULT
• PROP_BINARY
• PROP_ANALOGWAVEFORM
• PROP_DIGITALWAVEFORM
• PROP_NUMERICLIMIT

Say i want to choose all step names regarding to specific device test:

SELECT DISTINCT step_result.STEP_NAME
FROM step_result
LEFT JOIN uut_result ON (step_result.uut_result = uut_result.ID)
WHERE uut_result.BATCH_SERIAL_NUMBER LIKE '%DEV1%'
AND step_result.STEP_TYPE = 'NumericLimitTest' OR step_result.STEP_TYPE = 'PassFailTest'
OR step_result.STEP_TYPE = 'StringValueTest' OR step_result.STEP_TYPE = 'NI_MultipleNumericLimitTest'

I always get the same list of step names no matter wich device i set as a parameter LIKE '%DEV1%' or LIKE '%DEV2%' or other device.

I'm not sure i clear understand relations between tables. I failed to find detailed explanation.

 

[~jiggawax~]

Try this:

SELECT DISTINCT step_result.STEP_NAME
FROM step_result
LEFT JOIN uut_result ON (step_result.uut_result = uut_result.ID)
WHERE uut_result.BATCH_SERIAL_NUMBER LIKE '%DEV1%'
AND (step_result.STEP_TYPE = 'NumericLimitTest' OR step_result.STEP_TYPE = 'PassFailTest'
OR step_result.STEP_TYPE = 'StringValueTest' OR step_result.STEP_TYPE = 'NI_MultipleNumericLimitTest')

[john7]

Thanks a lot. Again no luck even if i replase LIKE with = 'Device1'.

It would be great if NI staff could issue some tutorial in form of SQL queries to show how to work with tables data.

[john7]

Well... it works if i pass exact BATCH_SERIAL_NUMBER.

Now i want to list all BATCH_SERIAL_NUMBER.

SELECT DISTINCT uut_result.BATCH_SERIAL_NUMBER, uut_result.ID 
FROM uut_result WHERE uut_result.UUT_STATUS = 'Passed'
ORDER BY uut_result.BATCH_SERIAL_NUMBER

 But i don't get it DISTINCT.

[JasoNI]

Hi john7,

I have included some resources below that you may find helpful in terms of your database communication. Because National Instruments does not develop the database formats, it may be helpful to use some of the resources available on the sites of that do the development. I hope this helps!

Microsoft SQL information: http://msdn.microsoft.com/en-us/sqlserver/aa336270.aspx

Creating a TestStand Database Schema: https://www.ni.com/en/support/documentation/supplemental/07/creating-a-teststand-database-schema-fro...

Logging TestStand Results to MySQL Database: https://knowledge.ni.com/KnowledgeArticleDetails?id=kA00Z000000kKLzSAM&l=en-US

Regards,

Jason D

Applications Engineer

National Instruments

[V-H]

I think you're now making some wrong assumptions what SELECT DISTINCT means. It means that the query will display only all-different rows of SELECTed fields (in your case, the SELECT was step_result.STEP_NAMEs - if the steps are always same for each device, they will show the same every time).

From MySQL 5.5. Reference Manual (first google hit for me): "DISTINCT specifies removal of duplicate rows from the result set. "

WHERE _only_ filters the rows of the result set based on the WHERE-sections criteria, in other words: "limits rows later show with SELECT", but SELECT picks which fields to return (read: display).

If you want it to display every step for each found BATCH_SERIAL_NUMBER, you have to add that field to the SELECT clause too, maybe like:

SELECT DISTINCT uur_result.BATCH_SERIAL_NUMBER, step_result.STEP_NAME
FROM step_result
JOIN uut_result ON (step_result.uut_result = uut_result.ID)
WHERE
    uut_result.BATCH_SERIAL_NUMBER LIKE '%Dev1%'
    AND
        (
        step_result.STEP_TYPE = 'NumericLimitTest' OR
        step_result.STEP_TYPE = 'PassFailTest' OR
        step_result.STEP_TYPE = 'StringValueTest' OR
        step_result.STEP_TYPE = 'NI_MultipleNumericLimitTest'
        )
;

[john7]

Thanks a lot guys.