PL/SQL Statement to Return NULL when NO_RECORD_FOUND, or NULL found value in column ELSE return TO_DATE

I have a case in a payroll document where the Termination Date of an Employee COULD be (null) if there is no Termination Date. Sometimes, I got something other than a date (garbage). Sometimes I had a bonafide termination date report.

In this case, I did not wish to write long lines of code with IF, SELECT, CASE or EXECEPTION when trying to determine whether or not there was a record found, nor if the date was garbage or not. I just wanted a NULL or a TO_DATE.

I probably spent more time on this than it was worth, but thought I would share my findings and see what everyone thought. I appreciate constructive criticism or improvement.

I would like however to add ' parenthesis ' around the Date when it is NOT NULL, so that I can use it in a Dynamic SQL I am writing. ie, it would be '15-Feb-2012' or just NULL.

I'm playing with the DECODE to get that to happen now, but still figuring it out. Ideas?

SELECT TO_DATE( 
    ( SELECT B FROM OUTPUT_TABLE WHERE A = 'Term Date:' AND REGEXP_LIKE(B,'?[0-9]/?[0-9]/[0-9]{4}') )
, 'mm-dd-yyyy')
AS id FROM DUAL;




</details>


# 答案1
**得分**: 2

```lang-sql
如果您正在使用PL/SQL，那么从Oracle 12开始，您可以使用：

```lang-sql
DECLARE
  v_b output_table.b%TYPE;
BEGIN
  SELECT b
  INTO   v_b
  FROM   OUTPUT_TABLE
  WHERE  A = &#39;Term Date:&#39;
  AND    VALIDATE_CONVERSION(B AS DATE, &#39;mm-dd-yyyy&#39;) = 1;

  DBMS_OUTPUT.PUT_LINE(v_b);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE(&#39;Does not exist or is not a valid date.&#39;);
END;
/

如果您只需要几行SQL：

SELECT ( SELECT TO_DATE(b DEFAULT NULL ON CONVERSION ERROR, 'mm-dd-yyyy')
         FROM   output_table
         WHERE  a = 'Term Date:' ) AS b
FROM   DUAL;

DECLARE
  v_b output_table.b%TYPE;
BEGIN
  SELECT b
  INTO   v_b
  FROM   OUTPUT_TABLE
  WHERE  A = 'Term Date:'
  AND    VALIDATE_CONVERSION(B AS DATE, 'mm-dd-yyyy') = 1;

  DBMS_OUTPUT.PUT_LINE(v_b);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Does not exist or is not a valid date.');
END;
/

SELECT ( SELECT TO_DATE(b DEFAULT NULL ON CONVERSION ERROR, 'mm-dd-yyyy')
         FROM   output_table
         WHERE  a = 'Term Date:' ) AS b
FROM   DUAL;

答案2

得分: 0

以下是翻译好的部分：

SELECT TO_DATE( 
    ( SELECT B FROM OUTPUT_TABLE WHERE A = 'Term Date:' AND REGEXP_LIKE(B,'?[0-9]/?[0-9]/[0-9]{4}') )
, 'mm-dd-yyyy')
AS id FROM DUAL;

Breaking it down, today I learned:

 1. There is a 'Dummy Table' in Oracle called DUAL which will return (null) when there is no record found. But will return the actual value sought when the record is found.
 2. There is a COALESCE that I can use to test for NULL. [IS_NULL is easier to remember, but apparently ORACLE wanted COALESCE instead]. I toyed with this also, but have removed it for the above statement.
 3. There is a REGEX_LIKE that I can use to test for valid values, using regular expressions.
 4. I can sum it all up into another function like TO_DATE().
 5. There is also a DECODE function I tried, but decided not to use. I was very excited to learn this could be used as a TERNARY (IF WHEN ELSE) selector too.

I hope someone finds this useful and/or if I made a mistake or there could be an improvement, feel free to chime in.

SELECT TO_DATE( 
    ( SELECT B FROM OUTPUT_TABLE WHERE A = 'Term Date:' AND REGEXP_LIKE(B,'?[0-9]/?[0-9]/[0-9]{4}') )
, 'mm-dd-yyyy')
AS id FROM DUAL;

答案3

得分: 0

以下是翻译好的部分：

对于那些使用11g或更早版本的人来说，在这些新的增强功能出现之前，旧式方法是编写一个函数来捕获转换错误并返回NULL，而不是引发异常：

CREATE OR REPLACE FUNCTION f_safe_to_date(in_date_string IN varchar2, in_format IN varchar2)
  RETURN date
AS
  var_date date;
BEGIN
  var_date := TO_DATE(in_date_string,in_format);
  RETURN var_date;
EXCEPTION
  WHEN OTHERS THEN
    RETURN NULL;
END;

然后在您的选择语句中使用它：

SELECT f_safe_to_date(B, 'mm-dd-yyyy') term_date FROM OUTPUT_TABLE WHERE A = 'Term Date:';

当然，如果您想减少不必要的执行，您可以添加更多的`REGEXP_LIKE`逻辑，但结果将是相同的。

<details>
<summary>英文:</summary>

For those with 11g or older, the old-school way before these newer enhancements came along is write a function that traps conversion errors and returns `NULL` rather than raising an exception:

    CREATE OR REPLACE FUNCTION f_safe_to_date(in_date_string IN varchar2, in_format IN varchar2)
      RETURN date
    AS
      var_date date;
    BEGIN
      var_date := TO_DATE(in_date_string,in_format);
      RETURN var_date;
    EXCEPTION
      WHEN OTHERS THEN
        RETURN NULL;
    END;

Then use it in your select statement:

    SELECT f_safe_to_date(B, &#39;mm-dd-yyyy&#39;) term_date FROM OUTPUT_TABLE WHERE A = &#39;Term Date:&#39; 

Of course you can add more `REGEXP_LIKE` logic if you want to reduce unnecessary executions, but the result would be the same.

</details>