topic Re: Making a JMP variable part of a SQL query? in Discussions

Making a JMP variable part of a SQL query?

rleeper — Mon, 16 Jun 2014 20:57:52 GMT

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 — Tue, 17 Jun 2014 13:53:42 GMT

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

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

rleeper — Wed, 18 Jun 2014 12:26:35 GMT

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?

pmroz — Wed, 18 Jun 2014 13:26:45 GMT

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?

rleeper — Wed, 18 Jun 2014 14:32:48 GMT

PMroz,

Works! Thank you so much!

Leep

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

rleeper — Wed, 18 Jun 2014 16:32:55 GMT

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 — Wed, 18 Jun 2014 16:52:33 GMT

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?

rleeper — Wed, 18 Jun 2014 17:17:44 GMT

PMroz,

You sir, are a genius!

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