Hello.

I have the following query.

SELECT
 SUM(InvNet) AS Net,
 Extract(Year FROM InvoiceDate) As Year,
 Extract(Month From InvoiceDate) AS Month
FROM
 ARInv
GROUP BY Year, Month

This gives me 3 columns.

I want to use the data in a QG Chart and in order for the user to be able to
show the results from different years side by side I need the value NET to
comprise a number of columns depending upon the Year of InvoiceDate.

So, I need the second line to do a SUM to a column named after the Year
value of InvoiceDate.  The number of columns would be the same as the number
of different years in InvoiceDate.  I still want the Grouping to be by
Month. (The Year is not important if it is possible)

Output would be something like this

Month        2002                 2003                 2004
2005          etc
1                £XXX               £XXX               £XXX
2                £XXX               £XXX               £XXX
3
4
5
6
7
8
9
10
11
12

How can I achieve this?

Thanks.

Rob.

"Robert Rowlands" <rob@notathome.com> wrote in message
news:29FABDA5-C185-4A09-BECC-0AFE2CC125C4@news.elevatesoft.com...
> Hello.
>
> I have the following query.

SELECT SUM(IF (EXTRACT(YEAR FROM INVOICEDATE) = 2002 THEN INVNET ELSE 0)) AS
TOT2002,
SUM(IF (EXTRACT(YEAR FROM INVOICEDATE) = 2003 THEN INVNET ELSE 0)) AS
TOT2003,
EXTRACT (MONTH FORM INVOICEDATE) AS MONTH
GROUP BY 3;

You should be able to use parameters instead of hardcoded year numbers.

Robert

>
> SELECT
>  SUM(InvNet) AS Net,
>  Extract(Year FROM InvoiceDate) As Year,
>  Extract(Month From InvoiceDate) AS Month
> FROM
>  ARInv
> GROUP BY Year, Month
>
> This gives me 3 columns.
>
> I want to use the data in a QG Chart and in order for the user to be able
> to show the results from different years side by side I need the value NET
> to comprise a number of columns depending upon the Year of InvoiceDate.
>
> So, I need the second line to do a SUM to a column named after the Year
> value of InvoiceDate.  The number of columns would be the same as the
> number of different years in InvoiceDate.  I still want the Grouping to be
> by Month. (The Year is not important if it is possible)
>
> Output would be something like this
>
> Month        2002                 2003                 2004 2005
> etc
> 1                £XXX               £XXX               £XXX
> 2                £XXX               £XXX               £XXX
> 3
> 4
> 5
> 6
> 7
> 8
> 9
> 10
> 11
> 12
>
> How can I achieve this?
>
> Thanks.
>
> Rob.
>

Thanks Robert

I need to spend some time and learn SQL properly.

Now, I'm really pushing my luck here, but is there any way I can check
within the SQL what years are available in the InvoiceDate Field ands then
apply your code?

To be fair, I guess I could check what dates are available in the table and
then create the SQL in the form's OnCreate event.

Rob.



"Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote in message
news:92072FA6-ABEE-47D6-80F6-A0034A72E2F9@news.elevatesoft.com...
>
> "Robert Rowlands" <rob@notathome.com> wrote in message
> news:29FABDA5-C185-4A09-BECC-0AFE2CC125C4@news.elevatesoft.com...
>> Hello.
>>
>> I have the following query.
>
> SELECT SUM(IF (EXTRACT(YEAR FROM INVOICEDATE) = 2002 THEN INVNET ELSE 0))
> AS TOT2002,
> SUM(IF (EXTRACT(YEAR FROM INVOICEDATE) = 2003 THEN INVNET ELSE 0)) AS
> TOT2003,
> EXTRACT (MONTH FORM INVOICEDATE) AS MONTH
> GROUP BY 3;
>
> You should be able to use parameters instead of hardcoded year numbers.
>
> Robert
>
>>
>> SELECT
>>  SUM(InvNet) AS Net,
>>  Extract(Year FROM InvoiceDate) As Year,
>>  Extract(Month From InvoiceDate) AS Month
>> FROM
>>  ARInv
>> GROUP BY Year, Month
>>
>> This gives me 3 columns.
>>
>> I want to use the data in a QG Chart and in order for the user to be able
>> to show the results from different years side by side I need the value
>> NET to comprise a number of columns depending upon the Year of
>> InvoiceDate.
>>
>> So, I need the second line to do a SUM to a column named after the Year
>> value of InvoiceDate.  The number of columns would be the same as the
>> number of different years in InvoiceDate.  I still want the Grouping to
>> be by Month. (The Year is not important if it is possible)
>>
>> Output would be something like this
>>
>> Month        2002                 2003                 2004 2005 etc
>> 1                £XXX               £XXX               £XXX
>> 2                £XXX               £XXX               £XXX
>> 3
>> 4
>> 5
>> 6
>> 7
>> 8
>> 9
>> 10
>> 11
>> 12
>>
>> How can I achieve this?
>>
>> Thanks.
>>
>> Rob.
>>
>
>

