Home » Developer & Programmer » Forms » default where
default where [message #622280] Sun, 24 August 2014 04:18 Go to next message
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.

Report message to a moderator

Re: default where [message #622281 is a reply to message #622280] Sun, 24 August 2014 04:25 Go to previous messageGo to next message
Littlefoot
Messages: 21807
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.

Report message to a moderator

Re: default where [message #622283 is a reply to message #622281] Sun, 24 August 2014 04:48 Go to previous messageGo to next message
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

Report message to a moderator

Re: default where [message #622284 is a reply to message #622283] Sun, 24 August 2014 04:51 Go to previous messageGo to next message
Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As I said, if both of these are DATEs, you don't need to apply any function except, maybe, TRUNC.

TO_DATE on a DATE (as you suggested in your last sentence) is terribly wrong.

Report message to a moderator

Re: default where [message #622285 is a reply to message #622284] Sun, 24 August 2014 05:10 Go to previous messageGo to next message
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.

Report message to a moderator

Re: default where [message #622286 is a reply to message #622285] Sun, 24 August 2014 05:18 Go to previous messageGo to next message
Littlefoot
Messages: 21807
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.

Report message to a moderator

Re: default where [message #622287 is a reply to message #622286] Sun, 24 August 2014 05:36 Go to previous messageGo to next message
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.

Report message to a moderator

Re: default where [message #622308 is a reply to message #622287] Mon, 25 August 2014 01:54 Go to previous messageGo to next message
Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OK then, here's how I do that.

:CTRL.DATUM is a date datatype form item. In order to use it in DEFAULT_WHERE, first I convert it to a character (TO_CHAR) and then back to date (TO_DATE). CHR(39) is a single quote character; sometimes it is handy in order to avoid mess with too many single quote characters.

l_where := ' and datum = ' ||chr(39)|| to_date(to_char(:ctrl.datum, 'dd.mm.yyyy'), 'dd.mm.yyyy') ||chr(39);

Report message to a moderator

Re: default where [message #622527 is a reply to message #622308] Wed, 27 August 2014 06:48 Go to previous messageGo to next message
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"

Report message to a moderator

Re: default where [message #622528 is a reply to message #622527] Wed, 27 August 2014 07:11 Go to previous message
Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Did you go to Help menu and chose "Display Error"? It'll tell you what's wrong. I suspect it is AND effective_start_date ... (try to remove AND).

By the way, yes - I tried it. Here's an example:

/forum/fa/12125/0/

I attached a FMB file (created with Forms 10g 9.0.4); see if you can open it and review what I've done.
  • Attachment: l_where.png
    (Size: 30.44KB, Downloaded 1480 times)
  • Attachment: L_WHERE.fmb
    (Size: 48.00KB, Downloaded 1449 times)

Report message to a moderator

Previous Topic: Mouse Navigation Problem
Next Topic: Application Blocked by Security Settings
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Apr 16 00:34:54 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.