cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
mwechtal
Level III

Passing Date value to Oracle SQL

I have a frustrating problem. I want to write a script to use a JMP date code, to query an Oracle database.

For instance:

StDt = today() - in weeks(4);

.

If I manually do it using Query Builder, I get something like the code below. What syntax do I need to replace the integer date code with to do this? The documentation is pretty sparse on this topic.

 

Query1 = New SQL Query(
 Version( 130 ),
 Connection(...),
  QueryName( "Generic Query" ),
  Select(
   Column( "AA", "t1" ),
   Column( "AB", "t1" ),
   Column( "BB", "t1" ),
   Column( "BC", "t1" ),
   Column(
    "TIMESTAMP",
    "t1",
    Numeric Format( "m/d/y h:m:s", "0", "NO", "" )
   ),
  From(
   Table(
   "XXTABLE",
   Schema( "XX" ),
   Alias( "t1" )
   )
  ),
 Where(
  Contains(
   Column( "AA", "t1" ),
   "Stuff",
   UI( Contains( Base( "Categorical" ) ) )
  ) & Contains(
   Column( "AB", "t1" ),
   "Other Stuff",
   UI( Contains( Base( "Categorical" ) ) )
  ) & GE(
   Column(
    "TIMESTAMP",
    "t1",
    Numeric Format( "m/d/y h:m:s", "0", "NO", "" )
   ),
   3605823947,
   UI( Comparison( Base( "Continuous" ) ) )
 );

 

 

3 REPLIES 3
cwillden
Super User (Alumni)

Re: Passing Date value to Oracle SQL

What happens when you replace the integer date/time value (3605823947) with StDt?  Seems like that should work.

-- Cameron Willden
mwechtal
Level III

Re: Passing Date value to Oracle SQL

Just inserting StDt gets me:

 

deleted object reference[ ]

 

I can find a few references in the community that show something like the following, but I don't see anything specific to Oracle.

 

'" || StDt || "'

" || StDt || "

to_date('^StDt^','m/d/y h:m:s')

to_date('06/13/18 01:01:01','m/d/y h:m:s')

gzmorgan0
Super User (Alumni)

Re: Passing Date value to Oracle SQL

Cameron was correct. See the example below that verifies a variable can be used.

 

However, if  you are getting a deleted reference, my quess is that you are prompting the user for information, and you are not retrieving the information (stdt) correctly.

 

If you share your dialog, someone will help you with the extraction.

 

stdt = 3402950400;
show( As Date(stdt) ); // 01Nov2011;
New SQL Query(
	Version( 130 ),
	Connection( "JMP" ),
	JMP Tables(
		["Stock Averages" =>
		"$Sample_Data\Stock Averages.jmp"]
	),
	QueryName( "SQLQuery1" ),
	Select(
		Column( "Date", "t1", Numeric Format( "m/d/y", "-1", "NO", "" ) ),
		Column( "Open", "t1", Numeric Format( "Currency", "2", "YES", "", "USD" ) ),
		Column( "High", "t1", Numeric Format( "Currency", "2", "YES", "", "USD" ) ),
		Column( "Low", "t1", Numeric Format( "Currency", "2", "YES", "", "USD" ) ),
		Column( "Close", "t1", Numeric Format( "Currency", "2", "YES", "", "USD" ) ),
		Column(
			"Moving Average",
			"t1",
			Numeric Format( "Currency", "2", "YES", "", "USD" )
		)
	),
	From( Table( "Stock Averages", Alias( "t1" ) ) ),
	Where(
		GE(
			Column( "Date", "t1", Numeric Format( "m/d/y", "-1", "NO", "" ) ),
			stdt,
			UI( Comparison( Base( "Continuous" ) ) )
		)
	)
) << Run