Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
oracle form default where date error
hi
i have view pur_bsh
having inv_date date
when i sql :
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
it return nothing
what may be the cause ?
i have view pur_bsh
having inv_date date
when i sql :
select * from pur_bsh where
inv_date = '01-feb-15'
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 ;
it return nothing
what may be the cause ?
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
>>inv_date = '01-feb-15'
If inv_date is a DATE data type, don't do this. It causes an implicit data type conversion and is based on environment settings of default format mask.
ALWAYS do explicit data type conversions:
inv_date = to_date('01-feb-15','DD-mo
If inv_date has the time portion of the value populated you might need:
(inv_date >= to_date('01-feb-15','DD-mo
I cannot help with the FORMS code specifically but I'm guessing what I posted above might help. It is either the fields have the time portion of it is an implicit data type conversion issue.
If inv_date is a DATE data type, don't do this. It causes an implicit data type conversion and is based on environment settings of default format mask.
ALWAYS do explicit data type conversions:
inv_date = to_date('01-feb-15','DD-mo
If inv_date has the time portion of the value populated you might need:
(inv_date >= to_date('01-feb-15','DD-mo
I cannot help with the FORMS code specifically but I'm guessing what I posted above might help. It is either the fields have the time portion of it is an implicit data type conversion issue.
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.
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 :
the message return , 01-feb-15
but then error , enable to perform query
d_f := to_date('01-feb-15','DD-mon-YY');
message(d_f);
SET_BLOCK_PROPERTY('pur_bsh',DEFAULT_WHERE,'inv_date >= d_f ');
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_bs
Notice the ':' in front of d_f.
I'm thinking this needs to be:
SET_BLOCK_PROPERTY('pur_bs
Notice the ':' in front of d_f.
>>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_bs
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/YY
I cannot help with the FORMs specific logic.
Does if return the correct subset if you hard-code the date?
SET_BLOCK_PROPERTY('pur_bs
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/YY
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
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'')');
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_bs
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.
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_bs
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.
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.
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_bs
But that doesn't seem correct to me. The examples were using strings, so I would think you want:
SET_BLOCK_PROPERTY('pur_bs
At least based on the examples I saw that seemed to be the syntax. Seems to defeat the purpose of a bind variable.
SET_BLOCK_PROPERTY('pur_bs
But that doesn't seem correct to me. The examples were using strings, so I would think you want:
SET_BLOCK_PROPERTY('pur_bs
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.
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
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
set_block_property('pur_bsh',default_where,'inv_date >=to_date('''|| :BASIC_BLOCK.D_from ||''',''dd-mm-rrrr'')');
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.
>>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_bs
adding the necessary form and field name.
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_bs
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'')');
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.
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.
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_bs
but your code works well
SET_BLOCK_PROPERTY('pur_bs
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_bs
What is wrong with:
SET_BLOCK_PROPERTY('pur_bs
I don't think it is recommended, but this might work:
SET_BLOCK_PROPERTY('pur_bs
The bind variables should be part of the string, they should not be concatenated. It really should be this:
SET_BLOCK_PROPERTY('pur_bs
SET_BLOCK_PROPERTY('pur_bs
The bind variables should be part of the string, they should not be concatenated. It really should be this:
SET_BLOCK_PROPERTY('pur_bs
>>'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.
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
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
this return the correct data , between 01-mar and 03-mar
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'')');
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'')');
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'')');
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.
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.
You can always use any explicit date format you like when you are converting string values to dates, or vice versa, as long as your format mask matches your data. So, you can use format masks like:
MM/DD/YYYY
DD/MM/YYYY
DD-MON-YYYY
etc., as long as your literal data matches the format mask you choose.
But, you will always get into trouble (sooner or later) if you force Oracle to do implicit datatype conversions (with no format mask) like this:
SET_BLOCK_PROPERTY('pur_bs
or this:
SET_BLOCK_PROPERTY('pur_bs
MM/DD/YYYY
DD/MM/YYYY
DD-MON-YYYY
etc., as long as your literal data matches the format mask you choose.
But, you will always get into trouble (sooner or later) if you force Oracle to do implicit datatype conversions (with no format mask) like this:
SET_BLOCK_PROPERTY('pur_bs
or this:
SET_BLOCK_PROPERTY('pur_bs
>>Ok , in general , can I use my own format within function to_date ?
Mark summed it up nicely but to answer this direct question: Yes.
Pick a format and make sure everything matches. What format you choose is entirely up to you. There is almost an infinite number of possibilities.
I would suggest you consult the documentation:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements004.htm#SQLRF00212
Mark summed it up nicely but to answer this direct question: Yes.
Pick a format and make sure everything matches. What format you choose is entirely up to you. There is almost an infinite number of possibilities.
I would suggest you consult the documentation:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements004.htm#SQLRF00212
for fear of confusing the matter, I would strongly suggest you stop using "ambiguous" date formats
(03/01/2015 is it month then day? or day then month?) and don't rely on just YY for year either
try this format:
YYYY-MM-DD
it is the very widely recognized (and an ISO standard) e.g.
to_date('2015-03-01','YYYY
absolutely no points please
(03/01/2015 is it month then day? or day then month?) and don't rely on just YY for year either
try this format:
YYYY-MM-DD
it is the very widely recognized (and an ISO standard) e.g.
to_date('2015-03-01','YYYY
absolutely no points please
Premium Content
You need an Expert Office subscription to comment.Start Free Trial