oracle form default where date error

One other thing.

You mention 2 different date formats.

You say that 01-FEB-15 works and that in the form the date format is MM/DD/YYYY.  If you specify 02/01/15 with a date format of MM/DD/YYYY, what you are actually searching for is 02/01/0015.  I thought there was a change to make that a format mask error, but I just tried it in a 11 database and I did not get an error.

Anyway, explicit conversion is always the way to go.  You can never count on the client not changing the default format mask.

i adjust the code like this :

d_f := to_date('01-feb-15','DD-mon-YY'); 

message(d_f);

	SET_BLOCK_PROPERTY('pur_bsh',DEFAULT_WHERE,'inv_date >= d_f ');

Select allOpen in new window

the message return , 01-feb-15
but then error , enable to perform query

Looking up the function shouldn't you use a bind variable?

I'm thinking this needs to be:
SET_BLOCK_PROPERTY('pur_bsh',DEFAULT_WHERE,'inv_date = :d_f');

Notice the ':' in front of d_f.

now i return all records (regardless the date creteria)

>>now i return all records (regardless the date creteria)

I cannot help with the FORMs specific logic.

Does if return the correct subset if you hard-code the date?
SET_BLOCK_PROPERTY('pur_bsh',DEFAULT_WHERE,'inv_date = to_date(''01/01/2011'',''MM/DD/YYYY'')');

Using a date that has know values in the table.

You also never did post back about the time portion of the dates being populated.

What does this return:
select to_char(inv_date,'MM/DD/YYYY HH24:MI:SS') from pur_bsh where rownum<11;

in table data , i didnt find any time portion
the latest query return :
01/01/2015 00:00:00
01/01/2015 00:00:00
01/01/2015 00:00:00
01/01/2015 00:00:00
01/01/2015 00:00:00
01/01/2015 00:00:00
01/01/2015 00:00:00
01/01/2015 00:00:00
01/03/2015 00:00:00
01/03/2015 00:00:00

SET_BLOCK_PROPERTY('pur_bsh',DEFAULT_WHERE,'inv_date = to_date(''01/01/2011'',''MM/DD/YYYY'')');

Select allOpen in new window

this now return the correct records
how should i adjust it to include filed name instead of the date value ?

>>i didnt find any time portion

Cool.  That should make things a little easier.

>>how should i adjust it to include filed name instead of the date value ?

From what I was able to Google, it should work with the bind variable example posted above:
SET_BLOCK_PROPERTY('pur_bsh',DEFAULT_WHERE,'inv_date = :d_f');

What I'm not sure about is if you can just declare a variable and use it as a bind variable.  I'm guessing not since it returned all data, the WHERE was getting ignored.  My guess is it was generating some error before.  Now it is at least running but just not doing what you want.

I cannot find an exact example for this and again, I'm not a Forms person.

ok , as a zone advisor , could you please invite some one who expert in forms ?
thanx

I really cannot invite people.  You need to give the Experts some time to locate this question and respond if they can.  It may be the Forms person you need is on the other side of the World and is asleep.

If you cannot figure it out and no other Expert has posted by this time tomorrow, I can set this as Neglected.  That may alert some additional Experts.

I don't know forms either.  But the examples that I saw didn't include the bind variable in the string.  They looked more like:

SET_BLOCK_PROPERTY('pur_bsh',DEFAULT_WHERE,'inv_date = ' || :d_f);

But that doesn't seem correct to me.  The examples were using strings, so I would think you want:

