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 );
Is there a way to have same user input fromt he text box work on another database SQL pull within the same script? The only catch is this new database follows traditional/standard numeric type date column. Is there a way to convert the user inputs (fromdate & todate) to secondarly variables with a numeric format month-day-year? Primary character based dates to be used for the issue that is already solved and secondary set to extract data based on numeric dates.
Here is my SQL that I am trying to use for extracting yield data from another database.
nw = new window("New Window for user input", << 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); Yieldsql = evalinsert( " SELECT model, lot, wafer, COUNT(test_status_code) as BIN_COUNT FROM test_data WHERE purpose='PROD' AND test_data.test_date BETWEEN TO_DATE('^fromdate^', 'MM/DD/YYYY hh24:mi:ss') AND TO_DATE('^todate^', 'MM/DD/YYYY hh24:mi:ss') ORDER by lot, wafer"); dt = Open Database( "Generic database connection string", Yieldsql, "Yield_Data");
Thank you for the assistance. This worked perfectly!