Problem with Complex SQL Queries

[2006 IR Open Discussion]

By: Roberto Rios - bobrivers

Problem with Complex SQL Queries

2002-02-22 13:32

Hi,

 

Now, I am trying to do a more complex report.

 

The first thing that I did was to build a query that holds all the information that I need.

 

The problem is that I am using an ALIAS to the database columns. I have to do that because I have columns with the same name over diferent tables.

 

The I compile the XML, I got the following errors:

 

C:jdk1_3_1Report>java JasperReportsTestApp -Tcompile -Frel_trafego_.xml

 

dori.jasper.engine.JRException: Errors were encountered when compiling report design:

 

relatorio_trafego.java:27: ';' expected.

private JRField field_ft.nom_bairro = null;

^

relatorio_trafego.java:28: ';' expected.

private JRField field_tt.dsc_categoria = null;

^

relatorio_trafego.java:29: Duplicate variable declaration: JRField field_tt was

 

JRField field_tt

private JRField field_tt.num_ramal = null;

^

relatorio_trafego.java:29: ';' expected.

private JRField field_tt.num_ramal = null;

^

relatorio_trafego.java:30: ';' expected.

private JRField field_nf.val_nota_fiscal = null;

 

...

 

at dori.jasper.engine.JRCompiler.compileReport(JRCompiler.java:78)

at dori.jasper.engine.JasperManager.compileReportToFile(JasperManager.ja

va:857)

at dori.jasper.engine.JasperManager.compileReportToFile(JasperManager.ja

va:829)

at JasperReportsTestApp.main(JasperReportsTestApp.java:80)

 

So, I think that JasperReports is not dealing well with table aliases.

 

The query that I did is :

 

<queryString><![CDATA[

select ft.nom_razao_social,ft.dsc_endereco,ft.dsc_complemento,ft.nom_bairro,ft.nom_cidade,ft.nom_estado,ft.num_cep,ft.num_cnpj,ft.num_ie, ft.num_im,

nf.val_nota_fiscal,nf.num_nota_fiscal,nf.dat_emissao,nf.val_icms,nf.val_pis, nf.val_cofins,

tt.cod_tarifador_tabs,tt.num_ramal,tt.dat_ligacao,tt.hor_ligacao, tt.num_duracao,tt.num_discado,tt.txt_localidade,tt.val_custo,tt.nom_razao_social, tt.dsc_categoria,tt.nom_operadora,tt.val_percentual_imposto,tt.txt_uf_destino,tt.num_chave,tt.hor_tarifa

from faturamento ft, nota_fiscal nf, tarifador_tabs tt, unidade un

where ft.nom_razao_social = tt.nom_razao_social and ft.cod_faturamento = nf.cod_faturamento and

un.nom_razao_social = ft.nom_razao_social and

upper(un.nom_fantasia) like '%empree%' and

tt.dat_ligacao between '2001-12-01' and '2001-12-31'

order by ft.nom_razao_social, tt.nom_operadora, tt.dsc_categoria, tt.dat_ligacao, tt.hor_ligacao

]]></queryString>

 

I could make a view to this select, and then change the column names, but I am using MySQL, and I think that MySQL doesn't have view...

 

Any help ?

 

Roberto - Brazil

 

 

 

 

By: Teodor Danciu - teodord

RE: Problem with Complex SQL Queries

2002-02-24 04:01

 

Hi,

 

Do not use '.' character in parameter,

field or variable names.

 

The name of a field should match exactly the

name of the corresponding column in the ResultSet.

Here is what you should do:

 

Use aliases for all your columns that have the

same name in different tables of the same join.

You can do this by using the "AS" keyword to

change the name of the column in the ResultSet.

 

I did that for the field "tt.num_ramal"

to which I provided the alias "tt_num_ramal"

in your query below (don't use '.' in aliases):

 

<queryString><![CDATA[

select

ft.nom_razao_social, ft.dsc_endereco,

ft.dsc_complemento, ft.nom_bairro,

ft.nom_cidade, ft.nom_estado,

ft.num_cep, ft.num_cnpj,

ft.num_ie, ft.num_im,

nf.val_nota_fiscal, nf.num_nota_fiscal,

nf.dat_emissao, nf.val_icms,

nf.val_pis, nf.val_cofins,

tt.cod_tarifador_tabs,

 

tt.num_ramal AS tt_num_ramal,

 

tt.dat_ligacao, tt.hor_ligacao,

tt.num_duracao, tt.num_discado,

tt.txt_localidade, tt.val_custo,

tt.nom_razao_social, tt.dsc_categoria,

tt.nom_operadora, tt.val_percentual_imposto,

tt.txt_uf_destino, tt.num_chave,

tt.hor_tarifa

from

faturamento ft, nota_fiscal nf,

tarifador_tabs tt, unidade un

where

ft.nom_razao_social = tt.nom_razao_social and

ft.cod_faturamento = nf.cod_faturamento and

un.nom_razao_social = ft.nom_razao_social and

upper(un.nom_fantasia) like '%empree%' and

tt.dat_ligacao between '2001-12-01' and '2001-12-31'

order by

ft.nom_razao_social, tt.nom_operadora,

tt.dsc_categoria, tt.dat_ligacao, tt.hor_ligacao

]]></queryString>

 

The name of the corresponding report field

that will store the values of the "tt.num_ramal"

column should be the same as the given column

alias : "tt_num_ramal".

 

I hope it helps!

Good luck!

 

P.S. You can also use the mailing list to post questions.

 

 

 

 

 

By: Daren O - rckrll106

RE: Problem with Complex SQL Queries

2003-09-19 10:11

Teodor , the database we are working with is a Unify Dataserver 7 and it does not support aliases with AS. I realize that it is odd for a database to not offer aliases with AS, however that is what we have to work with. <br><br>Therefore, the dot notation is preventing us from using jasper at this point. <br><br>Is there any way this can be overcome without aliases? <br><br> Is there is a class or two and can be changed? If so, we are willing to look into the source code to see if this could be overcome. <br> thanks for your help, rckrll

 

 

 

 

By: Teodor Danciu - teodord

RE: Problem with Complex SQL Queries

2003-09-27 02:20

 

Hi,

 

The problem comes from the fact that the build-in

JRResultSetDataSource uses the name of the report

field to retrieve the column value from the

ResultSet.

 

You could implement a custom JRDataSource that

would use the report field description to retrieve

the value from the ResultSet, as the field

description accepts dots and other special

characters.

 

But this would olso mean that you have to execute

the query outside JasperReports, and wrap the

ResultSet into this JRDataSource instance before

passing it to the report filling engine.

This is because at least for the moment, the engine

uses internally its own version of the

JRResultSetDataSource, the one that has the

above naming constraints.

 

I hope this helps.

Teodor