Request 3: (3 pts) Write/execute a SQL statement that lists all the data in each column in the Departments table. If a column might contain NULL values, the phrase “-NONE-“ must print in that column for that row. For this last request use one or more SQL single-row functions to accomplish this. Do not use an iSQL*PLUS report formatting command such as SET COLUMN * NULL ‘-NONE-’ which affects all columns during a session.


SELECT department_id, department_name, NVL(manager_id, 0 ) AS manager_id, location_id
FROM departments


MY question: So the code above is my attempt at the request 3: but as you can see i am not having the nvl return "none" rather i am returning 0
I am not sure how to make it say none, like if i put none where the 0 is it doesnt run the query because i think NVL only allows for integers rather than string
Does anyone have a suggestion on how i can fix this?

use a case statement.

So im having problem with condition 1 i think because i keep getting " "FROM keyword not found where expected"

My current case looks like :



sorry for this - im still noob at sql :X

This post was edited by Noobtard on Sep 28 2013 03:17pm

CASE
WHEN manager_id is null THEN 'none'
ELSE manager_ID
end


edit:
nvl should also work and this is probably prefered way to solve this problem.

This post was edited by Ironfister on Sep 28 2013 03:29pm

Yeah this is odd becaues:



I still get error with this current code?

"ORA-00923: FROM keyword not found where expected
00923. 00000 - "FROM keyword not found where expected"

It says the error is where the 'm' is after the CASE -- That is where it is saying it is located. But i double checked the table column names etc, and it is pointing to the correct column?

Google for examples of the case statement. i find it odd that you'd use the column name right after CASE then use the column name again in the condition. i think it should only be in one place, not both. otherwise what's the point?

/edit:

http://www.techonthenet.com/oracle/functions/case.php

yeah notice how those examples only use it in one place?

This post was edited by carteblanche on Sep 28 2013 04:15pm