Making a JMP variable part of a SQL query?

[#1]

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!

[#2 · Accepted solution]

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

[#3]

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 || "'";

???

[#4]

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;

[#5]

PMroz,

Works!  Thank you so much!

Leep

[#6]

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.

[#7]

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^'");

[#8]

PMroz,

You sir, are a genius!

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