CODEWITHSUNDEEP
sqloracle-database
StudentRik
StudentRik

Reputation: 1049

How to display message instead of NULL

I am returning a query with names, courses and course codes, but where the course code is null i would like to display "NOT ENROLLED". Could I do this by using a 'default'? The select statement is coming from a VIEW would this make any difference.

CREATE VIEW STUDENT_LIST
(studentname, dateofbirth, coursecode)
AS 
SELECT COURSECODE, STUDENTNAME, DATEOFBIRTH
FROM STUDENT;

SELECT STUDENTNAME, DATEOFBIRTH, NVL(COURSECODE,"NOT_ENROLLED")
FROM STUDENT_LIST;

I get the reply NOT ENROLLED invalid identifier... I have tried without quotes I am using oracle.

Upvotes: 4

Views: 7693

Answers (6)

Anonymous
Anonymous

Reputation: 1

One aspect of the problem not covered here is that we have only considered a general scenario of entering a text "display message" in a char field in a table.

If it were say a different number field like for example the DOB... in that case we convert use to_char and save the field otherwise we would be given an error for invalid input

SELECT e.studentname, NVL(TO_CHAR(e.dateofbirth),'PLS Contact admin to update'), NVL(e.COURSECODE,'NOT_ENROLLED')
FROM STUDENT_LIST e;

This would save the text to the number field which otherwise would have resulted in an error.

Upvotes: 0

dani herrera
dani herrera

Reputation: 51655

To avoid NOT ENROLLED invalid identifier reply you should replace double quote by single quote:

replace:

SELECT STUDENTNAME, DATEOFBIRTH, NVL(COURSECODE,"NOT_ENROLLED")
FROM STUDENT_LIST;

by:

SELECT STUDENTNAME, DATEOFBIRTH, NVL(COURSECODE,'NOT_ENROLLED')
FROM STUDENT_LIST;

For Oracle double quotes are for columns or tables identifiers (for example that contains a space: "total items")

Upvotes: 0

antlersoft
antlersoft

Reputation: 14786

SELECT STUDENTNAME, DATEOFBIRTH, NVL(COURSECODE,"NOT_ENROLLED") FROM STUDENT_LIST;

I get the reply NOT ENROLLED invalid identifier... I have tried without quotes I am using oracle.

This is because you are using double quotes around NOT_ENROLLED. In Oracle, you use single quotes around string literals. Double quotes delimit identifiers that may have embedded spaces, so Oracle thinks NOT_ENROLLED is an identifier, not a string literal.

Use this:

    SELECT STUDENTNAME, DATEOFBIRTH, NVL(COURSECODE,'NOT_ENROLLED') FROM STUDENT_LIST;

Upvotes: 3

Bert
Bert

Reputation: 82459

ORACLE has NVL()

SQL Server has ISNULL()

MySQL has IFNULL()

All of the above and PostgreSQL support COALESCE().

So,

SELECT STUDENTNAME, 
       DATEOFBIRTH, 
       NVL(COURSECODE,'NOT_ENROLLED') 
FROM STUDENT_LIST;

or

SELECT STUDENTNAME, 
       DATEOFBIRTH, 
       COALESCE(COURSECODE,'NOT_ENROLLED') 
FROM STUDENT_LIST;

in Oracle.

Upvotes: 2

S. A. Malik
S. A. Malik

Reputation: 3655

If you are using mySql, then yes! you can set a default value in place for NULL. And whenever a value will be none that value will be saved in database.

Upvotes: 0

Gigi
Gigi

Reputation: 29421

Use nvl. Different implementations may vary, e.g. for MySQL you want ifnull.

Upvotes: 1

Related Questions