SET_BLOCK_PROPERTY('pur_bsh',DEFAULT_WHERE,'inv_date = to_date(''' || to_char(:d_f, 'mmddyyyy') || ''',''mmddyyyy'')');

At least based on the examples I saw that seemed to be the syntax.  Seems to defeat the purpose of a bind variable.

>>  The examples were using strings, so I would think you want:

I saw those as well.  I didn't suggest them as a viable option because of the SQL injection possibilities of string concatenation.

Will it work, sure but I wouldn't use it.

But, I'm wondering if that is how forms has to work.  Is that the required syntax for a bind variable in a forms call like that.

i google , i found this in an arabic  forum

set_block_property('pur_bsh',default_where,'inv_date >=to_date('''|| :BASIC_BLOCK.D_from ||''',''dd-mm-rrrr'')');

Select allOpen in new window

and it works
but you note that it changed the format
1- in the : MM/DD/YYYY it didn't work , why ?
2-how i can adjust the above code to make it in between

Did you try what I posted?  That is essentially the same thing.  I just did explicit conversions because the variable being used is a date already.

yes , it works
could you please adjust it to be in between 2 dates

>>wondering if that is how forms has to work.

I would be very disappointed if that were the case.  It may be but I would still be disappointed...

Back to the question.

I was able to locate this in Oracle Support:
https://community.oracle.com/message/11682144#11682144

My guess is the actual value will be coming from a Form field not hard coded in the block itself.  The note above shows how to reference the form field object as a bind-like variable.

If the form field is a varchar you would still need to to the date conversion

Try something like:
SET_BLOCK_PROPERTY('pur_bsh',DEFAULT_WHERE,'inv_date = to_date(:pur_bsh.field_name,''MM/DD/YYYY'')');

adding the necessary form and field name.

SET_BLOCK_PROPERTY('pur_bsh',DEFAULT_WHERE,'inv_date = to_date(:pur_bsh.field_name,''MM/DD/YYYY'')');

Select allOpen in new window

this not works , the code of johnosn works with me

I would rather go with the in line bind variable.  Much better way to go, I just didn't see any examples that showed how to do it.

You need to change :pur_bsh.field_name to the correct form field that you are referencing.  We don't know what your field names are.

>>this not works

I assume you changed "pur_bsh.field_name" to the correct objects?

>>the code of johnosn works with me

Then I'll let him continue assisting.

here is the code of sligtwv after adjustment
SET_BLOCK_PROPERTY('pur_bsh',DEFAULT_WHERE,'inv_date = to_date(:BASIC_BLOCK.D_from,''MM/DD/YYYY'')');
 but your code works well

I just posted what I found by searching around.  It didn't seem like the best way to do it.  I would certainly go with the information that slightwv found.  That would  be the accepted way to use a bind variable.

What is wrong with:

SET_BLOCK_PROPERTY('pur_bsh',DEFAULT_WHERE,'inv_date = to_date(:BASIC_BLOCK.D_from,''MM/DD/YYYY'')');

unable to perform query

>>'inv_date between to_date

For this question there isn't an issue using BETWEEN because the dates do not have a time portion.  If you ever run into dates with times, you need to be careful using BETWEEN because you might not get all the data that is available.

i learn something now

SET_BLOCK_PROPERTY('pur_bsh',DEFAULT_WHERE,'inv_date between to_date(''' || :BASIC_BLOCK.D_from || ''',''mm/dd/yyyy'') and to_date(''' || :BASIC_BLOCK.D_to || ''',''mm/dd/yyyy'')');

Select allOpen in new window

the above code return same error : unable to perform query

SET_BLOCK_PROPERTY('pur_bsh',DEFAULT_WHERE,'inv_date between to_date(:BASIC_BLOCK.D_from,''mm/dd/yyyy'') and to_date(:BASIC_BLOCK.D_to,''mm/dd/yyyy'')'); 

Select allOpen in new window

this works , but i learned something
the above return opposite records
i mean , when i select between   03/01/2015 and  03/03/2015
it return data between : 03-jan and 03-mar
which is wrong
finally i goes back to the previous code of johnson , and adjust it like this

 

SET_BLOCK_PROPERTY('pur_bsh',DEFAULT_WHERE,'inv_date >= to_date(''' || to_char(d_f, 'mmddyyyy') || ''',''mmddyyyy'') and inv_date <= to_date(''' || to_char(d_t, 'mmddyyyy') || ''',''mmddyyyy'')');

Select allOpen in new window

this return the correct data , between 01-mar and 03-mar

>>it return data between : 03-jan and 03-mar

This is what I was talking about when I mentioned explicit data conversions.

Something in your environment took 03/01 to by January 3rd instead of March 1st.

You need to look at the data type of BASIC_BLOCK.D_to and/or d_f.

Remember dates do not have a format until they are displayed so you need to know what you are dealing with and when.

Ok , in general , can I use my own format within function to_date ?

thanx for every experts participated in here
i see how the experts are working
no one hurry for the points , unless he provide the benifitical knowledge
thanx for maxwell also for his last point , and his date format is what i will use from now