Avatar of NiceMan331
NiceMan331
 asked on

oracle form default where date error

hi
i have view pur_bsh
having inv_date date
when i sql :
select * from pur_bsh where
inv_date = '01-feb-15'

Open in new window

it return correct rows
but in form , i have non_database block "basic_block" & non_database item "d_from"
type date , format mask  "MM/DD/YYYY"
now , in when button pressed trigger i wrote
declare
d_f date;
d_t date;   
begin
		d_f := :BASIC_BLOCK.D_from;
	d_t := :BASIC_BLOCK.D_to;
		SET_BLOCK_PROPERTY('pur_bsh',DEFAULT_WHERE,'inv_date = d_f');

	GO_BLOCK('pur_bsh');

	EXECUTE_QUERY;
	end ;

Open in new window


it return nothing
what may be the cause ?
Oracle Database

Avatar of undefined
Last Comment
NiceMan331

8/22/2022 - Mon
SOLUTION
slightwv (䄆 Netminder)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
johnsone

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.
NiceMan331

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

Open in new window


the message return , 01-feb-15
but then error , enable to perform query
slightwv (䄆 Netminder)

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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
NiceMan331

ASKER
now i return all records (regardless the date creteria)
slightwv (䄆 Netminder)

>>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;
NiceMan331

ASKER
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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
NiceMan331

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

Open in new window


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

>>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.
NiceMan331

ASKER
ok , as a zone advisor , could you please invite some one who expert in forms ?
thanx
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
slightwv (䄆 Netminder)

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.
johnsone

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.
slightwv (䄆 Netminder)

>>  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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
johnsone

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.
NiceMan331

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

Open 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
johnsone

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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
NiceMan331

ASKER
yes , it works
could you please adjust it to be in between 2 dates
slightwv (䄆 Netminder)

>>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.
NiceMan331

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

Open in new window


this not works , the code of johnosn works with me
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
johnsone

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.
slightwv (䄆 Netminder)

>>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.
NiceMan331

ASKER
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 started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
johnsone

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

ASKER
unable to perform query
ASKER CERTIFIED SOLUTION
johnsone

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
slightwv (䄆 Netminder)

>>'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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
NiceMan331

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

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

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

Open in new window


this return the correct data , between 01-mar and 03-mar
slightwv (䄆 Netminder)

>>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.
NiceMan331

ASKER
Ok , in general , can I use my own format within function to_date ?
Your help has saved me hundreds of hours of internet surfing.
fblack61
SOLUTION
Mark Geerlings

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
slightwv (䄆 Netminder)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
PortletPaul

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
NiceMan331

ASKER
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