Break down a string into individual parts to search a database

[buickgn]

Sorry,

The tutorials I've read explain how to use select/join/where statements when comparing two or more tables. The problem is the results from the VI aren't in table form, so I don't know how to modify the where statement.

[buickgn]

---

@nyc_(is_out_of_here) wrote:

---

@buickgn wrote:

The problem is they all discuss joins where there is already a table created. I'm not creating a table from the submatches.

---

---

Huh?

Those two sentences don't make any sense.

I have a book called Visual Quickstart SQL. You might want to try that. Every possible SQL scenario.

---

Ok, I'll get that. Thanks for your help. I do want to learn and this was my last resort to ask on here, I didn't just say "I don't know how to do this I'll let someone on the labVIEW forums do my work." I was really just out of options. Take care.

[nyc_(is_out_of_here)]

---

@buickgn wrote:

Sorry,

The tutorials I've read explain how to use select/join/where statements when comparing two or more tables. The problem is the results from the VI aren't in table form, so I don't know how to modify the where statement.

---

The results from the VI are to be used to create SQL statement(s).

Knowing how to use the results requires knowledge of SQL and the database schema.

All of this was explained above by others.

[buickgn]

Understood. I just placed the order for the book.

Thanks

[Mark_Yedinak · Accepted solution]

Which is what you would want to do based on your initial posts. You want to use the subparts of your data which I have to assume are the ID in the various tables. My guess is that you want to provide a meaningful report to the user for what the serial number actually means. The SELECT statement you will need to access all of the tables will get rather large since you are combinig so many tables. I would once again STRONGLY recommend that as you come up with this query you implement it as a stored procedure. The correct stored procedure could accept the whole serial number and break it down itself.

If the submatches are not in the DB then there has to be additional tables that equate those submatches with the IDs in the other tables. If this is the case you will need to find those tables and relationships as well. Given you say that your tables only have an ID and a value I would say that you are missing parts of the schema that do associate the submatches with the IDs.

Since I do not have your schema nor do I know exactly what you want I can't give you the specific SQL. But you might want to see this as a learning experience. It soundsl ike you will have to maintain this going forward and there is no better way to learn than trial and error. The example I posted is pretty straightforward SQL. One change would be that the '*' would bereplaced with somethng along the line of "value_t1, value_t2, value_t3, ...,value_tn". This would be necessary since you have columns that have the same name. You will need to effectively rename the value column within your subqueries.

[buickgn]

Mark,

I understand now. I ordered the book nyc suggested and with your tips I'm confident that I can get it working. Time to read up on stored procedures.

Thanks again,

Chris

[Mark_Yedinak]

---

@buickgn wrote:

Mark,

I understand now. I ordered the book nyc suggested and with your tips I'm confident that I can get it working. Time to read up on stored procedures.

Thanks again,

Chris

---

One thing to note is that not all SQL is created equal. Each DB will have a few slight differences in their syntax. O'Reilly has some decent books that includes the syntax variances for Oracle, MS SQL and MySQL as well as one other I don't recall. The general concepts will apply to all but the specific SQL syntax may differ slightly.

[Mark_Yedinak]

This was from a private message to the OP. I thought I would post it here for others to see.

t would look something like the following

SELECT cable_length, drawing, option FROM

   (SELECT value AS cable_length FROM A1 WHERE A!.id = <submatch value from parsing>

    JOIN SELECT value AS drawing FROM A2 WHERE A2.id = <submatch value from parsing>

   JOIN SELECT value AS option FROM A3 WHERE A3.id = <submatch value from parsing>;

   );

You can see the basic pattern here. From there you can add the rest.