NI TestStand

cancel
Showing results for 
Search instead for 
Did you mean: 

Database query problem

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.

 

0 Kudos
Message 1 of 7
(3,625 Views)

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')
jigg
CTA, CLA
testeract.com
~Will work for kudos and/or BBQ~
0 Kudos
Message 2 of 7
(3,615 Views)

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.

0 Kudos
Message 3 of 7
(3,611 Views)

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.

0 Kudos
Message 4 of 7
(3,599 Views)

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

0 Kudos
Message 5 of 7
(3,569 Views)

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'
        )
;

 

 

 

 

0 Kudos
Message 6 of 7
(3,549 Views)

Thanks a lot guys.

0 Kudos
Message 7 of 7
(3,544 Views)