You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ofbiz.apache.org by Tim McGuire <tm...@go-integral.com> on 2007/07/12 06:50:12 UTC

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: (651) 259-1008

Re: crosstab queries

Posted by Daniel Martínez <da...@paradisosistemas.es>.
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

Re: crosstab queries

Posted by David E Jones <jo...@hotwaxmedia.com>.
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" <tm...@go-integral.com>
> À : <us...@ofbiz.apache.org>
> 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: (651) 259-1008
>

Re: crosstab queries

Posted by Jacques Le Roux <ja...@les7arts.com>.
Did you try to use EntityWhereString ?

Jacques

----- Message d'origine ----- 
De : "Tim McGuire" <tm...@go-integral.com>
À : <us...@ofbiz.apache.org>
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: (651) 259-1008