SQL DB2 Queries Syntax Issue

Question

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

Why do you have the \ at the end of each line? That is definitely invalid (standard) SQL. — user330315
– user330315, Commented Apr 12, 2015 at 20:43

John Bollinger · Accepted Answer · 2015-04-12 18:17:49Z

2

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.

answered Apr 12, 2015 at 18:17

John Bollinger

191k11 gold badges102 silver badges206 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

geforce Over a year ago

Would it be possbile to print a new line in SQL db2 that is treated like a empty row?

geforce Over a year ago

Also, is there a way I can make the table print 'null' instead of '-'? that is why I am trying to use 'null' which throws an error

Clockwork-Muse Over a year ago

@GeForce - printing 'null' would be a formatting/display issue, and doesn't really belong in the database (rather, in your reporting application) - for that matter, it's a technical/computer term, and you'd be better off choosing whatever your users/clients/customers would expect for the field (like 'N/A').

user2338816 Over a year ago

@geforce ...print a new line in SQL... SQL doesn't "print". Printing isn't a database function. However, any number of SQL clients and management products include a "Report Writer" feature that may provide some printing support. In order to answer, you need to tell us what you're using. What platform is DB2 on? What release is it? What client are you using?

Collectives™ on Stack Overflow

SQL DB2 Queries Syntax Issue

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related