default where [message #622280] |
Sun, 24 August 2014 04:18 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
hany_marawan
Messages: 198 Registered: April 2005 Location: Cairo - Egypt
|
Senior Member |
|
|
Hello,
I want putting date formate in the default where, i.e
Set_Block_Property('MOFI_POSITION_TRANS_V',DEFAULT_WHERE,'TRANS_LINK='''||:MAIN_BLOCK.link
||''' and GRADE_NO='''||:MAIN_BLOCK.GRADE
||''' and JOB_NO='''||:MAIN_BLOCK.JOBNO
||''' and EFFECTIVE_START_DATE!='''||to_char(e_start_date,'dd-mm-yyyy')||'''');
As you see I want putting the formate of EFFECTIVE_START_DATE like to_char(EFFECTIVE_START_DATE,'dd-mm-yyyy')
How Can I do this
Thanks.
|
|
|
Re: default where [message #622281 is a reply to message #622280] |
Sun, 24 August 2014 04:25 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
These two lines don't match:
||''' and EFFECTIVE_START_DATE!='''||to_char(e_start_date,'dd-mm-yyyy')||'''');
vs.
As you see I want putting the formate of EFFECTIVE_START_DATE like to_char(EFFECTIVE_START_DATE,'dd-mm-yyyy')
Which one of these two is true?
Furthermore, what is EFFECTIVE_START_DATE column's datatype? What is E_START_DATE? As it is not preceded with a colon, I suspect that it is not a form item. What is it, then? If it is a column, what is its datatype?
If both of them are DATE datatypes, you don't need TO_CHAR as it won't do anything good. Why do you want to use such a format mask? Is it because EFFECTIVE_STAT_DATE contains both date and time? If so, you probably need to truncate its value, instead of applying TO_CHAR to it, i.e. TRUNC(EFFECTIVE_START_DATE).
As of whether DEFAULT_WHERE clause is valid or not, I'd suggest you to put the whole clause into a variable and display it on the screen (either with the MESSAGE built-in, or put it into a form item) so that you could verify whether it is composed in a correct manner or not.
|
|
|
Re: default where [message #622283 is a reply to message #622281] |
Sun, 24 August 2014 04:48 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
hany_marawan
Messages: 198 Registered: April 2005 Location: Cairo - Egypt
|
Senior Member |
|
|
the type of EFFECTIVE_START_DATE is date and it 's field in the table
E_START_DATE is a variable of type date
and I want
||''' and to_char(EFFECTIVE_START_DATE,'dd-mm-yyyy')!='''||to_char(e_start_date,'dd-mm-yyyy')||'''');
or you can write it with to_date for the both
|
|
|
|
Re: default where [message #622285 is a reply to message #622284] |
Sun, 24 August 2014 05:10 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
hany_marawan
Messages: 198 Registered: April 2005 Location: Cairo - Egypt
|
Senior Member |
|
|
Okay , You right and I am sorry for not explaining why I want converting it to to_char and using it in the default where.
I am working in KSA (Saudi Arabia) So I am working in Arabic version beside the English version.
When I put the default formate (date as you said) it's working in the English version not in Arabic, SO I want to try putting formate char and trying what 's the effect.
I hope I explained my problem well.
|
|
|
Re: default where [message #622286 is a reply to message #622285] |
Sun, 24 August 2014 05:18 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Oracle stores dates in its own, internal format, which is not readable by us, humans. Therefore, I suspect that Oracle doesn't care which country you are in - DATE is simply a DATE.
However, when we want to display such values to end users, we do use TO_CHAR function and choose date format which is used in our country. For example, in Croatia, we use DD.MM.YYYY. Some other country uses MM-DD-YYYY. I don't know which format is used in Saudi Arabia, though.
Once again: none of the above should matter while creating DEFAULT_WHERE clause. What you are doing is saying "WHERE one_date_value = another_date_value". Because of the way Oracle stores these values, no additional function (such as TO_CHAR or, even worse, TO_DATE) should be applied.
Note, however, that I don't have any experience with multilingual applications so I apologize if it turns out that I was wrong. Unfortunately, I can't help any further so you'll have to wait for someone else's opinion.
|
|
|
Re: default where [message #622287 is a reply to message #622286] |
Sun, 24 August 2014 05:36 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
hany_marawan
Messages: 198 Registered: April 2005 Location: Cairo - Egypt
|
Senior Member |
|
|
No problem,
I just want knowing the syntax.
i.e I wrote ||''' and GRADE_NO='''||:MAIN_BLOCK.GRADE
How can I write it in formate to_char.
If you know it's good, if not no problem and I thank you for your attention.
|
|
|
|
Re: default where [message #622527 is a reply to message #622308] |
Wed, 27 August 2014 06:48 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
hany_marawan
Messages: 198 Registered: April 2005 Location: Cairo - Egypt
|
Senior Member |
|
|
Did you try this code in the form
. It's not working with me
I wrote
||''' and EFFECTIVE_START_DATE != ' ||chr(39)|| to_date(to_char(e_start_date, 'dd-MON-yy'), 'dd-MON-yy') ||chr(39)||'''')
but it gave me this error
"can not set default_where : invalid value"
|
|
|
|