Making a JMP variable part of a SQL query?<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />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!
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.
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^'");
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.
Re: Making a JMP variable part of a SQL query?PMroz,
Works! Thank you so much!
Leep
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;
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 || "'";
???
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";