Robert,

<< You should be able to use parameters instead of hardcoded year numbers.
>>

Unfortunately you cannot use parameters in SELECT expressions.

--
Tim Young
Elevate Software
www.elevatesoft.com

Robert,

<< To be fair, I guess I could check what dates are available in the table
and then create the SQL in the form's OnCreate event. >>

That's really the only way to do it dynamically since the content of the
SELECT expressions is dependent upon knowing the desired years in advance.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:3A075548-587E-4732-BE9A-45EBDE8CD144@news.elevatesoft.com...
> Robert,
>
> << You should be able to use parameters instead of hardcoded year numbers.
> >>
>
> Unfortunately you cannot use parameters in SELECT expressions.
>

I never remember exactly what the rules are here. Obviously, you can do a
SELECT ... WHERE FIELD = :Param, so it is not clear why parameters would not
work with other flavors of SELECT. One of them things, I guess.

Robert

Robert,

<< I never remember exactly what the rules are here. Obviously, you can do a
SELECT ... WHERE FIELD = :Param, so it is not clear why parameters would not
work with other flavors of SELECT. One of them things, I guess. >>

The issue really comes down to one of allowing the database engine to
finalize what the result set will look like after a query is
compiled/prepared.  This is important for a lot of reasons, and using
dynamic parameters in the SELECT expressions list would prevent the database
engine from being able to determine 100% what each SELECT expression is in
terms of type, size, etc.

This isn't the case with WHERE or JOIN clauses, so they are permitted to
contain dynamic parameters.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:5A38D855-C076-4DC0-8042-73D10B586D82@news.elevatesoft.com...
> Robert,
>
> << I never remember exactly what the rules are here. Obviously, you can do
> a SELECT ... WHERE FIELD = :Param, so it is not clear why parameters would
> not work with other flavors of SELECT. One of them things, I guess. >>
>
> The issue really comes down to one of allowing the database engine to
> finalize what the result set will look like after a query is
> compiled/prepared.  This is important for a lot of reasons, and using
> dynamic parameters in the SELECT expressions list would prevent the
> database engine from being able to determine 100% what each SELECT
> expression is in terms of type, size, etc.
>
> This isn't the case with WHERE or JOIN clauses, so they are permitted to
> contain dynamic parameters.
>

Uh? This was the original statement in question

SELECT SUM(IF (EXTRACT(YEAR FROM INVOICEDATE) = 2002 THEN INVNET ELSE 0)) AS
TOT2002,

Why should this cause any problem?

SELECT SUM(IF (EXTRACT(YEAR FROM INVOICEDATE) = :y1 THEN INVNET ELSE 0)) AS
TOT2002,

Robert

Robert,

<< Why should this cause any problem? >>

It wouldn't.  However, the possibilities with parameters in the SELECT
expression list is not just limited to one particular SQL statement.
Someone could just as easily try this:

SELECT :MyParam
FROM MyTable

The question in this case is, what type/size is :MyParam at query
compilation/prepare time ?

--
Tim Young
Elevate Software
www.elevatesoft.com