Choose Language Hide Translation Bar
mwechtal
Community Trekker

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

 

 

0 Kudos
3 REPLIES 3
cwillden
Super User

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
Highlighted
mwechtal
Community Trekker

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

0 Kudos
gzmorgan0
Super User

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