empty string versus space within a definition of a materialized query table

[toralf]

I'm wondering, why I can't re-use the same SELECT definitition to
create a MQT if the SELECT statement has an empty string instead a space
as the default value for a column (DB2/6000 8.2.9 AIX
5.3).

Both the sample script and the output are attached.

--
#!/bin/sh
#

db2 -v connect to TLMA 1>/dev/null

db2 -v "create table EXT.TORALF (ID int)"

TORALF_SPACE="select ID, ' ' as ALIAS from EXT.TORALF"
TORALF_EMPTY="select ID, '' as ALIAS from EXT.TORALF"

for i in TORALF_SPACE TORALF_EMPTY
do
echo
echo "$i-------------------------------------------------------------------------------"

echo
eval $(echo db2 -v "export to ./tmp of del modified by nochardel coldelx09 \$$i")

echo
db2 -v "drop table EXT.$i"

echo
eval $(echo db2 -v "create table EXT.$i as \( \$$i \) data initially deferred refresh deferred")
done

db2 -v "drop table EXT.TORALF"

db2 -v connect reset 1>/dev/null

exit 0



--
[***@detlmtst01] /home/tfoerste> ./quote.sh
create table EXT.TORALF (ID int)
DB20000I The SQL command completed successfully.


TORALF_SPACE-------------------------------------------------------------------------------

export to ./tmp of del modified by nochardel coldelx09 select ID, ' ' as ALIAS from EXT.TORALF
SQL3104N The Export utility is beginning to export data to file "./tmp".

SQL3105N The Export utility has finished exporting "0" rows.


Number of rows exported: 0



drop table EXT.TORALF_SPACE
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0204N "EXT.TORALF_SPACE" is an undefined name. SQLSTATE=42704


create table EXT.TORALF_SPACE as ( select ID, ' ' as ALIAS from EXT.TORALF ) data initially deferred refresh deferred
DB20000I The SQL command completed successfully.


TORALF_EMPTY-------------------------------------------------------------------------------

export to ./tmp of del modified by nochardel coldelx09 select ID, '' as ALIAS from EXT.TORALF
SQL3104N The Export utility is beginning to export data to file "./tmp".

SQL3105N The Export utility has finished exporting "0" rows.


Number of rows exported: 0



drop table EXT.TORALF_EMPTY
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0204N "EXT.TORALF_EMPTY" is an undefined name. SQLSTATE=42704


create table EXT.TORALF_EMPTY as ( select ID, '' as ALIAS from EXT.TORALF ) data initially deferred refresh deferred
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0604N The length, precision, or scale attribute for column, distinct type,
structured type, attribute of structured type, function, or type mapping
"ALIAS" is not valid. SQLSTATE=42611

drop table EXT.TORALF
DB20000I The SQL command completed successfully.

[Serge Rielau]

Post by toralf
create table EXT.TORALF_EMPTY as ( select ID, '' as ALIAS from EXT.TORALF ) data initially deferred refresh deferred
DB21034E The command was processed as an SQL statement because it was not a
SQL0604N The length, precision, or scale attribute for column, distinct type,
structured type, attribute of structured type, function, or type mapping
"ALIAS" is not valid. SQLSTATE=42611

Ah... this is very philosophical....
The long and the short of it is that VARCHAR(0) columns are not allowed
in tables.
Now one can point out that CREATE VIEW v(c1) AS VALUES ('');
does just that... as I say it's philosophical...

There is no relation to MQT here btw.

Cheers
Serge