topic Passing Date value to Oracle SQL in Discussions

Passing Date value to Oracle SQL

mwechtal — Fri, 22 Jun 2018 19:18:17 GMT

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

Re: Passing Date value to Oracle SQL

cwillden — Fri, 22 Jun 2018 22:19:19 GMT

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

Re: Passing Date value to Oracle SQL

mwechtal — Mon, 25 Jun 2018 18:06:10 GMT

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

Re: Passing Date value to Oracle SQL

gzmorgan0 — Mon, 25 Jun 2018 21:27:28 GMT

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