Selecting a cell and using that cell's value for an SQL query<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />Is suspect this is extremely easy but I'm struggling a little here.
The script below works fine. At the moment it is selecting data by an SQL query where the packhouse is "ALBS"
I want the packhouse value to be selected from a cell within a list in a JMP table. I want to be able to select a cell and open the query relating to that value, rather than "ALBS".
packhouse = "ALBS";
dsn_string = "DSN=TRG-SQL13;UID=;Trusted_Connection=Yes;APP=JMP;WSID=MTMLAP160;DATABASE=ZS_Export";
sql_string = "SELECT top 5 PalletID, dtTempSample, TempSampleValue " ||
" FROM dbo.tb_Raw_Data " ||
" WHERE tb_Raw_Data.SiteName = '" || packhouse || "' " ||
" AND tb_Raw_Data.ShipmentID = 'warehouse' " ||
"order by palletid, dttempsample";
dt = open database(dsn_string, sql_string, packhouse);
Selecting a cell and using that cell's value for an SQL queryHere's a dialog box that will allow you to select one facility, and then call the SQL with that.
// Facility Selector Dialog.jsl
// Replace this with your own table...
dt = New Table( "Facilities",
Add Rows( 6 ),
New Column( "Facility",
Character,
Nominal,
Set Values( {"ALBS", "POKI", "MOLJ", "ZXQQ", "GXRS", "IPQA"} )
)
);
facility_list = column(dt, "Facility") << get values;
dsn_string = "DSN=TRG-SQL13;UID=;Trusted_Connection=Yes;APP=JMP;WSID=MTMLAP160;DATABASE=ZS_Export";
fac_win = new window("Select Facility",
panelbox("Select which facility to retrieve data for",
select_facility_lb = listbox(facility_list, width(100), nlines(10),
maxselected(1)
),
),
panelbox("Actions",
buttonbox("OK",
selected_list = select_facility_lb << get selected;
selected_facility = selected_list[1];
sql_string = "SELECT top 5 PalletID, dtTempSample, TempSampleValue " ||
" FROM dbo.tb_Raw_Data " ||
" WHERE tb_Raw_Data.SiteName = '" || selected_facility || "' " ||
" AND tb_Raw_Data.ShipmentID = 'warehouse' " ||
"order by palletid, dttempsample";
fac_dt = open database(dsn_string, sql_string, selected_facility);
fac_win << close window;
),
buttonbox("Cancel",
fac_win << close window;
)
)
);
// end Facility Selector Dialog.jsl
Selecting a cell and using that cell's value for an SQL queryMaybe it's better for you :
dtP = Data Table( "Your Packouse Table");
dsn_string = "DSN=TRG-SQL13;UID=;Trusted_Connection=Yes;APP=JMP;WSID=MTMLAP160;DATABASE=ZS_Export";
CurrentDataTable(dtP);
ir_selected = dtP << Get Selected Rows();
packhouse = :Packouse[ir_selected[1]];
sql_string = "SELECT top 5 PalletID, dtTempSample, TempSampleValue " ||
" FROM dbo.tb_Raw_Data " ||
" WHERE tb_Raw_Data.SiteName = '" || packhouse || "' " ||
" AND tb_Raw_Data.ShipmentID = 'warehouse' " ||
"order by palletid, dttempsample";
dt = open database(dsn_string, sql_string, packhouse);
Selecting a cell and using that cell's value for an SQL queryThe script above isn't doing what I want it to.
I already have a list of packhouse names in a table, about 20 of them. I want to be able to click on one of these names to select the cell, then run the query above using the value in the cell instead of ALBS. I think I want to create a name e.g. facilty = current cell, the use the word "facility" within my query.
Selecting a cell and using that cell's value for an SQL queryHere may be a solution for you :
dtP = New Table( "Packouse Table",
Add Rows( 3 ),
New Column( "Packouse",
Character,
Nominal,
Set Values( {"ALBS", "POKI", "MOLJ"} )
)
);
dsn_string = "DSN=TRG-SQL13;UID=;Trusted_Connection=Yes;APP=JMP;WSID=MTMLAP160;DATABASE=ZS_Export";
CurrentDataTable(dtP);
lstPackouse = column("Packouse") << GetAsMatrix;
For(i=1, i<=NItems(lstPackouse), i++,
packhouse = lstPackouse;
sql_string = "SELECT top 5 PalletID, dtTempSample, TempSampleValue " ||
" FROM dbo.tb_Raw_Data " ||
" WHERE tb_Raw_Data.SiteName = '" || packhouse || "' " ||
" AND tb_Raw_Data.ShipmentID = 'warehouse' " ||
"order by palletid, dttempsample";
dt = open database(dsn_string, sql_string, packhouse);
);
