VIEW and GROUP BY

[harborboy76]

Hi,
I have a problem with one of my view. Basically, I need to create a
view
to sum the amount in Table A grouped by the EMPLOYEE_ID. But I also
need the ability to limit the date range.

What I need to do is creating a view with the ability to limit the
date
range want the result sets to be broken down by employee_id (not
dates).
Is there an easy way to accomplish this ?

Table_A
-----------
ID EMPLOYEE_ID DATE AMOUNT
1 1 01/01/2006 100.00
2 1 01/02/2006 50.00
3 2 01/02/2006 25.00
4 1 01/03/2006 10.00
5 2 01/03/2006 15.00
6 3 01/03/2006 5.00
7 1 01/04/2006 10.00
8 2 01/04/2006 20.00
9 3 01/04/2006 30.00

---------------------------------------------------------------------
SELECT EMPLOYEE_ID,DAT E,SUM(AMOUNT)
FROM Table_A
GROUP BY EMPLOYEE_ID,DAT E ;
--------------------------------------------------------------------
This query will give me the DATE column, so that I can
pass in the date range I want in the view, but the result
sets will also be broken down by employee and date.

--------------------------------------------------------
SELECT EMPLOYEE,SUM(AM OUNT)
FROM Table_A
GROUP BY EMPLOYEE_ID;
--------------------------------------------------------
This query will provide me what I need, but when
I create the view over it, there is no way to pass in
the date range I want.

Any experts out there ? :-) I must accomplish this
in a view. I would appreciate any advices anyone can
give.

Thanks

[Dave Hughes]

ha*********@yah oo.com wrote:

Hi,
I have a problem with one of my view. Basically, I need to create a
view
to sum the amount in Table A grouped by the EMPLOYEE_ID. But I also
need the ability to limit the date range.
[snip] This query will provide me what I need, but when
I create the view over it, there is no way to pass in
the date range I want.

Any experts out there ? :-) I must accomplish this
in a view. I would appreciate any advices anyone can
give.

Is an SQL table function absolutely out of the question? Wouldn't be a
great deal different to accessing a view; you'd wind up using something
like the following to access the function:

SELECT
fields
FROM
TABLE(myfunc(st artdate, enddate)) AS T
Dave.

--

[Artur]

Exactly, I agree with Dave that function can be the solution.
Using view to achieve what you need, first of all rows have to be
filtered by date, and then grouped, eg.:

select employee_id, sum(amount)
from table_a
where date < '2006-01-04'
group by employee_id;

In view definition you cannot specify variables, but only fixed value
or value selected from another table. But function can accept a
variable passed as an argument.

create function employee_sum(p_ date date)
returns table (employee_id int, sum_amount int)
begin atomic
return
select employee_id,sum (amount)
from table_a
where date < p_date
group by employee_id;

end !

using the function:
select * from table(employee_ sum(date('2006-01-04'))) as employee_sum

PS. If you for 100% need a view you can create a special table and join
that table within the view. But before executing the select you need to
update min, max dates in the table, which is not very elegant solution,
roughly saying.

-- Artur Wronski

[harborboy76]

Arthur,

Thanks for the suggestion. It worked out really great when I tried
it
in the SQL Editor. But for the time being, our users are running their
reports though EXCEL ODBC. And they can't really execute any
stored procedures or functions through it. All they can access is
either user tables or views.

So I must try to make this work via view for now. I will keep
looking
to see what I can do with what I have. I have been experimenting a lot
with CTE (WITH Clause) or using GROUPING SETS without success.

Thank you

[Dave Hughes]

ha*********@yah oo.com wrote:

Arthur,

Thanks for the suggestion. It worked out really great when I tried
it
in the SQL Editor. But for the time being, our users are running their
reports though EXCEL ODBC. And they can't really execute any
stored procedures or functions through it. All they can access is
either user tables or views.

So I must try to make this work via view for now. I will keep
looking
to see what I can do with what I have. I have been experimenting a lot
with CTE (WITH Clause) or using GROUPING SETS without success.

Erm ... Using Excel ODBC shouldn't preclude users from accessing table
functions. As far as DB2 is concerned Excel is "just another ODBC
client", and I certainly have no troubles using table functions via
ODBC in other applications.

Oh, hold on a sec ... I've just figured it out ... you mean they're
accessing DB2 via Excel's "Query Wizard" or the MS Query interface
neither of which will list table functions? I've just tried out the MS
Query interface and it won't even let me add a table function reference
in a manual SQL statement! Urgh ... crap.

