sqlerr -119 when trying to produce following report

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

lenygold - 27 May 2008 00:23 GMT

Hi everybody!

I am trying to produce following report: sum of sales value for two years
group by months:

month 2006 2007
------- ------ --------
Jan 111.0 332.00
Feb 222.0 225.00
March 232.0 234.00
.................................
Dec 322.00 344.00

Here is my query for this report:

WITH T1(SALESDATE,SALESAMOUNT,YEAR,MONTH,MONTHNAME)
AS (SELECT MONTHEND,SALEVOL,YEAR(MONTHEND),MONTH(MONTHEND),MONTHNAME
(MONTHEND) FROM MYSALES
ORDER BY 3,4)
Select coalesce(y1.mm,y2.mm) AS MM,coalesce(y1.mth,y2.mth) AS MONTH_NAME,
sum(y1.amt) as
YEAR_2001,sum(y2.amt) as YEAR_2002
from (select month as mm, monthname as mth, sum(SalesAmount) as amt
from T1
WHERE year = 2006
group by month, monthname) as y1
full outer join
(select month as mm, monthname as mth,sum(SalesAmount) as amt
from T1
WHERE year = 2007
group by month, monthname) as y2
on y2.mth = y1.mth
order by coalesce(y1.mm,y2.mm);

But i got the following error:
sqlcode: -119
An expression starting with "MONTH_NAME specified in a SELECT
clause, HAVING clause, or ORDER BY clause is not specified in the GROUP BY
clause or it is in a SELECT clause, HAVING clause, or ORDER BY clause with a
column function and no GROUP BY clause is specified. SQLSTATE=4280

Any idea what is wrong with this query?
Thank's in advance. Leny G.

Reply to this Message

Tonkuma - 27 May 2008 15:55 GMT

The error -119 may be solved by adding "GROUP BY
coalesce(y1.mm,y2.mm),coalesce(y1.mth,y2.mth)".
But, looking into your required output, it must be not neccesary to
include "coalesce(y1.mm,y2.mm) AS MM "in SELECT list.

Here is an example:
WITH T1(SALESAMOUNT,YEAR,MONTH,MONTHNAME)
AS (SELECT SALEVOL,YEAR(MONTHEND),
MONTH(MONTHEND),SUBSTR(MONTHNAME (MONTHEND),1,12)
FROM MYSALES
)
Select coalesce(y1.mth,y2.mth) AS "month",
sum(y1.amt) as "2006", sum(y2.amt) as "2007"
from (select month as mm, monthname as mth, sum(SalesAmount) as amt
from T1
WHERE year = 2006
group by month, monthname) as y1
full outer join
(select month as mm, monthname as mth,sum(SalesAmount) as amt
from T1
WHERE year = 2007
group by month, monthname) as y2
on y2.mth = y1.mth
GROUP BY coalesce(y1.mm,y2.mm),coalesce(y1.mth,y2.mth)
order by coalesce(y1.mm,y2.mm)
;

Reply to this Message

lenygold - 27 May 2008 20:48 GMT

Thank You very much Tonkuma for your help it is working perfect:
Here is the output after your fix:

month 2006($mln) 2007($mln)
------------ ------------------- ------------------
January 337 354
February 366 409
March 431 437
April 379 412
May 419 481
June 425 403
July 397 462
August 421 465
September 434 417
October 441 405
November 455 407
December 406 380

12 record(s) selected.

One more question: T1 table data for 10 years from 1998 thru 2007.
Is there a generic way to build such report without adding 8 more outer joins
to existing query?
Thank's in advance Leny G.

>The error -119 may be solved by adding "GROUP BY
>coalesce(y1.mm,y2.mm),coalesce(y1.mth,y2.mth)".
[quoted text clipped - 22 lines]
> order by coalesce(y1.mm,y2.mm)
>;

Reply to this Message

Lennart - 28 May 2008 13:56 GMT

> Thank You very much Tonkuma for your help it is working perfect:
> Here is the output after your fix:
[quoted text clipped - 47 lines]
> > order by coalesce(y1.mm,y2.mm)
> >;

IMO sql is not the right tool to do presentation, but perhaps you can
use something like:

SELECT MONTH(MONTHEND),
sum(case when YEAR(MONTHEND) = 1997 then SALEVOL else 0
end) as Sum1997,
sum(case when YEAR(MONTHEND) = 1998 then SALEVOL else 0
end) as Sum1998,
[...]
FROM MYSALES

/Lennart

Reply to this Message