Hi Thomas,

>> what should I expect as result data type of the following
>> expression (4th column):
>>
>>
>> select FIRST 10 "Id", "Vorname", "Name",
>> SUBSTRING(
>> CASE WHEN ("IsMonatUngueltig" = 1) THEN
>> ' '||extract (year from "Geburtsdatum")
>> ELSE
>> CASE when ("IsTagUngueltig" = 1) THEN
>> LPad(extract (month from "Geburtsdatum"),2,'0')||'.'||extract (year from "Geburtsdatum")
>>
>> ELSE
>> LPad(extract (day from "Geburtsdatum"),2,'0')||'.'||LPad(extract (month from "Geburtsdatum"),2,'0')||'.'||extract (year from "Geburtsdatum")
>>
>> END
>> END FROM 1 FOR 10) AS "Geburtsdatum"
>> FROM RMV_PERSON
>>
>>
>> I'd expect varchar(10) or something like that.
>> Actual result is (according to IBExpert) varchar(32765)
>>
>> That would be no problem but there is limit for rowsizes....
>>
>> Any ideas how I can shorten the result inside a view?

> So, have you tried to create a view and see if in both IBExpert and isql
> the resulting field is reported as VARCHAR(32765)?

Yes, and here is the result of flamerobin:

Starting transaction...
Preparing query: select FIRST 10 "Id", "Vorname", "Name",
SUBSTRING(
CASE WHEN ("IsMonatUngueltig" = 1) THEN
' '||extract (year from "Geburtsdatum")
ELSE
CASE when ("IsTagUngueltig" = 1) THEN
LPad(extract (month from "Geburtsdatum"),2,'0')||'.'||extract (year from "Geburtsdatum")

ELSE
LPad(extract (day from "Geburtsdatum"),2,'0')||'.'||LPad(extract (month from "Geburtsdatum"),2,'0')||'.'||extract (year from "Geburtsdatum")

END
END FROM 1 FOR 10) AS "Geburtsdatum"
FROM RMV_PERSON
Prepare time: 0.157s
Field #01: RMV_PERSON.Id Alias:Id Type:BIGINT
Field #02: RMV_PERSON.Vorname Alias:Vorname Type:STRING(84)
Field #03: RMV_PERSON.Name Alias:Name Type:STRING(84)
Field #04: . Alias:Geburtsdatum Type:STRING(32765)


But there is the same result with a much more simple call of LPad:


Starting transaction...
Preparing query: select FIRST 10 LPad("Vorname",10,'*')

FROM RMV_PERSON
Prepare time: 0.031s
Field #01: .LPAD Alias:LPAD Type:STRING(32765)
PLAN (RMV_PERSON NATURAL)


Hm, seems to be a bug?


--
Björn Reimer - RRZE