About the use of the NVL () function in Oracle, the Chinese Medicine Department inquires the discharge patient information error

Chinese Medicine Department inquires the Discharge patient Information Report the following error

Then see the SQL for the interface

Then run the code inside the Plsql,

Error

Processing method:

1. Determine the location of the error

Now determine where the error is the two lines of code that were written off,

And then

And then this is the reason for the error,

Because he was diagnosed with two admissions.

I would like to judge, if it is a TCM ward to take the diagnosis of traditional Chinese medicine, or take Western medicine

But Ningo has given a better way to solve the problem.

Using the NVL function

Before you introduce this, you have to understand what a null value in Oracle is NULL

1.NVL function

The format of the NVL function is as follows: NVL (EXPR1,EXPR2)

The meaning is: if the first parameter of Oracle is empty then the value of the second parameter is displayed, and if the value of the first parameter is not NULL, the first parameter is displayed.

2 NVL2 function

The format of the NVL2 function is as follows: NVL2 (EXPR1,EXPR2, EXPR3)

The meaning is: if the first argument of the function is empty then the value of the second parameter is displayed, and if the value of the first parameter is not NULL, the value of the third parameter is displayed

3. Nullif function

The effect of the Nullif (EXP1,EXPR2) function is to return null (NULL) if EXP1 and exp2 are equal, otherwise the first value is returned.

4.Coalesce function

The function of the Coalese function is somewhat similar to the functions of NVL, and its advantage is that there are more options.

The format is as follows:

COALESCE (Expr1, expr2, EXPR3 ... exprn)

Represents a placeholder that can specify multiple expressions. All expressions must be of the same type, or can be implicitly converted to the same type.
Returns the first non-empty expression in the expression, such as the following statement: SELECT COALESCE (null,null,3,4,5) from dual its return result is: 3
If all arguments are NULL, COALESCE returns a null value. COALESCE (expression1,... N) is equivalent to this case function:

------------------------------------------------

So brother Ning changed the SQL to

NVL ((select JBMC from ys_zy_jbzd where zdlb = ' admission diagnosis ' and Jbzh = 1 and znxh = 1 and zfbz = 0 and zxlb = 1 and TJBZ = 1 and Jzhm = zy_brry.zyh), (select JBMC from ys_zy_jbzd where zdlb = ' admission diagnosis ' and Jbzh = 1 and znxh = 1 and zfbz = 0 and zxlb = 2 and tjbz = 1 and jzhm = Zy_brry.zyh)) Ryzd,

NVL ((select JBMC from ys_zy_jbzd where zdlb = ' discharge diagnosis ' and Jbzh = 1 and znxh = 1 and zfbz = 0 and zxlb = 1 and TJBZ = 1 and Jzhm = zy_brry.zyh), (select JBMC from ys_zy_jbzd where zdlb = ' discharge diagnosis ' and Jbzh = 1 and znxh = 1 and zfbz = 0 and zxlb = 2 and tjbz = 1 and jzhm = zy_brry.zyh)) Cyzd

----------------------------------------------------------------------------

About the use of the NVL () function in Oracle, the Chinese Medicine Department inquires the discharge patient information error