Periods in column names

[Dan Gradl]

I have a fairly complex query in MSSQL containing a PIVOT... resulting
in row data becoming the column names.  This in itself is not a
problem except that those rows may contain a period.   This is mapped
to HashMap and should normally result in a List of HashMaps for each
row with the column name as key and an Integer value.  However, when
the automatic mapping runs across this period (in
org.apache.ibatis.reflection.property.PropertyTokenizer), it ends up
treating this as children and I get an entry in the Map with the
column name up to the period and then my value is a Map instead of an
integer.

Other than completely reworking this query, is there a way to prevent
it from tokenizing the column name?

Simplified Example:
So the normal query is like
select itemName, qty from items
|   itemName     | qty                |
|   item a           | 10                 |
|   item b.c        | 20                 |

The pivot makes it
|  item a           | item b.c         |
|  10                 |  20                |

resultType="java.util.HashMap"

Will be a map with
| item a            | 10                  |
| item b            | MAP: {c = 20} |

[Jeff Butler]

Hi Dan,

Can you add "AS" phrases to the query to rename the columns (select
col1 as foo, col2 as bar...)?

Jeff Butler

On Mon, Apr 30, 2012 at 10:39 AM, Dan Gradl <[hidden email]> wrote:

> I have a fairly complex query in MSSQL containing a PIVOT... resulting
> in row data becoming the column names.  This in itself is not a
> problem except that those rows may contain a period.   This is mapped
> to HashMap and should normally result in a List of HashMaps for each
> row with the column name as key and an Integer value.  However, when
> the automatic mapping runs across this period (in
> org.apache.ibatis.reflection.property.PropertyTokenizer), it ends up
> treating this as children and I get an entry in the Map with the
> column name up to the period and then my value is a Map instead of an
> integer.
>
> Other than completely reworking this query, is there a way to prevent
> it from tokenizing the column name?
>
> Simplified Example:
> So the normal query is like
> select itemName, qty from items
> |   itemName     | qty                |
> |   item a           | 10                 |
> |   item b.c        | 20                 |
>
> The pivot makes it
> |  item a           | item b.c         |
> |  10                 |  20                |
>
> resultType="java.util.HashMap"
>
> Will be a map with
> | item a            | 10                  |
> | item b            | MAP: {c = 20} |
>
>

[Dan Gradl]

Hey Jeff,

Yes I can add AS, however, the actual set of data returned can vary..
it might return item a, item b.c this time, depending on search
criteria the next time it will be item d, item e.f.      So the AS
cannot be static.     Yeah, there are some different things I can do
with the query to avoid the item numbers in column headings... however
this query was already in place with all the code around it expecting
it to come out this way.   I could restructure the query to use the
ItemId (unique id on the row) instead, but then I'd have to alter code
elsewhere to translate the ItemId to an ItemNumber.    Admittedly the
query is a strange edge case, and probably should be done some other
way... but I was hoping to avoid a more pervasive change.


On Apr 30, 12:51 pm, Jeff Butler <[hidden email]> wrote:

> Hi Dan,
>
> Can you add "AS" phrases to the query to rename the columns (select
> col1 as foo, col2 as bar...)?
>
> Jeff Butler
>
>
>
>
>
>
>
> On Mon, Apr 30, 2012 at 10:39 AM, Dan Gradl <[hidden email]> wrote:
> > I have a fairly complex query in MSSQL containing a PIVOT... resulting
> > in row data becoming the column names.  This in itself is not a
> > problem except that those rows may contain a period.   This is mapped
> > to HashMap and should normally result in a List of HashMaps for each
> > row with the column name as key and an Integer value.  However, when
> > the automatic mapping runs across this period (in
> > org.apache.ibatis.reflection.property.PropertyTokenizer), it ends up
> > treating this as children and I get an entry in the Map with the
> > column name up to the period and then my value is a Map instead of an
> > integer.
>
> > Other than completely reworking this query, is there a way to prevent
> > it from tokenizing the column name?
>
> > Simplified Example:
> > So the normal query is like
> > select itemName, qty from items
> > |   itemName     | qty                |
> > |   item a           | 10                 |
> > |   item b.c        | 20                 |
>
> > The pivot makes it
> > |  item a           | item b.c         |
> > |  10                 |  20                |
>
> > resultType="java.util.HashMap"
>
> > Will be a map with
> > | item a            | 10                  |
> > | item b            | MAP: {c = 20} |