BIRT column aliases and report designer

I'm using BIRT 2.2.1.r221_v20070924 Build <2.2.0.v20070924-1550 on
Eclipse 3.3.0

When designing a query, I'm using aliases on various columns in order to
distinguish them.

select wf.phone as work_fax, wp.phone as work_phone
from contact
left join phone as wf on wf.contact_id = c.id and wf.location = 'work and
wf.phone_type = 'Fax'
left join phone as wp on wp.contact_id = c.id and wp.location = 'work' and
wf.phone_type = 'Phone'

However, in the "Edit Data Set" window I find that the column aliases
aren't preserved. Instead I see "name" allocated based on the column
order, so I get phone and phone_2 (for the above case).

This is confusing, because instead of being able to refer to the defined
aliases I have to go back and count columns.

I hoped that I could use the "Alias" feature in the "Edit Data Set" window
to achieve what I normally do via SELECT foo AS bar, but it doesn't work
as expected either. I added aliases for each of my (seven) phone
locations, then clicked OK, and then clicked to edit the data set again;
when I viewed the "Output Columns" only two had aliases, and those two had
been assigned an alias which previously pointed to another column.

I'm hoping that I'm doing something horribly wrong because I'm new to BIRT
- is there a magic syntax for making aliases behave as I'd expect?

Also ... If I edit the query, and change the number of columns before one
of the columns with an automatically assigned number suffix (say, email_16
becomes email_17 with introduction of a column at 12), then I need to
manually update each entry in the XML source to change the reference for
email_16.

Any input appreciated.

Hi,

I haven't found your issue in 2.2.1.r221_v20070924 build. The column name
is as the alias which defined in SQL but not a name with automatically
suffix. If the column name are same with out define alias in SQL, it will
add a number suffix and the number is will be the index of the columns'
sequence. So would you like to try another build to reproduce it?

Thanks

Tianli

Thanks, Tianli. I appreciate your reply.

I believe I'm defining the column aliases by using syntax like,

SELECT wf.phone AS work_fax, wp.phone AS work_phone
FROM contact
LEFT JOIN phone AS wf ON wf.contact_id = c.id AND wf.location = 'work AND
wf.phone_type = 'Fax'
LEFT JOIN phone AS wp ON wp.contact_id = c.id AND wp.location = 'work' AND
wf.phone_type = 'Phone'


You said,

> If the column name are same with out define alias in SQL, it will add a
number suffix and the number is will be the index of the columns' sequence.

Is there some other syntax I should be using, rather than the above?

I'm downloading 2.2.2 now and will report my results.

Chris

We just recently ran into this issue as well. It turned out to be an
issue with the MySQL driver, not with BIRT.

http://bugs.mysql.com/bug.php?id=33966

The workaround is to add a parameter ("useOldAliasMetadataBehavior") to
the data source's connection URL.

For example,

jdbc:mysql://localhost/databaseName?useOldAliasMetadataBehavior=true


Hope that helps.

Justin




chris burgess wrote:
> Thanks, Tianli. I appreciate your reply.
>
> I believe I'm defining the column aliases by using syntax like,
>
> SELECT wf.phone AS work_fax, wp.phone AS work_phone
> FROM contact
> LEFT JOIN phone AS wf ON wf.contact_id = c.id AND wf.location = 'work
> AND wf.phone_type = 'Fax'
> LEFT JOIN phone AS wp ON wp.contact_id = c.id AND wp.location = 'work'
> AND wf.phone_type = 'Phone'
>
>
> You said,
>
>> If the column name are same with out define alias in SQL, it will add a
> number suffix and the number is will be the index of the columns' sequence.
>
> Is there some other syntax I should be using, rather than the above?
>
> I'm downloading 2.2.2 now and will report my results.
>
>