Hi,
I am having issues while trying to have a custom user input based variable into my SQL code. The problem with the database I am connecting is that the date column is character. So here is the actual code that works well with hard coded dates inside the SQL query.
Open Database("Generic database connection string",
" SELECT
p.productname,
p.model,
p.family,
TO_DATE (SUBSTR (txntime, 1, 15), 'yyyy/mm/dd hh24miss') txntime
FROM database p
WHERE
txntime >= '20161201 000000000'
AND txntime < '20161231 000000000'
",
// End of SQL statement
"New_Table" // New table name
);
The picture below shows the sample view on how the dates are saved in this table. Column property is Character & Nominal.
Here is the code I tried to insert user input variable. This code returns an empty table.
Delete Symbols(); Delete Globals(); nw = new window("Scrap Data", << modal(), panelbox("Enter Date Range", hlistbox( text box("From Date"), fromdate_teb = text edit box("%", << set width (100)), ), hlistbox( text box("To Date"), todate_teb = text edit box("%" , << set width (100)), ), ), panelbox("Actions", hlistbox( ok_button = button box("OK", fromdate = fromdate_teb << get text; todate = todate_teb << get text; ok_pushed = 1;), cancel_button = button box("Cancel", ok_pushed = 0), ) ) ); Open Database("Generic database connection string", " SELECT p.productname, p.model, p.family, TO_DATE (SUBSTR (txntime, 1, 15), 'yyyy/mm/dd hh24miss') txntime FROM database p WHERE txntime >= '^fromdate^' AND txntime < '^todate^' ", // End of SQL statement "New_Table" // New table name --> can be changed as per requirements );
You have to match the hatch for your different dates. The dates coming from JMP have one date mask, while the dates in your database have another one. This should do it:
nw = new window("Scrap Data", << modal(),
panelbox("Enter Date Range",
lineup box(ncol(2),
text box("From Date: "),
fromdate_teb = text edit box(format(today(),"y/m/d h:m:s"), << set width (150)),
text box("To Date: "),
todate_teb = text edit box(format(today(),"y/m/d h:m:s") , << set width (150)),
),
),
panelbox("Actions",
hlistbox(
ok_button = button box("OK",
fromdate = fromdate_teb << get text();
todate = todate_teb << get text();
ok_pushed = 1;),
cancel_button = button box("Cancel", ok_pushed = 0),
)
)
);
print(fromdate, todate);
sql = evalinsert(
"SELECT
p.productname,
p.model,
p.family,
TO_DATE(SUBSTR (txntime, 1, 15), 'yyyymmdd hh24miss') txntime
FROM database p
WHERE TO_DATE(SUBSTR (txntime, 1, 15), 'yyyymmdd hh24miss') >= to_date('^fromdate^', 'yyyy/mm/dd hh:mi:ss am')
AND TO_DATE(SUBSTR (txntime, 1, 15), 'yyyymmdd hh24miss') < to_date('^todate^', 'yyyy/mm/dd hh:mi:ss am')");
Open Database("Generic database connection string", sql, "New_Table");
print(sql);
Try this and see if it works for you
Names Default To Here( 1 );
Delete Symbols();
Delete Globals();
nw = New Window( "Scrap Data",
<<modal(),
Panel Box( "Enter Date Range",
H List Box( Text Box( "From Date" ), fromdate_teb = Text Edit Box( "%", <<set width( 100 ) ), ),
H List Box( Text Box( "To Date" ), todate_teb = Text Edit Box( "%", <<set width( 100 ) ), ),
),
Panel Box( "Actions",
H List Box(
ok_button = Button Box( "OK",
fromdate = fromdate_teb << get text;
todate = todate_teb << get text;
ok_pushed = 1;
),
cancel_button = Button Box( "Cancel", ok_pushed = 0 ),
)
)
);
Eval(
Substitute(
Expr(
Open Database(
"Generic database connection string",
" SELECT
p.productname,
p.model,
p.family,
TO_DATE (SUBSTR (txntime, 1, 15), 'yyyy/mm/dd hh24miss') txntime
FROM database p
WHERE
txntime >= __From_Date__
AND txntime < __To_Date__
",
// End of SQL statement
"New_Table" // New table name --> can be changed as per requirements
)
),
Expr( __From_Date__ ), Parse( "'" || fromdate || "'" ),
Expr( __To_Date__ ), Parse( "'" || todate || "'" )
)
);
Hello Jim,
Thanks for the reply, but I am still having issues. Here are the snap shots of my input and the corresponding errors seen in the log
Input type 1
Input type 2
I have a feeling the problem is coming from this part of the code but I am still a learning JSL. I would appretiate feedback from experts like you. Is there any way that we can directly use the user input variable "fromdate" & "todate" directly in SQL?
I think you need to enforce the format for the from date and to date which the user enters so I've provided an option. However, I think the major issue is in the where statement so I've provided an option which should get you close to what you need.
Delete Symbols(); Delete Globals(); nw = new window("Scrap Data", << modal(), panelbox("Enter Date Range", hlistbox( text box("From Date"), fromdate_teb = text edit box(format(today(),"y/m/d h:m:s"), << set width (100)), ), hlistbox( text box("To Date"), todate_teb = text edit box(format(today(),"y/m/d h:m:s") , << set width (100)), ), ), panelbox("Actions", hlistbox( ok_button = button box("OK", fromdate = fromdate_teb << get text(); todate = todate_teb << get text(); ok_pushed = 1;), cancel_button = button box("Cancel", ok_pushed = 0), ) ) ); Open Database("Generic database connection string", " SELECT p.productname, p.model, p.family, TO_DATE (SUBSTR (txntime, 1, 15), 'yyyy/mm/dd hh24:mi:ss') txntime FROM database p WHERE txntime >= to_date('"|| fromdate || "', 'yyyy/mm/dd hh:mi:ss am') AND txntime < to_date('"|| todate || "', 'yyyy/mm/dd hh:mi:ss am') ", // End of SQL statement "New_Table" // New table name --> can be changed as per requirements );
Hello Mark,
Thanks for your reply as well. I am having issues with this code as well.
Here are the snap shots of my user input in the text box and the error that I am seeing in the log
The biggest reason for this problem is that the database I am dealing with has time stamp is a character format. :(
The date and format of the date in the TO_DATE function need to match.
Try this in the select section of the script I sent:
TO_DATE(
SUBSTR (txntime, 1,4)||"/"||SUBSTR(txntime,5,2)||"/"||SUBSTR(txntime,7,2)||" "||
SUBSTR(txntime,10,2)||":"||SUBSTR(txntime,12,2)||":"||SUBSTR(txntime,14,2),
'yyyy/mm/dd hh24:mi:ss') txntime
Thanks Mark but unfortunately I am seeing the following error when trying to execute
The error seems to be caused due to the concatenation of the space between the date & the time (highlighted in red). Is there any other way to have the space in between the date & the time?
TO_DATE(SUBSTR (txntime, 1,4)||"/"||SUBSTR(txntime,5,2)||"/"||SUBSTR(txntime,7,2)||" "||SUBSTR(txntime,10,2)||":"||SUBSTR(txntime,12,2)||":"||SUBSTR(txntime,14,2),'yyyy/mm/dd hh24:mi:ss') txntime
Also I have a question about the text 'am' for the where clause. Could you let me know why is it needed there?
You have to match the hatch for your different dates. The dates coming from JMP have one date mask, while the dates in your database have another one. This should do it:
nw = new window("Scrap Data", << modal(),
panelbox("Enter Date Range",
lineup box(ncol(2),
text box("From Date: "),
fromdate_teb = text edit box(format(today(),"y/m/d h:m:s"), << set width (150)),
text box("To Date: "),
todate_teb = text edit box(format(today(),"y/m/d h:m:s") , << set width (150)),
),
),
panelbox("Actions",
hlistbox(
ok_button = button box("OK",
fromdate = fromdate_teb << get text();
todate = todate_teb << get text();
ok_pushed = 1;),
cancel_button = button box("Cancel", ok_pushed = 0),
)
)
);
print(fromdate, todate);
sql = evalinsert(
"SELECT
p.productname,
p.model,
p.family,
TO_DATE(SUBSTR (txntime, 1, 15), 'yyyymmdd hh24miss') txntime
FROM database p
WHERE TO_DATE(SUBSTR (txntime, 1, 15), 'yyyymmdd hh24miss') >= to_date('^fromdate^', 'yyyy/mm/dd hh:mi:ss am')
AND TO_DATE(SUBSTR (txntime, 1, 15), 'yyyymmdd hh24miss') < to_date('^todate^', 'yyyy/mm/dd hh:mi:ss am')");
Open Database("Generic database connection string", sql, "New_Table");
print(sql);
Thank you so much. This script is working great and I am getting the data.