- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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!
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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";
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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";
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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 || "'";
???
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Making a JMP variable part of a SQL query?
PMroz,
Works! Thank you so much!
Leep
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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^'");
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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.