如何显示消息而不是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.

我将返回一个带有名称、课程和课程代码的查询，但是如果课程代码为空，我想显示“未注册”。我可以用“默认”来实现吗?select语句来自一个视图，这有什么区别吗?

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.

我得到的答复不是登记无效的标识符……我试过没有引用，我在使用oracle。

6 个解决方案

#1

ORACLE has NVL()

甲骨文NVL()

SQL Server has ISNULL()

SQL Server ISNULL()

MySQL has IFNULL()

MySQL有IFNULL()

All of the above and PostgreSQL support COALESCE().

以上所有和PostgreSQL支持合并()。

So,

所以,

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

或

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

in Oracle.

在Oracle。

#2

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.

我得到的答复不是登记无效的标识符……我试过没有引用，我在使用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.

这是因为您在not_enrollment前后使用了双引号。在Oracle中，在字符串文本中使用单引号。双引号分隔可能具有嵌入空格的标识符，因此Oracle认为not_enrollment是一个标识符，而不是字符串字面量。

Use this:

用这个:

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

#3

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

使用nvl。不同的实现可能会有所不同，例如对于需要ifnull的MySQL。

#4

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.

如果你使用的是mySql，那是肯定的!您可以为NULL设置一个默认值。当一个值为none时，该值将保存在数据库中。

#5

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")

对于Oracle，双引号用于列或表标识符(例如，包含空格:“total items”)

#6

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.

这里没有讨论的问题的一个方面是，我们只考虑了在表的char字段中输入文本“显示消息”的一般场景。

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

如果它是一个不同的数字域，比如DOB。在这种情况下，我们转换使用to_char并保存字段，否则会给无效输入一个错误

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.

这将把文本保存到number字段中，否则会导致错误。

#1