CODEWITHSUNDEEP
sqlselectdb2
geforce
geforce

Reputation: 53

SQL DB2 Queries Syntax Issue

I currently have a table that I want to modify with select statements.

I want to select a empty row and combine it with another table which can be seen in the code below.

The first select statement is wrong because it needs to only do this for one row not all of them. Or somehow add a empty row without having to add a values to the table

Also the code below throws this error:

SQL0420N Invalid character found in a character string argument of the function "DECFLOAT". SQLSTATE=22018

Code:

select ' ' as RD_E_SUP, \
       ' ' as RD_E_EMP, \
       ' ' as RD_QUOT   \
from RD_SUPERVISOR \
     \
UNION \
       \
select RD_E_SUP,  \
       RD_E_EMP,  \ 
      'null' as RD_QUOT  \ 
from RD_SUPERVISOR   \ 
ORDER BY RD_E_EMP

Upvotes: 0

Views: 1113

Answers (1)

John Bollinger
John Bollinger

Reputation: 180201

Your first subquery is not drawing anything from table RD_SUPERVISOR, so the only reason to select from that table would be if you did want one result for each row of the table. Given that you want exactly one row regardless of the content of table RD_SUPERVISOR, there is a special table you can select from for just that purpose:

select ' ' as RD_E_SUP,
   ' ' as RD_E_EMP,
   ' ' as RD_QUOT
from SYSIBM.SYSDUMMY1

In some other DBMSs, you might achieve the same effect by selecting from "DUAL", or by omitting the FROM clause altogether.

Given that the row produced by the first subquery cannot duplicate any produced by the second, UNION ALL would be a better choice than UNION. If you want to remove duplicates from the results of the second subquery then you should use SELECT DISTINCT in that subquery.

Note, too, that neither ' ' nor 'null' is the same as NULL. This is not necessarily a problem, but if you thought differently then your query does not mean what you thought it means.

The SQL0420N error probably means that DB2 is trying (and failing) to convert one of your blank strings (' ') to type DECFLOAT. It would do this if it chose that type for column RD_E_SUP or RD_E_EMP of the overall result, likely as a consequence of that being the type of that column in the second subquery. To work around that you should ensure via explicit type conversions in one or both select lists that the data types of corresponding columns selected by the subqueries are the same.

Upvotes: 2

Related Questions