Home » SQL & PL/SQL » SQL & PL/SQL » null field and changing data question
null field and changing data question [message #318383] Tue, 06 May 2008 13:18 Go to next message
googenfrog
Messages: 9
Registered: May 2008
Junior Member
Hello all...

I have 2 questions.

1. How do i fill a field that is empty with a word like if someone does not have an entry I would like the word None put in the blank field.

2. And how do I write a select statement that continues with lots of records that constantly changes.

Thanks
Re: null field and changing data question [message #318384 is a reply to message #318383] Tue, 06 May 2008 13:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1. NVL

2. Select sees the database as it is as the time select starts. It does not matter if data change in the meanwhile.

Regards
Michel
Re: null field and changing data question [message #318392 is a reply to message #318384] Tue, 06 May 2008 14:35 Go to previous messageGo to next message
googenfrog
Messages: 9
Registered: May 2008
Junior Member
Is this how you write it because it doesn't work


NVL(e.first_name || ' ' || e.last_name , 'NO NAME')
Re: null field and changing data question [message #318395 is a reply to message #318384] Tue, 06 May 2008 14:47 Go to previous messageGo to next message
googenfrog
Messages: 9
Registered: May 2008
Junior Member
And for question 2 I have to find the 10 highest salaries and it cannot be from the 30 some records in the tables. It has to be salaries that are always changing from day to day. Thanks
Re: null field and changing data question [message #318396 is a reply to message #318392] Tue, 06 May 2008 14:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
e.first_name || ' ' || e.last_name

This cannot be null as it contains at least one blank.
Correct that or use NVL2.

Regards
Michel
Re: null field and changing data question [message #318397 is a reply to message #318395] Tue, 06 May 2008 14:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
googenfrog wrote on Tue, 06 May 2008 21:47
And for question 2 I have to find the 10 highest salaries and it cannot be from the 30 some records in the tables. It has to be salaries that are always changing from day to day. Thanks

I don't understand. You want to find 10 rows in the table but it must not be in the table???

Regards
Michel

Re: null field and changing data question [message #318404 is a reply to message #318397] Tue, 06 May 2008 15:10 Go to previous messageGo to next message
googenfrog
Messages: 9
Registered: May 2008
Junior Member
I can't use a select statement based on just the 30 records. It has to continue to work if the table contained numerous records that have salaries that change from one day to the next.
Re: null field and changing data question [message #318405 is a reply to message #318404] Tue, 06 May 2008 15:13 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Oh, you must be looking for Top N queries?
Re: null field and changing data question [message #318406 is a reply to message #318383] Tue, 06 May 2008 15:27 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> I can't use a select statement based on just the 30 records.

So insert so many rows as you want. The query will not change with number of rows.

> It has to continue to work if the table contained numerous records

You can benefit from an index on salary column, as it will avoid full table scan. But other operations (inserts/updates) may get slower.

> that have salaries that change from one day to the next.

Although the results will change with changing data, they will be consistent from the time the query was issued (as Michel stated). This is called read consistency, described in the Concepts documentation book or http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:27330770500351 thread on AskTom.
Re: null field and changing data question [message #318417 is a reply to message #318406] Tue, 06 May 2008 16:26 Go to previous messageGo to next message
googenfrog
Messages: 9
Registered: May 2008
Junior Member
ok everything worked except the NVL does not work with the first name and last name concatenated and the NVL2 doesn't work. I did get the top salaries though.
Re: null field and changing data question [message #318419 is a reply to message #318383] Tue, 06 May 2008 16:45 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> the NVL does not work with the first name and last name concatenated

But you showed here concatenation with additional space. That expression will never be NULL, as Michel remarked earlier.

> and the NVL2 doesn't work

So you used the wrong expression. Think. Which expression will be NULL, when (and only when) first name and last name will be NULL?
Re: null field and changing data question [message #318420 is a reply to message #318417] Tue, 06 May 2008 16:48 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
NVL does work you are using it incorrectly. i.e. you are concatenating a non null constant (the ' ') into your sting. You might also want to think about the trim function.
Re: null field and changing data question [message #318431 is a reply to message #318420] Tue, 06 May 2008 18:10 Go to previous messageGo to next message
googenfrog
Messages: 9
Registered: May 2008
Junior Member
but there is blanks in the NAME column. If the department does not have a supervisor id then there will not be a name in the superviaor name field. So maybe I should have the NVL with the supervisor id instead.
Re: null field and changing data question [message #318496 is a reply to message #318431] Wed, 07 May 2008 00:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
NVL2 should work, you just don't use it properly but as you didn't post what you did we can't say you what you did wrong.

Regards
Michel
Re: null field and changing data question [message #318529 is a reply to message #318383] Wed, 07 May 2008 02:04 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> but there is blanks in the NAME column.

Which column? There are two source columns. You may remove blanks using one of TRIM / REPLACE / REGEXP_REPLACE function.
Or do you mean the output column? Then look at NVL2 function closer - you may specify different expressions for checked and output value.
Re: null field and changing data question [message #319982 is a reply to message #318529] Tue, 13 May 2008 10:39 Go to previous messageGo to next message
googenfrog
Messages: 9
Registered: May 2008
Junior Member
Hello All...

I did get an answer on the concat first name and last name. I had to do a concat on 2 NVL's. I needed "No Name" to go into a blank column. I did a NVL on the No and another on the Name. Thanks
Re: null field and changing data question [message #319998 is a reply to message #319982] Tue, 13 May 2008 12:09 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
You should show us your solution, because that sounds like a kooky way to achieve your goal.
Previous Topic: Multiple Rows Returned but Only Want One Based on stat_cde
Next Topic: Constraints
Goto Forum:
  


Current Time: Sun Dec 04 02:46:24 CST 2016

Total time taken to generate the page: 0.09035 seconds