Oracle Keyword

rbuser

Hello all

D5, RB 7.03 Oracle 8i (or 9i), DOA, end user reporting.

In dataview we are able to add a search criteria like

aDateField = sysdate

BUT

we are unable to use something like

aDateField = to_date(sys_context(...), 'dd/mm/yyyy')

Actually it is impossible to use "sys_context" or other Oracle function

Is there any workaround or patch for fixing this?

Do I need to manually add sys_context between SQLReservedWord in
daSQLReservedWords.pas?

Thanks in advance!

Bye
Nicola

nardmoseley

Try adding it to daBuildOracleReservedWordList in daSQLReservedWords


--
Nard Moseley
Digital Metaphors
http://www.digital-metaphors.com

rbuser

I tried but it doesn't work.
I need an expression of this kind (in the sql where clause):

aDateField <= to_Date(SYS_CONTEXT('userenv'','client_info'),'dd/mm/yyyy')

rbuser

Oops.. sorry I was wrong
The unit daSQLReservedWords
simply doesn't get compiled ..
I tried moving in the rbuilder/lib folder but with no success.
Which .dpk do I need to recompile to get this unit?

nardmoseley

To fix the behavior at Delpi runtime, you can copy daSQLReservedWords.pas to
RBuilder\Lib and then rebuild your Delphi project.

To fix Delphi design-time, would require that the the package rbDAD77.dpk be
rebuilt. The .bpl would need to be copied to Windows\System32 and the .dcp
to RBuilder\Lib.



--
Nard Moseley
Digital Metaphors
http://www.digital-metaphors.com

rbuser

rbuser

The Exception raised in TdaCriteria.AddNumericValue in daSQL.pas

if (FField.DataType in [dtDate, dtDateTime]) then
try
ldValue := ppStrToDateTime(aValue)
except on EConvertError do
ldValue := Now;
end


If the fieldtype is Date she tries to convert from string to datetime

It would be correct, unless the string contains a Oracle function that
returns a date at runtime asking the DB to do so...

I think it is not a good idea trying to validate SQL in the client
The SQL extension of the various vendors may be huge (and not much
standard as you can see...)

What can I do to fix this?
Consider that it is *vital* requirement for us to use a date search
criteria specified in this way...

nardmoseley

Sorry, but I think I may have misunderstood the original question. Just went
back and re-read it.

I think the approach you need to take is to flip the condition around from

aDateField <= to_Date(SYS_CONTEXT('userenv'','client_info'),'dd/mm/yyyy')

To

to_Date(SYS_CONTEXT('userenv'','client_info'),'dd/mm/yyyy') >=
TableName.DataField

The Query Designer will permit you to define a calculated expression field
and then search on a datafield value, but not the other way around.

Try using the Calc tab of the Query Designer to define the expression
calculation:

To_Date(SYS_CONTEXT(''userenv'',''client_info''),''dd/mm/yyyy'')

Then use the Search tab of the Query Designer to define the search condition
on the calculated field by specifying the tableName.FieldName as the search
value.



--
Nard Moseley
Digital Metaphors
http://www.digital-metaphors.com

rbuser

This way it works!
Thank you very *VERY* much!