Sql - Topic - d2jsp

Member

Posts: 1,849

Joined: May 31 2008

Gold: 2,571.50

Feb 16 2013 05:02pm

Question:

Code

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.

What ive tried:

Code

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

So it doesn't work, where i have 'NONE' if i put a integer or a numeric value in it will output the numeric value, is there a way i can make it so it will output none here? Cause im kinda lost been reading alot of google and still cant find answer.
I will donate if you post something thats helpful to me solving this problem

Thanks ahead of time.

This post was edited by Noobtard on Feb 16 2013 05:10pm

irimi

Member

Posts: 4,605

Joined: Sep 15 2011

Gold: 9,464.00

Feb 16 2013 05:04pm

is there a reason why you have what looks like a syntax error inside the NVL call?

Noobtard

Member

Posts: 1,849

Joined: May 31 2008

Gold: 2,571.50

Feb 16 2013 05:10pm

Quote (irimi @ Feb 16 2013 06:04pm)

is there a reason why you have what looks like a syntax error inside the NVL call?

Sorry yes, i do I have one two many ' - but it still is a problem with that fixed - ill edit that fix quick ty.

AbDuCt

Member

Posts: 13,425

Joined: Sep 29 2007

Gold: 0.00

Warn: 20%

Feb 16 2013 05:27pm

without seeing the table itself i cant really imagine what it would look like lol.

This post was edited by AbDuCt on Feb 16 2013 05:27pm

Noobtard

Member

Posts: 1,849

Joined: May 31 2008

Gold: 2,571.50

Feb 16 2013 05:33pm

Quote (AbDuCt @ Feb 16 2013 06:27pm)

without seeing the table itself i cant really imagine what it would look like lol.

TABLE: -- Good point sorry forgot to add.

AbDuCt

Member

Posts: 13,425

Joined: Sep 29 2007

Gold: 0.00

Warn: 20%

Feb 16 2013 06:22pm

wat backend database are you using?

choombawoomba

Member

Posts: 1,967

Joined: Jul 10 2007

Gold: 1,252.00

Feb 16 2013 06:54pm

This looks like the Oracle SQl Developer, so I assume your database engine is some version of Oracle. That being the case, if you were to check the entry for NVL in the Oracle Docs, you would learn:

Quote

The arguments expr1 and expr2 can have any datatype. If their datatypes are different, then Oracle Database implicitly converts one to the other. If they are cannot be converted implicitly, the database returns an error. The implicit conversion is implemented as follows:

If expr1 is character data, then Oracle Database converts expr2 to the datatype of expr1 before comparing them and returns VARCHAR2 in the character set of expr1.

If expr1 is numeric, then Oracle determines which argument has the highest numeric precedence, implicitly converts the other argument to that datatype, and returns that datatype.

So basically Oracle wants to convert 'none' to a numeric datatype since the first argument for NVL is a numeric type. 'none' can't be parsed as a number so you get the behavior you are seeing. One workaround would be to cast manager id as a char, so that your first argument is a char, which is compatible with the value 'none'

I don't have an Oracle installation handy to test, but essentially this is what you are looking for:

Code

SELECT department_id, department_name, NVL(TO_CHAR(manager_id), 'NONE') AS manager_id, location_id
FROM departments

This post was edited by choombawoomba on Feb 16 2013 06:55pm

Go Back To Programming & Development Topic List