Subscribe Bookmark RSS Feed

Selecting a cell and using that cell's value for an SQL query

phillby

Community Trekker

Joined:

Sep 28, 2011

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);

1 ACCEPTED SOLUTION

Accepted Solutions
Solution

Here 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);

);

4 REPLIES
Solution

Here 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);

);

phillby

Community Trekker

Joined:

Sep 28, 2011

The 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.

carole

Community Trekker

Joined:

Jun 23, 2011

Maybe 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);

pmroz

Super User

Joined:

Jun 23, 2011

Here'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