OraFAQ Forum: Forms » Replace NULL with a different value during display (merged 2)

Home » Developer & Programmer » Forms » Replace NULL with a different value during display (merged 2) (Oracle Forms 6i, WinXP-SP3)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Replace NULL with a different value during display (merged 2) [message #539628]

Tue, 17 January 2012 08:23

termite_paste
Messages: 12
Registered: January 2012

Junior Member

Hi,

There is a table, desc :-
name varchar2(10), class number(1)

values:-

name class
----- ------
qwe 1
asd
zxc 2

Here 'asd's class is NULL.

Now, using forms, how do i display the above with the modification where NULL will be shown as -1?
that is-

name class
----- ------
qwe 1
asd -1
zxc 2

Any suggestions will do Smile

Smile

Report message to a moderator

Re: Replace NULL with a different value during display [message #539629 is a reply to message #539628]

Tue, 17 January 2012 08:27

Tomcrzy
Messages: 101
Registered: December 2011
Location: Chennai,India

Senior Member

romogenia@gmail.com

Use NVL S

Report message to a moderator

Re: Replace NULL with a different value during display [message #539630 is a reply to message #539628]

Tue, 17 January 2012 08:28

termite_paste
Messages: 12
Registered: January 2012

Junior Member

Is it possible?

Report message to a moderator

Re: Replace NULL with a different value during display [message #539631 is a reply to message #539630]

Tue, 17 January 2012 08:28

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Yes. Everyone can do it.

Regards
Michel

Report message to a moderator

Re: Replace NULL with a different value during display [message #539633 is a reply to message #539630]

Tue, 17 January 2012 08:31

termite_paste
Messages: 12
Registered: January 2012

Junior Member

thanks

Smile

\m/

Report message to a moderator

Re: Replace NULL with a different value during display [message #539635 is a reply to message #539633]

Tue, 17 January 2012 08:48

termite_paste
Messages: 12
Registered: January 2012

Junior Member

which trigger to use???

Report message to a moderator

Re: Replace NULL with a different value during display [message #539636 is a reply to message #539635]

Tue, 17 January 2012 08:58

termite_paste
Messages: 12
Registered: January 2012

Junior Member

i am using

BEGIN
:blk_nm.col_nm := NVL(:blk_nm.col_nm , -1);
END;

but after i execute, its sayin cannot update record(Note- only select is allowed)

Please help

[Updated on: Tue, 17 January 2012 08:59]

Report message to a moderator

Re: Replace NULL with a different value during display [message #539645 is a reply to message #539636]

Tue, 17 January 2012 10:15

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

only select is allowed

Yes and this was your question (in addition to your title): "how do i display the above with the modification where NULL will be shown as -1?" and so we answered to this question.

Regards
Michel

[Updated on: Tue, 17 January 2012 10:16]

Report message to a moderator

Re: Replace NULL with a different value during display [message #539647 is a reply to message #539645]

Tue, 17 January 2012 10:19

Tomcrzy
Messages: 101
Registered: December 2011
Location: Chennai,India

Senior Member

romogenia@gmail.com

termite_paste,

The method you had written will not work,why becouse

BEGIN
  :blk_nm.col_nm := NVL(:blk_nm.col_nm , -1);
END;

Here you are setting the record value;
initially it will be Null only.

Report message to a moderator

Re: Replace NULL with a different value during display [message #539669 is a reply to message #539636]

Tue, 17 January 2012 14:20

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

When exactly do you want to do that? When querying existing records from a table? If so, code you used should be in a POST-QUERY trigger. Note that if you set database item value to -1 (instead of NULL), Forms will consider you updated a record and - if you by any chance commit - you'll save -1 into a table.

Therefore, perhaps you should consider creating a non-database item that would be used just for displaying values.

Report message to a moderator

Re: Replace NULL with a different value during display [message #539674 is a reply to message #539669]

Tue, 17 January 2012 15:00

Bill B
Messages: 1971
Registered: December 2004

Senior Member

select name,nvl(class,-1) class
from my_table;

Report message to a moderator

Re: Replace NULL with a different value during display [message #539686 is a reply to message #539669]

Tue, 17 January 2012 23:21

termite_paste
Messages: 12
Registered: January 2012

Junior Member

@Littlefoot,

your solution seems correct, i will implement it rite nw!

thanks for your help Smile

Smile

Cheers

Report message to a moderator

Display a value instead of NULL in a DataBlock Item... [message #539736 is a reply to message #539628]

Wed, 18 January 2012 05:43

termite_paste
Messages: 12
Registered: January 2012

Junior Member

Hi,

I have created a form.
Here, there is a Data Block named test_block.
It contains items : Col_1(TEXT ITEM), Col_2(TEXT ITEM), Col_3(LIST_ITEM)
In the database, Col_3(datatype NUMBER) holds some NULL values.
But in the form I want to display -1 instead of NULL. Using NVL is a logical solution, but where to use it? And how?
Please HELP!!!

PS - I dont want to create any non-database field.

Thanks.

Report message to a moderator

Re: Display a value instead of NULL in a DataBlock Item... [message #539737 is a reply to message #539736]

Wed, 18 January 2012 05:46

cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester

Senior Member

Do you want to update the nulls in the database to -1?
If you do then do the update, make the column not null, and don't bother changing your form.
If you don't then you have to use a non-database item.

Report message to a moderator

Re: Display a value instead of NULL in a DataBlock Item... [message #539741 is a reply to message #539737]

Wed, 18 January 2012 05:59

termite_paste
Messages: 12
Registered: January 2012

Junior Member

Fingers crossed for a second opinion! ;D

Srry, @cookiemonster, but i am a bit desperate rite now!!!

Report message to a moderator

Re: Display a value instead of NULL in a DataBlock Item... [message #539750 is a reply to message #539741]

Wed, 18 January 2012 06:26

cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester

Senior Member

What exactly is the problem with using a non-database item?

Report message to a moderator

Re: Display a value instead of NULL in a DataBlock Item... [message #539756 is a reply to message #539750]

Wed, 18 January 2012 06:33

termite_paste
Messages: 12
Registered: January 2012

Junior Member

@cookiemonster, is there a way around?

Report message to a moderator

Re: Display a value instead of NULL in a DataBlock Item... [message #539761 is a reply to message #539756]

Wed, 18 January 2012 06:56

cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester

Senior Member

You could create a view that selects from the table and does the appropriate nvl and base the block on that.
It might cause issues with insert and update of the nvl'd column though - you'll have to test it.

Report message to a moderator

Re: Display a value instead of NULL in a DataBlock Item... [message #539766 is a reply to message #539761]

Wed, 18 January 2012 07:24

termite_paste
Messages: 12
Registered: January 2012

Junior Member

ok, thanks \m/

Report message to a moderator

Re: Display a value instead of NULL in a DataBlock Item... [message #539772 is a reply to message #539766]

Wed, 18 January 2012 08:05

cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester

Senior Member

Actually it definitely won't work if you want to insert/update data.

Report message to a moderator

Re: Display a value instead of NULL in a DataBlock Item... [message #539775 is a reply to message #539772]

Wed, 18 January 2012 08:34

termite_paste
Messages: 12
Registered: January 2012

Junior Member

the solutions i got are
1. create view
2. use non-database items.

Isnt dere any other solution, such as by using property palette of the data block or the item???

Report message to a moderator

Re: Display a value instead of NULL in a DataBlock Item... [message #539776 is a reply to message #539775]

Wed, 18 January 2012 08:38

Bill B
Messages: 1971
Registered: December 2004

Senior Member

Another solution is use a view and have forms triggers on the datablock use the triggers ON-INSERT, ON-UPDATE, and ON-DELETE then you can do what ever you want. Think of it as an instead trigger on the view.

Report message to a moderator

Re: Display a value instead of NULL in a DataBlock Item... [message #539777 is a reply to message #539775]

Wed, 18 January 2012 08:39

cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester

Senior Member

Do you really think that if there was a property we wouldn't have mentioned it already?

Only other alternative is to base the block on stored procedures, but that's complicated and most people struggle to make it work.

Report message to a moderator

Re: Display a value instead of NULL in a DataBlock Item... [message #539869 is a reply to message #539777]

Thu, 19 January 2012 00:30

termite_paste
Messages: 12
Registered: January 2012

Junior Member

Ok.

Smile

I mentioned property palette 'cos i thot dat there wud be a way to use NVL here!

Report message to a moderator

Re: Display a value instead of NULL in a DataBlock Item... [message #539872 is a reply to message #539869]

Thu, 19 January 2012 00:44

Tomcrzy
Messages: 101
Registered: December 2011
Location: Chennai,India

Senior Member

romogenia@gmail.com

Hi termite_paste,

Try to write the following code inside a post_query (block level trigger)

IF :id IS NULL THEN
  :id  :=-1;
END IF;

Let me know whether it is working or not

[Updated on: Thu, 19 January 2012 00:52]

Report message to a moderator

Re: Display a value instead of NULL in a DataBlock Item... [message #539885 is a reply to message #539872]

Thu, 19 January 2012 01:26

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

termite_paste

'cos i thot dat there wud

Please, use plain English while communicating with people on OraFAQ Forum.

Report message to a moderator

Re: Display a value instead of NULL in a DataBlock Item... [message #539901 is a reply to message #539872]

Thu, 19 January 2012 03:18

cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester

Senior Member

Tomcrzy wrote on Thu, 19 January 2012 06:44

Try to write the following code inside a post_query (block level trigger)
IF :id IS NULL THEN
  :id  :=-1;
END IF;
Let me know whether it is working or not

That requires a non-datablock item as has already been pointed out.

Report message to a moderator

Re: Display a value instead of NULL in a DataBlock Item... [message #539910 is a reply to message #539901]

Thu, 19 January 2012 05:05

Tomcrzy
Messages: 101
Registered: December 2011
Location: Chennai,India

Senior Member

romogenia@gmail.com

Oh cookiemonster...i do not know about you.

i tried out the code using a data block item.

Report message to a moderator

Re: Display a value instead of NULL in a DataBlock Item... [message #539912 is a reply to message #539910]

Thu, 19 January 2012 05:11

cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester

Senior Member

And it'll change it, and oracle forms will record the fact that it's changed and ask the user if they want to save their changes.
But as far as the user is concerned they haven't made any changes.

Never, ever change a database item post-query.

Report message to a moderator

Re: Display a value instead of NULL in a DataBlock Item... [message #539943 is a reply to message #539912]

Thu, 19 January 2012 08:17

Tomcrzy
Messages: 101
Registered: December 2011
Location: Chennai,India

Senior Member

romogenia@gmail.com

Yes you are right.

Report message to a moderator

Re: Display a value instead of NULL in a DataBlock Item... [message #540641 is a reply to message #539736]

Wed, 25 January 2012 01:57

azamkhan
Messages: 557
Registered: August 2005

Senior Member

SET_RECORD_PROPERTY:

Sets the specified record property to the specified value.

On Post-Query trigger using NVL function replace the null value with -1 and populate it in your base table item value like this

:my_colmun := Nvl(:my_Colmun, -1);

And just after this update, issue the SET_RECORD_PROPERTY and change its status to query.

In this way you can show -1 in your data base column without adding any non-database column... Smile

Smile

And because you have force fully changed the record status to QUERY, form will not ask you to save the changes

[Updated on: Wed, 25 January 2012 02:03]

Report message to a moderator

Re: Display a value instead of NULL in a DataBlock Item... [message #540642 is a reply to message #540641]

Wed, 25 January 2012 02:16

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

azamkhan

And because you have force fully changed the record status to QUERY, form will not ask you to save the changes

Indeed. But, if you press "Save", you'll save -1 into that column anyway.

Report message to a moderator

Re: Display a value instead of NULL in a DataBlock Item... [message #540644 is a reply to message #540642]

Wed, 25 January 2012 02:18

azamkhan
Messages: 557
Registered: August 2005

Senior Member

My dear,

Forms only save those records that are marked as 'INSERT' or 'CHANGED'. Form will not save any record with status "NEW" or "QUERY".

So feel free to use it

Report message to a moderator

Re: Display a value instead of NULL in a DataBlock Item... [message #540647 is a reply to message #540644]

Wed, 25 January 2012 02:37

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

What I meant to say (should have, I know ... sorry) was: user can change any item's value in that record, for any reason. Record's status would change. SAVE would save those changes, as well as -1 into that column.

I still believe that Cookiemonster was right when he said that database items' values must not be changed in POST-QUERY trigger.

Which, in addition, from my point of view, makes your approach wrong. It *might* be OK in some special circumstances, but - generally speaking - it is wrong.

Report message to a moderator

Re: Display a value instead of NULL in a DataBlock Item... [message #540648 is a reply to message #540647]

Wed, 25 January 2012 02:59

cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester

Senior Member

As I said back up the thread, if you want to make the DB column hold -1 instead of null you should just update the column and make it not null. If you don't then you can't set the db item in the form to -1 since, as LF points out, sooner or later that value will end up in the DB.
For this reason it makes no sense to ever assign a value to a db item post-query.

Report message to a moderator

Re: Display a value instead of NULL in a DataBlock Item... [message #540649 is a reply to message #540648]

Wed, 25 January 2012 03:03

cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester

Senior Member

And because of the above I've always been of the opinion that if you're using SET_RECORD_PROPERTY then you have a design flaw in your form.
You should never need to order forms to change it's record status.

Report message to a moderator

Re: Display a value instead of NULL in a DataBlock Item... [message #541871 is a reply to message #540649]

Thu, 02 February 2012 23:02

ollivier
Messages: 19
Registered: January 2012
Location: chihuahua

Junior Member

May you can use ON-INSERT trigger block
and insert manualy each record
insert into table ( item1, item2, item3)
values (nvl(:item1,-1, :item2, item3);

So you will storage a -1 value when is null !

May you are creating a problem when there are not !
store a null value and use a nvl( ) statemant when you need to desplay it ! but is just a sugestion !

Good Luck !

Report message to a moderator

Re: Display a value instead of NULL in a DataBlock Item... [message #541882 is a reply to message #541871]

Fri, 03 February 2012 00:07

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

This will cause DUP-VAL-ON-INDEX (if the index exists, that is) or duplicate records (if the index doesn't exist), because Forms will insert a record in its default processing, and you'll try to do the same with the INSERT statement. That's probably not a good idea.

As of displaying -1 instead of NULL using the NVL function, it was discussed previously. Did you read what's being said, or do you just answer the first message and discard what other people said?

[Updated on: Fri, 03 February 2012 00:08]

Report message to a moderator

Re: Display a value instead of NULL in a DataBlock Item... [message #541915 is a reply to message #541882]

Fri, 03 February 2012 03:06

cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester

Senior Member

Littlefoot wrote on Fri, 03 February 2012 06:07

This will cause DUP-VAL-ON-INDEX (if the index exists, that is) or duplicate records (if the index doesn't exist), because Forms will insert a record in its default processing, and you'll try to do the same with the INSERT statement. That's probably not a good idea.

Actually no - on-insert overrides default processing.

That said I've already pointed out the correct approach if you want to store -1 further up the thread. on-insert is not involved.

And why you would write an on-insert trigger when you could just assign the value to the appropriate datablock item in pre-insert I don't know.

As far as I'm concerned if you need to use an on trigger you've got a design flaw somewhere.

Report message to a moderator

Re: Display a value instead of NULL in a DataBlock Item... [message #541942 is a reply to message #541915]

Fri, 03 February 2012 04:21

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Cookiemonster

on-insert overrides default processing

I see; thank you, didn't know that (didn't use it, obviously. Nor read the documentation /forum/fa/1606/0/

/forum/fa/1606/0/

). Apologies, Ollivier!

Report message to a moderator

Previous Topic:	search for NULL fields
Next Topic:	Exit Form Move to Another Block

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Feb 09 22:18:45 CST 2025