crosstab queries

[Tim McGuire-2]

Hi,

I'm wondering how to approach crosstab queries with the entity engine.
That is, I want to turn data from a row into a column heading.

if the entity is something like:

salesperson(char 20), month(number), total(number)

and I want a query like

SELECT salesperson,
(SELECT SUM(total) from sales where month = 1) as 'JAN',
(SELECT SUM(total) from sales where month = 2) as 'FEB',
.
.
.
.
.
.
(SELECT SUM(total) from sales where month = 12) as 'DEC',
(SELECT SUM(total) as 'GRAND Total'

reading the entity engine cookbook, I see a way to to SUMs and other
functions, but not sub-selects.

and also I have a case where I would have to get the column names
themselves using a query.  For this, I would be limited to JDBC, I take
it?




Tim McGuire
Integral Business Solutions
phone: [phone]

[Daniel Martínez-4]

Hi Tim,

Tim McGuire escribió:

> Hi,
>
> I'm wondering how to approach crosstab queries with the entity engine.
> That is, I want to turn data from a row into a column heading.
>
> if the entity is something like:
>
> salesperson(char 20), month(number), total(number)
>
> and I want a query like
>
> SELECT salesperson,
> (SELECT SUM(total) from sales where month = 1) as 'JAN',
> (SELECT SUM(total) from sales where month = 2) as 'FEB',
> .
> .
> .
> .
> .
> .
> (SELECT SUM(total) from sales where month = 12) as 'DEC',
> (SELECT SUM(total) as 'GRAND Total'
>
> reading the entity engine cookbook, I see a way to to SUMs and other
> functions, but not sub-selects.
>  

AFAIK encapsulating selects is only possible through use of view-entities.
> and also I have a case where I would have to get the column names
> themselves using a query.  For this, I would be limited to JDBC, I take
> it?
>
>  
See class ModelEntity (getAllFieldNames) for getting info about entities
structure. Of course this names are in Ofbiz format (partyId), not
database format (party_id)

--
Daniel

[Jacques Le Roux]

Did you try to use EntityWhereString ?

Jacques

----- Message d'origine -----
De : "Tim McGuire" <[hidden email]>
À : <[hidden email]>
Envoyé : jeudi 12 juillet 2007 06:50
Objet : crosstab queries

> Hi,
>
> I'm wondering how to approach crosstab queries with the entity engine.
> That is, I want to turn data from a row into a column heading.
>
> if the entity is something like:
>
> salesperson(char 20), month(number), total(number)
>
> and I want a query like
>
> SELECT salesperson,
> (SELECT SUM(total) from sales where month = 1) as 'JAN',
> (SELECT SUM(total) from sales where month = 2) as 'FEB',
> .
> .
> .
> .
> .
> .
> (SELECT SUM(total) from sales where month = 12) as 'DEC',
> (SELECT SUM(total) as 'GRAND Total'
>
> reading the entity engine cookbook, I see a way to to SUMs and other
> functions, but not sub-selects.
>
> and also I have a case where I would have to get the column names
> themselves using a query.  For this, I would be limited to JDBC, I take
> it?
>
>
>
>
> Tim McGuire
> Integral Business Solutions
> phone: [phone]

[David E Jones]

Or even EntityConditionSubSelect... and there are probably even some examples of it.

Of course, in most cases (I think maybe even this one, though I haven't gone through the exercise) queries like this can be simplified to avoid needing to use sub-selects.

-David


Jacques Le Roux wrote:

> Did you try to use EntityWhereString ?
>
> Jacques
>
> ----- Message d'origine -----
> De : "Tim McGuire" <[hidden email]>
> À : <[hidden email]>
> Envoyé : jeudi 12 juillet 2007 06:50
> Objet : crosstab queries
>
>
>> Hi,
>>
>> I'm wondering how to approach crosstab queries with the entity engine.
>> That is, I want to turn data from a row into a column heading.
>>
>> if the entity is something like:
>>
>> salesperson(char 20), month(number), total(number)
>>
>> and I want a query like
>>
>> SELECT salesperson,
>> (SELECT SUM(total) from sales where month = 1) as 'JAN',
>> (SELECT SUM(total) from sales where month = 2) as 'FEB',
>> .
>> .
>> .
>> .
>> .
>> .
>> (SELECT SUM(total) from sales where month = 12) as 'DEC',
>> (SELECT SUM(total) as 'GRAND Total'
>>
>> reading the entity engine cookbook, I see a way to to SUMs and other
>> functions, but not sub-selects.
>>
>> and also I have a case where I would have to get the column names
>> themselves using a query.  For this, I would be limited to JDBC, I take
>> it?
>>
>>
>>
>>
>> Tim McGuire
>> Integral Business Solutions
>> phone: [phone]
>