cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
rleeper
Level III

Making a JMP variable part of a SQL query?

Hi All.

I would like to add the number of days of records to pull from my SQL database.

I am currently using :

WHERE TestTime > GetDate()-4

I would like to replace the number 4 with a variable that the user can input from a dialog box.

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
pmroz
Super User

Re: Making a JMP variable part of a SQL query?

nw = new window("My Query", << modal(),

    panelbox("Input",

        hlistbox(

            text box("Enter the number of days: "),

            ndays_teb = text edit box()

        ),

    ),

    panelbox("Actions",

        hlistbox(

            ok_button = button box("OK",

                        ndays = ndays_teb << get text;

                        ok_pushed = 1;),

            cancel_button = button box("Cancel", ok_pushed = 0),

        )

    )

);

sql_statement = "SELECT '" || ndays || "' FROM DUAL";

View solution in original post

7 REPLIES 7
pmroz
Super User

Re: Making a JMP variable part of a SQL query?

nw = new window("My Query", << modal(),

    panelbox("Input",

        hlistbox(

            text box("Enter the number of days: "),

            ndays_teb = text edit box()

        ),

    ),

    panelbox("Actions",

        hlistbox(

            ok_button = button box("OK",

                        ndays = ndays_teb << get text;

                        ok_pushed = 1;),

            cancel_button = button box("Cancel", ok_pushed = 0),

        )

    )

);

sql_statement = "SELECT '" || ndays || "' FROM DUAL";

rleeper
Level III

Re: Making a JMP variable part of a SQL query?

PMroz,

I understand the text box input.  I'm not following the SQL statement.

"SELECT * FROM dbo.QVvw_Power_Measurements WHERE TestDate > GetDate()-4",

is my original.

So are you saying something like this?

"SELECT * FROM dbo.QVvw_Power_Measurements WHERE TestDate > GetDate() '" || ndays || "'";

???

pmroz
Super User

Re: Making a JMP variable part of a SQL query?

Almost.  I was showing an example that would work in Oracle SQL.  Your example looks like SQL Server.  Try this:

"SELECT * FROM dbo.QVvw_Power_Measurements WHERE TestDate > GetDate()-" || ndays;

rleeper
Level III

Re: Making a JMP variable part of a SQL query?

PMroz,

Works!  Thank you so much!

Leep

rleeper
Level III

Re: Making a JMP variable part of a SQL query?

PMroz,

So when I try to use the variable as a text input the query stops working.  The variable is a text string "R8D-01" I enter with the input box.  nlot is the variable.

"SELECT * FROM dbo.tbl_NVR WHERE LT_NAME = " || nlot,

Jmp shows the proper value when I hover over the nlot variable. i.e. "R8D-01"

If I type out

"SELECT * FROM dbo.tbl_NVR WHERE LT_NAME = 'R8D-01' "

I get the data.

pmroz
Super User

Re: Making a JMP variable part of a SQL query?

You forgot to put single quotes around it.  Like this:

sql_statement = "SELECT * FROM dbo.tbl_NVR WHERE LT_NAME = '" || nlot || "'";

// or use evalinsert for a cleaner look.  Note the use of ^^ around the nlot variable

sql_statement = evalinsert(

"SELECT * FROM dbo.tbl_NVR WHERE LT_NAME = '^nlot^'");

rleeper
Level III

Re: Making a JMP variable part of a SQL query?

PMroz,

You sir, are a genius!

Sorry I'm taking so much of your expert time.  Thank you for all the help.