Oracle 11g:查询不返回任何内容时，默认为静态值 [英] Oracle 11g: Default to static value when query returns nothing

问题描述

在Oracle 11g中工作时，当表中存在该输入值时，我需要选择一个与该输入值相对应的数据，而当输入值不存在时，则需要选择一个静态默认值.我能找到的最好的方法就是写这样的东西:

Working in Oracle 11g, I have a need to select a datum corresponding to an input value when that value exists in a table, and to instead select a static default value when it does not. The best way I could find to accomplish this was to write something like this:

SELECT desired_datum
FROM (
    --Try to get explicit datum
    SELECT desired_datum, 1 AS was_found
    FROM data_table
    WHERE the_key = &input_value
    UNION
    --Get default datum
    SELECT 'default' AS desired_datum, 0 AS was_found
    FROM dual
    --Put explicit datum on top, if it exists
    ORDER BY was_found DESC
) finder
WHERE ROWNUM <=1;

似乎必须有一些惯用的方式来执行此操作，而这并不依赖于ORDER BY的这种奇怪用法，但是我找不到它.有谁知道更好的方法吗?

It seems like there must be some idiomatic way to do this which doesn't depend on this strange use of ORDER BY, but I couldn't find it. Does anyone know of any better methods?

推荐答案

这应该是您所做的工作的简单版本:

This should be a simpler version of what you did:

SELECT NVL(desired_datum, 'default') AS desired_datum
FROM DUAL LEFT JOIN  data_table ON the_key = &input_value

这篇关于Oracle 11g:查询不返回任何内容时，默认为静态值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！