Kbase P125130: SQL-92: Error (7498) using NULLIF Function in conjunction with the UNION SET OPERATOR.
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
26/07/2007 |
|
Status: Unverified
SYMPTOM(s):
SQL-92: Error (7498) using NULLIF Function in conjunction with the UNION SET OPERATOR.
Invalid number string (7498)
Executing the following SQL-92 query:
SELECT
'stringOne' as 'UNIQUE_ID',
'A' as 'VALUE_STRING',
1.50 as 'VALUE_NUMERIC',
TO_DATE('02-02-2007') as 'VALUE_DATE'
FROM
(SELECT ID FROM SYSPROGRESS.SYSTABLES WHERE TBL = 'Item') dual
UNION ALL
SELECT
'stringTwo' as 'UNIQUE_ID',
NULLIF(-1,-1) as 'VALUE_STRING',
CAST(NULL as FLOAT) as 'VALUE_NUMERIC',
CAST(NULL as DATE) as 'VALUE_DATE'
FROM
(SELECT ID FROM SYSPROGRESS.SYSTABLES WHERE TBL = 'Item') dual
CAUSE:
Syntax error. Specifically, the standard SQL-92 rules for a UNION require that the corresponding columns in each SELECT statement have the same, or compatible, data types. The first parameter of the NULLIF function determine the data type of the the returned value. In this case it defines the data type of the second element of the second set as INTEGER which is not compatible with the corresponding element of the first set which is CHARACTER. Hence the error.
FIX:
Ensure that the corresponding elements of all the sets of a UNION have the same or compatible data types and remember that the data type of the value returned by the NULLIF function is the same as the data type of its first parameter. For example, to changing the NULLIF expression in the above to the following corrects this syntax error:
NULLIF('-1',-1) as 'VALUE_STRING',