CODEWITHSUNDEEP
jpacrosstabrapidclipse
Schwabenheinz
Schwabenheinz

Reputation: 143

How to generate a crosstab with RapidClipse?

I would like to generate a crosstab inside RapidClipse using existing Entity/ DAO. The result should be loaded into a XdevTable/ XdevGrid

Would this be possible, if yes how?

in an existing DAO I placed following JPA-SQL:

findAllForPivot(String pjahr, String pkontos)
    {
     select 
     l1Id,
     l1GroupName,
     l2Id,
     l2GroupName,
     sum( case when (monat = 1) then dbetrag end ) AS `jan`,
     sum( case when monat = 2 then dbetrag end ) AS `feb`,
     sum( case when monat = 3 then dbetrag end ) AS `mrz`,
     sum( case when monat = 4 then dbetrag end ) AS `apr`,
     sum( case when monat = 5 then dbetrag end ) AS `mai`,
     sum( case when monat = 6 then dbetrag end ) AS `jun`,
     sum( case when monat = 7 then dbetrag end ) AS `jul`,
     sum( case when monat = 8 then dbetrag end ) AS `aug`,
     sum( case when monat = 9 then dbetrag end ) AS `sep`,
     sum( case when monat = 10 then dbetrag end ) AS `okt`,
     sum( case when monat = 11 then dbetrag end ) AS `nov`,
     sum( case when monat = 12 then dbetrag end ) AS `dez`
     from VHibCrosUmsGiroOpaOmaReport
     where  jahr =:pjahr and kontoId in (:pkontos)
     group by l1Id, l1GroupName, l2Id, l2GroupName;
    }

each line has one ore more errors: First field after the select generates: 

the field can not be resolved

the first sum line generates (like all other sum-lines):

Multiple markers at this line - `jan` cannot be resolved to a type - case cannot be resolved - missing '(' at 
 'dbetrag' - missing RULE_ID at ',' - dbetrag cannot be resolved to a type - mismatched input '=' expecting 
 RULE_ID - monat cannot be resolved to a type - missing ')' at 'when' - missing '(' at '`jan`' - mismatched input 
 'then' expecting '{' - mismatched input 'AS' expecting '{'

... The select itself works fine if i use it in SQL-interface.

The fields are also exactly like defined and if I use "findAll" I'll get exactly all the fields.

At least I would like to have the crosstab result in a XDevTable.
After it should be possible to click on a result cell, to open a detail page.

Where is my error/ whats wrong?

Thank you in advance rgds OpaHeinz

Upvotes: 1

Views: 65

Answers (0)

Related Questions