Skip to main content
Question

FME Parameter in SQLCreator

  • December 14, 2018
  • 4 replies
  • 27 views
sunsilk11
Contributor
Forum|alt.badge.img+6

Hi I am trying to run an SQL to select records in past 10 mins from Oracle

I have tried various methods in my sql, but none seem to work

select * from table name where updatedtime >= sysdate - interval '10' minute

select * from table name where updatedtime >= to_char(sysdate - (10/24/60), 'DD-MON-YYYY HH24:MI:SS')

I am thinking of setting a parameter to the required datetime and use it in SQL Server

Couple of queries:

1) Is startup script best place to create this parameter

2) Will SQLCreator query work with a parameter

e.g. select * from table name where updatedtime >= to_timestamp(fmeparameter, 'DD-MON-YYYY HH24:MI:SS')

where fmeparameter ='14-DEC-2018 14:00:00'

4 replies

ebygomm
Influencer
Forum|alt.badge.img+39
  • ebygommInfluencer
  • Influencer
  • December 14, 2018

For last 10 mins in an oracle query you would normally use the folllowing.

sysdate - (10/1440)

is there a reason you don't want to use this?

sunsilk11
Contributor
Forum|alt.badge.img+6
  • sunsilk11Contributor
  • Author
  • Contributor
  • December 14, 2018
ebygomm wrote:

For last 10 mins in an oracle query you would normally use the folllowing.

sysdate - (10/1440)

is there a reason you don't want to use this?

No, I would definitely use this. But it doesn't work when used in my sql, hence looking for a workaround with a date string which works in the same sql

 

david_r
Celebrity
  • david_rCelebrity
  • December 17, 2018
sunsilk11 wrote:

No, I would definitely use this. But it doesn't work when used in my sql, hence looking for a workaround with a date string which works in the same sql

 

I think the bigger question is why this doesn't work in your SQL, in my opinion there's no obvious reason for it not to work. Do you get any error messages?

david_r
Celebrity
  • david_rCelebrity
  • December 17, 2018

Try the following, notice that you shouldn't convert the result back to a string before comparison:

select * from table_name 
where updatedtime >= sysdate - (10/1440)

Reply


Helpful Members This Week

Community Stats

31,372
Posts
118,986
Replies
39,003
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.