Sql combine queries help

[Old Pedant]

Ahh...yes...You never said you needed other matching fields from that same record. Indeed, what you have done is basically the right way to do it. Kudos for recognizing the need to join MAXSGRP back to F_SGRP to get the right data. I take back what I said about needing to learn more SQL <grin/>. Many (most) Access users wouldn't have gotten that right.

[unscarred]

Haha... I've never seen SQL until I began this. The only coding experience I have is two semester of Java thus far.

GUESS WHAT???

I just revealed a major software limitation (or deception)

We accomplished exactly what I set out to just the infinityqs software isn't capable of doing what it appears to be capable of.

Here's an example of our Successful Query





Problem: The provider will only provide me with one record at a time



After spending 15 minutes on the phone InfinityQS Tech Support they just told me its not possible to be able to access multiple records from a single provider and gave me no explanation as to why I return multiple records (as shown above) in the first place

So I'm back to square one unless I can combine all my query results into one record

Maybe I should post a new thread... ?

[Old Pedant]

Well, since this *IS* in an Access or SQL Server database (I assume, since the syntax of the query matches those and you are using an OLEDB provider), one answer would be to use a different database tool to do the query.

Unless you *must* do this from within InfinityQS, I see no reason that wouldn't work.

Do you know what kind of DB it is?

[unscarred]

I'm stuck working within the constraints of this software...
The chart needs to be available from the InfinitySPC interface which is the tool the plant has committed to.

We are using Microsoft SQL Server 2005 on Microsoft Windows Server 2003

I very well may end up with a separate provider for each test I chose to query for.
Although it seems inefficient, i've also learned alot through this process so all is not lost.

There may be one other Alternative...
We use a number of OPC providers as well.
I Could possibly query with another application and use TopServer to monitor the "addresses"
Infinity DMS OPC provider can point to those addresses/channels.

However, this would be another major learning experience for me.
I get whats going on but I haven't got much deeper than that.


Quick lesson in Infinity (only if you are interested):

*Every* Record must contain at least 3 columns including Part, Process and Test.

(There are more, not generally seen like "F_SGRP" which increments with every saved record)

Part: Generally the part you are producing
Process: The process or machines the part is being produced on
Test: The variable you wish to collect

There can be only one Part and Process but unlimitted Test.

You can also add unlimited "Descriptors" other columns which are user defined separated by groups - selected before the test which help to filter the data later

There are also precanned "Super Descriptors" which include "Lot", "Shift", "Job" which allow for a little more functionality

It's a powerful database tool. The data entry configurations are usually preconfigured by me and executed from a custom toolbar. General Operator use is beyond simple. We have about a hundred different Infinity Projects in use at that moment.

[Old Pedant]

Personally, if you have that many Infinity projects going on, I'd be *real* tempted to PUSH HARD back at the company and ask them for the multi-record reporting you want. It couldn't be that hard for them to allow it if somebody there would just use a brain cell or two.

[SaltineWarrior]

I think there are more efficient ways to get what you want.... I am involved with Infinity deployments so I am fairly well versed in what you are looking at. I dont think I would attempt to use DMS to create these values. Seems like a great deal of overhead to accomplish something relatively simple.

1.) Depending how you are getting your data to start with you can use your OPC server to calculate the % for you before getting to DMS. We use Kepware as the OPC server and I have the option of creating "advanced tags" which could calculate this number. Then DMS will see this as an available tag and can be used as a provider (alternately you could probably have the math done on the PLC)

2.) You could most likely setup a DEC in SPCEE for each line that contains the two tests with the time and the third test for the %DT. Use last database value for the two time tests and a calculated value for the %DT. Set the DEC to be called every 8 hours (or whatever) and I believe it should open the DEC, grab the values it needs, calculate and close without any user intervention (provided you have pre-selected all the descriptors). This would essentially get you the result you are after with the downside being that project would have to be running on a PC all the time.

3.) If #1 isnt an option you could also use SQL Server to do the work for you. Write a stored procedure to grab the last time values for a given shift, calculate the %DT then insert this data into the infinity tables as a new subgroup/Test. Setup an agent job to run this at the end of every shift for each line and voila you have automated %DT charts. The only thing you need to get right is the F_CRTM, F_EDTM, F_SGTM fields so the times display right in SPCEE. The values for pretty much everything in infinity are the UNIX time the value was created. So the F_SGRP value on an insert is this: DATEDIFF(s, '1/1/1970', GETDATE()) (**If you are using the most updated version they switched the database to UTC time so you will need to adjust the results from the GETDATE depending on what time zone you are in) assuming you are only inserting one row at a time. If you were doing this with a group of rows to enter I would increment the above by the row number in the recordset to ensure each one has a unique F_SGRP. Use the same DATEDIFF(s, '1/1/1970', GETDATE()) for the F_CRTM, F_EDTM, F_SGTM fields, then look up the values you want for the various descriptors.

Anyway hope this helps (or that you have already figured out what you were working on )