topic Re: Having custom date range in SQL based on user input variable in Discussions

Having custom date range in SQL based on user input variable

bharathu — Sat, 07 Jan 2017 00:08:29 GMT

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

Re: Having custom date range in SQL based on user input variable

txnelson — Thu, 05 Jan 2017 22:20:28 GMT

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

Re: Having custom date range in SQL based on user input variable

mark_anawis — Thu, 05 Jan 2017 23:01:31 GMT

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

Re: Having custom date range in SQL based on user input variable

bharathu — Sat, 07 Jan 2017 00:00:28 GMT

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?

Re: Having custom date range in SQL based on user input variable

bharathu — Sat, 07 Jan 2017 00:11:18 GMT

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

Re: Having custom date range in SQL based on user input variable

bharathu — Sat, 07 Jan 2017 00:15:30 GMT

The biggest reason for this problem is that the database I am dealing with has time stamp is a character format. :(

Re: Having custom date range in SQL based on user input variable

mark_anawis — Mon, 09 Jan 2017 20:10:02 GMT

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

Re: Having custom date range in SQL based on user input variable

bharathu — Wed, 11 Jan 2017 00:36:20 GMT

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?

Re: Having custom date range in SQL based on user input variable

pmroz — Wed, 11 Jan 2017 13:41:50 GMT

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

Re: Having custom date range in SQL based on user input variable

bharathu — Thu, 12 Jan 2017 19:47:31 GMT

Thank you so much. This script is working great and I am getting the data.

Re: Having custom date range in SQL based on user input variable

bharathu — Fri, 13 Jan 2017 22:00:09 GMT

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

Re: Having custom date range in SQL based on user input variable

bharathu — Sat, 14 Jan 2017 00:01:23 GMT

Fixed it by correcting the WHERE clause for test_date

AND test_data.test_date BETWEEN TO_DATE('^fromdate^', 'YYYY/MM/DD hh:mi:ss am') AND TO_DATE('^todate^', 'YYYY/MM/DD hh:mi:ss am')

Now I am able to use the same input date variables across 2 databases in the same query. One query pulls data with numeric dates and another sql pulls data where dates are character based.

Re: Having custom date range in SQL based on user input variable

Bandfield — Tue, 05 Sep 2017 18:23:49 GMT

Thank you for the assistance. This worked perfectly!