03-11-2013 04:45 AM
I use default TestStand Table Schema:
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.
03-11-2013 08:03 AM
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')
03-11-2013 08:26 AM
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.
03-11-2013 09:52 AM
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.
03-12-2013 04:59 PM
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: http://www.ni.com/white-paper/6484/en
Logging TestStand Results to MySQL Database: http://www.ni.com/white-paper/3604/en
Regards,
Jason D
Applications Engineer
National Instruments
03-14-2013 07:08 AM - edited 03-14-2013 07:11 AM
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'
)
;
03-14-2013 08:03 AM
Thanks a lot guys.