LabVIEW

cancel
Showing results for 
Search instead for 
Did you mean: 

Is there better query to accomplish the task?(related to multi column list box)

Hi All...
 
I have an application in which i have to display information in a multi column listbox.
 
But the data i have to fetch is creating problems to me.
 
I have two tables setting and info.
Suppose i have fields pump1,pump2,pump3,pump4 Setting of all pumps is there in setting table.
 
in the display there are column headers for pump1...pump4
A station may be having no pump or any number of pumps.If for a stationA ,
if it has 2 pumps then in the setting table,a value 1 will be assigned to the two pumps and a value 0 to the other two pumps.
Then in the display of station A,i have to select all records of station A from info table and assign null value to the pumps which is set as 0 in the setting table.Info table contains the status of pumps of various stations in different days.
There is no way for me to check the info table only as it contains all pumps for all stations.
 
 
 
Settingtable
 
Stnno   Stnname               pump1   pump2 pump3  pump4.................
 
s1             stationA                 1            0           0           0       (this station has only pump1)
 
s2             stationB                 1            1           0           0       (this station has pump1 and pump2)
 
s3             stationC                 1           1          0           1         (this station has pump1,pump2 and pump4)
 
Infotable
 
stnno            datetime                                       pump1     pump2     pump3   pump4
s1                12-12-2007 09:00:00 AM                   off             off              off       off
s1                12-12-2007 10:00:00 AM                  on            off              off       off                 (a value off may imply either the pump is off at that
s2                 12-12-2007 09:00:00 AM                   off           on              off        off                  moment or the station dont have that pump.
s2                  12-12-2007 10:00:00 AM                  on           on              off         off                    thats why i have to check setting table also)
 
In the display i want to have
 
stnno            datetime                                       pump1     pump2     pump3   pump4
s1                12-12-2007 09:00:00 AM                   off             -            -             -
s1                12-12-2007 10:00:00 AM                  on              -            -             -
s2                 12-12-2007 09:00:00 AM                   off           on           -            -
s2                  12-12-2007 10:00:00 AM                  on           on           -            -
 
Is there any way to  construct query to get the records from info and display the result comparing with setting(if setting is 0 then leave the
fields value as blank)
 
 
If no query to suit my purpose how can i accomplish the task?
If i have 400 records ,checking each record with setting table is time consuming process right?
So it will affect the performance also.So how shall i go forward?
 
Thanks in advance
 
 
 
 
 
 
 
 
0 Kudos
Message 1 of 5
(2,564 Views)
Hi User_1:

You can build matrix1 from SettingTable with
* 1 if a pump is present
* 0 if a pump is not present
You can build matrix2 from InfoTable with
* -1 if a pump is ON
* +1 if a pump is OFF
You multiply matrix1 and matrix2 and you get:
* 0 if there is no pump
* -1 if there is a pump and it's ON
* +1 if there is a pump and it's OFF
Then you can build a RING with 3 elements
* Value 0: "-"
* Value -1: "ON"
* Value +1: "OFF"
And create a 2D string array with the text values of the ring
And send that 2D string array to your multicolumnlistbox->"Item Names" property

Hope it helps,
Aitortxo.
0 Kudos
Message 2 of 5
(2,549 Views)

Tnal u 4 ur reply sir.

I am still confused...so let me ask my doubts?

In my point of view, i have to select all records from setting table(for checking purpose)and all records(depending on criteria,ie,which day's record the user wants 2 view) from info table.There may be a number of records in a particular day may be more than 300 records.And there may be records of different stations.(setting table is a master table which stores the information of all stations like how many pumps the station have,how many reservoir the station have...Not only pump but reservo,flow...also i have to check before display.)

What i have to do is display theselected records of info table by after checking for the field in the setting table.If i have 300 records i have to repeat the task for all the records.I have to check each field of the record of info table got value of 0 or 1 in setting table.If 0 just i have to display ' - ' and if its value is 1 i have to display the value of that field of the record as it is.For 300 records,i have to select each record,take each field and compare its value in setting table.This will be really time consuming process

If iam constructing a matrix as u suggested, i dont know how 2 go 4wd.Bse i am not only dealing wth pump but reservo levels,flow levels...has also to be checked and so the matrix multiplication also become complex,and have to do for all the 300 records also.

Can u please suggest a work around for my issue?

Thanx once again

 

 

 

0 Kudos
Message 3 of 5
(2,522 Views)
You are not going to get anywhere when you only give us a fraction of your problem at any one time.  It would be very helpful to either do a screen dump of the two tables in question with some sample data as well as what  your output looks like or post your existing code/create an example.  This approach will get your answer the quickest and most complete.

It would seem that you have experience with databases, as the way you are referring to your data seems to be query related.  If your data is NOT in a database, then you have to 'build' your query routines for getting the data out of the two tables.  If you are more comfortable with SQL, you can dynamically create 'flat' databases and use database queries to get your data back out. 


Paul
0 Kudos
Message 4 of 5
(2,519 Views)
I think I finally understood your problem.
 
Your problem is not about LabView nor multicolumn listboxes, but with database queries.
 
You need to know how to relate Infotable and Settingtable (I think you could join them on "stnno")
You need to know how to insert a conditional into a query ("iif(...)" can be used in MS-Access, or "select ... case ..." in SQL-Server)
    so that you evaluate { Settingtable.Pump(i) * Infotable.Pump(i) } and output "-", "ON" or "OFF" depending on the result (0, -1 and +1)
And you need to know how many pumps and pipes and whatsoever you have in your system to complete the query.
 
If I understood your problem, the query would start like:
 
"SELECT Infotable.stnno, Infotable.stnno, iif ((iif(infotable.stnno='off', 0, 1) * settingtable.stnno) = 0, '-', iif( ...
 FROM Infotable, settingtable
 WHERE Infotable.stnno ...
"
 
Hope it helps,
 
Aitortxo.
Message 5 of 5
(2,496 Views)