search database for a matching value

[jack1]

An existing database saves the results from a test as col 1 "serial number" col 2 date etc.

There is now a requirement to search on the serial number and display any results related to it (rows).

I have the database toolset and have started by using the SQL vi to extract the serial number passing a recordset ref number. I am at the point where I now need to select the row for that ref number but was wondering if it would be better to just read in the database to an array and then do a search on the serial number col looking for matching text, obtain the row of the array where matches occur and then display those rows, or is this route really long winded and should stick with the SQL vi?

Thanks

Jack

[falkpl]

Using the sql command WHERE 'Field' = value will allow you to select out items from your table which meet the criteria you are looking for (such as a serial number or even a range of values).  This is better that reading the whole table and then searching in labview through the resulte (in my opinion) for a few reasons. 

1.  way less data to transfer from database

2.  Little or no code to write - just write the sql command string (static or generate dynamically), no code to maintain optomize, dont reinvent the wheel if youe DB engine has done it.

3.  Sql has complex logic for quering.  you can select on ranges, boolean sets of values ....

[jack1]

Paul

Thanks for reply and I will do as you suggest and go with the SQL vi. I have a few problems using this vi so far. The first is that to Search say the Status field for Pass or Fail I used the query:-

select Status from TestDetails1 where Status="Pass"

This instruction resulted in an error message. So I tried a different field Ambient where the temperature is logged:-

select Ambient from TestDetails1 where Ambient=23      (ambient temperature at 23 deg C) 

This command line worked fine and brought up all instances where 23 occurred.

 Also when I use the vi 'Fetch recordset data' I get just the values searched on not th erows to which they belong.

Thanks

John

[smercurio_fc]

These two queries:

---

select Status from TestDetails1 where Status="Pass"

select Ambient from TestDetails1 where Ambient=23      (ambient temperature at 23 deg C) 

---

don't make a whole lot of sense to me. They would simply return the column that you're searching. For example, the second query would return one column, with all values being 23. How is that useful? Are you trying to get a count of how many records were run at Ambient=23? If so, you probably want to use SELECT COUNT.

As for the first query, you need to use single quotes for strings, not double quotes when dealing with T-SQL.

[jack1]

I was using the Ni database PDF document for the structure.

Changed to single quotes as you suggest and that works fine, thanks. You are right I only get a column of 23s etc, not much use. What I want is the row of data that the searched serial number relates to. What would be the SQL command for extracting the row below if searched on serial number 0002:-

Serial Number          Date          Time    Status

0001                  11/12/08      13:23    Pass

0002                  12/12/08      09:34    Pass

For learning the SQL commands and structure would I just refer to a text book on database's? sorry if these questions are basic,

Thanks Jack 

[Dennis_Knutson · Accepted solution]

The query SELECT * FROM tablename WHERE SERIAL_NUMBER = '0002' would return the entire row for each occurence of serial number 0002. If you only wanted pass results, the query would be SELECT * FROM tablename WHERE SERIAL_NUMBER = '0002' AND Status = 'Pass'.

Don't use column names with spaces in them!

I've seen numerous online SQL references and there are books available. I don't have any that I could recomend, though. I learned from the SQL Server help and a SQL expert in our IT department

[smercurio_fc]

---

Dennis Knutson wrote:

Don't use column names with spaces in them!

---

Absolutely agree.

@Jack: Since you have spaces in your column names if you have to specify column names in your select clause then you need to enclose the column name within []. For example, if you only wanted to returnthe "Serial Number" column then your query would be something like this:

SELECT [Serial Number] WHERE Status='Pass'

I've also never really found a good book on SQL. Most of what I learned was also from the SQL Online Help. You could start at the W3 Schools SQL Tutorial Site. It's OK, I guess.

[jack1]

Thanks guys for your help.

Put in the revised statement and now have just the rows. I had got gaps in column names resulting in errors when trying to read as Dennis pointed out. I could have used underscores in the name but client prefers descriptive header and no underscrore if possible. Put in the square brackets and another success!

It would perhaps be a good idea if NI provided some more info in their documentation for the database toolset re SQL structure etc. In the meantime I will do as you both suggest and pick tips up through the various links on the web, no IT division here!!.

Thanks again, appreciated,

Jack