If you're stuck using Excel, there is another option which would allow
you to use table functions: use a ADO (ActiveX Data Objects) in a VB
script to send SQL to DB2 directly and retrieve the results into a
worksheet. Advantage: you can use any SQL you want. Disadvantage:
you've got to build the user interface (if you want one), and deal with
VB (urgh).

If you want to try that last option, let me know; I've got an Excel
file sat around that allows you to enter some SQL, execute it against
an ODBC data source, and retrieve the results into a worksheet. It's
very basic, but demonstrates the techniques (I mostly made it so I'd
have something to refer to whenever I needed to remember how to do this
:-)
HTH,

Dave.

--

[harborboy76]

Hi Dave,
Thank you very much for all the info. Yes, I was referring to the
MS Query. That's what our users have been running their EXCEL
reports against. Something better should be coming along in the
future, but I'm stuck with this for now. So I've kinda gotta work with
what I was given :-) I don't have much knowledge on this VB stuff,
but it does sound fantastic and flexible. Would you show me the
rope ? Now I have more google search to do haha.

Thanks very much you guys are awesome,
Nick

[Bob Stearns]

ha*********@yah oo.com wrote:

Hi,
I have a problem with one of my view. Basically, I need to create a
view
to sum the amount in Table A grouped by the EMPLOYEE_ID. But I also
need the ability to limit the date range.

What I need to do is creating a view with the ability to limit the
date
range want the result sets to be broken down by employee_id (not
dates).
Is there an easy way to accomplish this ?

Table_A
-----------
ID EMPLOYEE_ID DATE AMOUNT
1 1 01/01/2006 100.00
2 1 01/02/2006 50.00
3 2 01/02/2006 25.00
4 1 01/03/2006 10.00
5 2 01/03/2006 15.00
6 3 01/03/2006 5.00
7 1 01/04/2006 10.00
8 2 01/04/2006 20.00
9 3 01/04/2006 30.00

---------------------------------------------------------------------
SELECT EMPLOYEE_ID,DAT E,SUM(AMOUNT)
FROM Table_A
GROUP BY EMPLOYEE_ID,DAT E ;
--------------------------------------------------------------------
This query will give me the DATE column, so that I can
pass in the date range I want in the view, but the result
sets will also be broken down by employee and date.

--------------------------------------------------------
SELECT EMPLOYEE,SUM(AM OUNT)
FROM Table_A
GROUP BY EMPLOYEE_ID;
--------------------------------------------------------
This query will provide me what I need, but when
I create the view over it, there is no way to pass in
the date range I want.

Any experts out there ? :-) I must accomplish this
in a view. I would appreciate any advices anyone can
give.

Thanks

What is wrong with:

SELECT EMPLOYEE_ID,DAT E,SUM(AMOUNT),
FROM (SELECT * FROM table_a where date between 'fd' and 'ld') t1
GROUP BY EMPLOYEE_ID,DAT E

[Dave Hughes]

ha*********@yah oo.com wrote:

Hi Dave,
Thank you very much for all the info. Yes, I was referring to the
MS Query. That's what our users have been running their EXCEL
reports against. Something better should be coming along in the
future, but I'm stuck with this for now. So I've kinda gotta work with
what I was given :-) I don't have much knowledge on this VB stuff,
but it does sound fantastic and flexible. Would you show me the
rope ? Now I have more google search to do haha.

Flexible, yes. Fantastic ... well ... it's VB ... I tend to swear
profusely when coding in VB (and sometimes it makes its way into the
comments :-)

That said, I think the comments in this particular worksheet are
relatively benign (I must have been feeling very relaxed when I wrote
it!). Anyway, you can grab a copy of it from:

http://www.waveform.plus.com/ExcelQuery.xls

Unless you need to present your users with a list of ODBC data sources
within the worksheet, you can ignore the stuff in the "odbc" module
(which contains a little bit of the ODBC API translated into VB simply
for the purpose of populating the data sources drop down on the main
page). The stuff that'll be of primary interest to you is the code
behind the Execute button in Sheet1 (see the QueryBtn_Click subroutine).

Drop me a mail if you need a hand with any of the code (Excel & VB is
rather off-topic for this newsgroup, even if they are being used to
access DB2 :-)
HTH,

Dave.
--

[harborboy76]

Hi Bob,

Thanks for the reply. My problem with your solution is that 'fd'
and 'ld'
can't be hard coded. I don't know the date ranges that the user will
pass in.
And views cannot be created with variables unlike procedures or
functions.
Unless I'm missing something :-)

Nick

[harborboy76]

Thanks Dave,
This looks to be something really neat. I'm looking at the worksheet

and the codes. I'll let you know what I run into next :-)

Nick