Search code examples
sqlms-accessenterprise-architectjet

IIF of SWITCH output is limited at length 255

For a custom SQL query in Enterprise Architect we are using IIF or SWITCH, but both seem to have a limitation on their output.

Is there any way to bypass this limitation?

As a simple example below a query with the limitation

select 
switch(true, note) as NoteAfterSwitch,
Cstr(switch(true, note)) as NoteAfterCstrSwitch,
switch(true, cstr(note)) as CstrNoteAfterSwitch,
Cvar(switch(true, note)) as NoteAfterCVarSwitch,
switch(true, cvar(note)) as CvarNoteAfterSwitch,
Note
from t_object as t
where t.object_id = 115

The column NoteAfterSwitch is limited at 255 chars. While the original Note has more then 255 chars. We want to use the original size.

In column NoteAfterCstrSwitch and CstrNoteAfterSwitch we tried using CStr, but also without success.

In column NoteAfterCvarSwitch and CvarNoteAfterSwitch we tried using Cvar, but also without success.

Edit We also tried to use JET 4.0, but unfortunately without success. It resulted in a scrambled output:

enter image description here

Solution

  • Our intern (Kevin) found the solution on a Austrian blog. We have do an implicit cast before the IIF or Switch does it. And that can be done with an empty union:

    SELECT  Note as notes
FROM    t_object AS o
where   1 = 0
union all
select  switch(true, o.note)
FROM    t_object AS o
WHERE   o.Object_ID = 115

    This trick should also work with other implicit unwanted cast actions.