| null field and changing data question [message #318383] |
Tue, 06 May 2008 13:18  |
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  |
Michel Cadot
Messages: 16994
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
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 |
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 |
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 |
Michel Cadot
Messages: 16994
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
| 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 |
Michel Cadot
Messages: 16994
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
| 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 |
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 |
Littlefoot
Messages: 6007
Registered: June 2005
Location: Croatia, Europe |
Senior Member |
|
|
|
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 |
flyboy
Messages: 496
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 |
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 |
flyboy
Messages: 496
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 |
pablolee
Messages: 987
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 |
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 |
Michel Cadot
Messages: 16994
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
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 |
flyboy
Messages: 496
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 |
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 |
joy_division
Messages: 2085
Registered: February 2005
Location: NY |
Senior Member |
|
|
|
You should show us your solution, because that sounds like a kooky way to achieve your goal.
|
|
|
]
Members
Search
Help
Register
Login
Home
