Result formats of two equivalent SQL statements differ

Two variants of a SQL statement with a CASE clause show different date formats depending on the order in which the cases appear. In some cases an error may result from one variant but not the other.

May 19, 2016
Knowledge

Title

URL Name

Result-formats-of-two-equivalent-SQL-statements-differ

Article Number

000185171

Environment

Product: OpenEdge Version: 10.x, 11.x OS: All supported platforms Other: JDBC

Question/Problem Description

Two variants of a SQL statement with a CASE clause show different date formats depending on the order in which the cases appear.

Dates may appear in ISO format or month/day/year format. In the month/day/year format, only the first two digits of the four-digit year are displayed.

Query 1

SELECT

CASE

WHEN Order.OrderDate IS NOT NULL THEN Order.OrderDate
ELSE '01/01/01'
END AS 'ReceiptDate'

FROM PUB.Order
WHERE Order.CustNum = 1
WITH (NOLOCK)

Output 1

2001-01-01
2001-01-01
1997-11-21
1998-03-02

Query 2

SELECT

CASE

WHEN Order.OrderDate IS NULL THEN '01/01/01'
ELSE Order.OrderDate
END AS 'ReceiptDate'

FROM PUB.Order
WHERE Order.CustNum = 1
WITH (NOLOCK)

Output 2

01/01/01
01/01/01
05/17/20
03/02/19

In some cases, Query 2 may raise error "Software caused connection abort: recv failed" when issued through the JDBC driver.

Steps to Reproduce

Clarifying Information

Error Message

Software caused connection abort: recv failed

Defect Number

Enhancement Number

Cause

The column in the CASE statement is of type DATE, but the constant is actually of type VARCHAR.

The ANSI SQL standard would require that both queries raise syntax errors and not run, because the result datatypes of the WHEN and ELSE branches are not the same. In the standard, DATE and VARCHAR (or CHAR) are not compatible, whereas DATE and TIMESTAMP are compatible

OpenEdge SQL follows a super-set of the ANSI standard, for backward compatibility with Progress 9 and early OE 10 releases. If the ANSI SQL standard rules cause a syntax error, OpenEdge SQL tries to use the result datatype of the first WHEN as the result of the CASE, as long as the other WHEN/ELSE values can be converted to the first type.

In Query 1, the first WHEN value is of type Date, so the results are displayed as ISO dates (the default format).

In Query 2, the first WHEN value is a constant string. This string is actually of type VARCHAR, and must be converted to DATE. The DATE value after conversion is ten characters long, so the last two characters are truncated in the display.

The exact cause of the "Software caused connection abort: recv failed" is not known at this time. it may be related to a problem in the data type conversion.

Resolution

As a best practice, write queries so that every result of the CASE branches (WHEN, ELSE) are the same datatype.
For example, rewrite Query 1 as:

SELECT

CASE

WHEN Order.OrderDate IS NOT NULL THEN Order.OrderDate
ELSE CAST('01/01/01' AS DATE)
END AS 'ReceiptDate'

FROM PUB.Order
WHERE Order.CustNum = 1
WITH (NOLOCK)

Rewrite Query 2 as:

SELECT

CASE

WHEN Order.OrderDate IS NULL THEN CAST('01/01/01' AS DATE)
ELSE Order.OrderDate
END AS 'ReceiptDate'

FROM PUB.Order

As an alternative, always put the Date result in the first WHEN branch as seen in Query 1. With this form, OpenEdge SQL uses the first datatype as the result datatype for the CASE and converts all the other WHEN/ELSE result values into the first datatype.

Workaround

Notes

Keyword Phrase

Last Modified Date

5/19/2016 11:33 PM

Result formats of two equivalent SQL statements differ

Files(0)

Was this article helpful?

Was this article helpful?

Result formats of two equivalent SQL statements differ

Information

Related Articles