Passing a data parameter into a Command SQL statement - how?

[mp9]

Hello,

Apologies if this is a simple question. Am running CR10, querying an Oracle database, connected via OLEDB.

Am using a Command in Database Expert, SQL as follows:

SELECT ID_NUMBER, EVENT_TIME FROM ALL_EVENTS

Also have a Parameter Field called StartDate, which a value of type Date.

I want to pass the Parameter through to the Command and since EVENT_TIME is a DateTime field I thought I could try:

SELECT ID_NUMBER, EVENT_TIME FROM ALL_EVENTS
WHERE EVENT_TIME > '{?StartDate}'

which syntactically parses but dosen't return any results... so I'm guessing the '{?StartDate}' is incorrect.

What should the syntax be?

http://www22.brinkster.com/accessory/

 

[Bloke152]

you might need to put brackets around your parameter name outside of the quotes

eg ('{?StartDate}')

 

[mp9]

Still no joy. Again, valid in that in parses but returns no data when I know from running without the WHERE clause that there should be some.

http://www22.brinkster.com/accessory/

 

[Bloke152]

is your event_time field formatted as a date field?

if it is its a long shot but try putting to_date around your parameter

eg to_date(('{?StartDate}'),'DD/MM/YYYY')

 

[mp9]

Again, valid parsed syntax but still no results.

Am at my wits' end with this, so thanks for your suggestions.

http://www22.brinkster.com/accessory/

 

[Bloke152]

you could try the to_date without the inner brackets...

to_date('{?StartDate}','DD/MM/YYYY')

but if that dont work i would give up and put the parameter in the record selection

 

[mp9]

Still not joy, but thanks again for the idea.

http://www22.brinkster.com/accessory/

 

[lbass]

Remove the quotes:

SELECT ID_NUMBER, EVENT_TIME
FROM ALL_EVENTS
WHERE EVENT_TIME > {?StartDate}

I'm assuming you are creating the date parameter within the command.

-LB