Friday, December 22, 2006

I wish I'd started this blog earlier in the project. Now I'm going to have to reconstruct where I've gotten to so far.

I first started out creating a database for this project. I sketched out what we'd need for tables and data elements. I had our Oracle DBAs create me a database and I created the tables.

Next, I used Visual Studio .Net 2005 (VS) to create a strongly typed data access layer(DAL). As this was a simple system, I decided not to go with a separate business logic layer. I looked at the tutorial in MSDN for this and was overwhelmed by the arcane syntax and also by the problem with the parameterless constructor and problems with object datasource and strongly-typed datasets. I especially did not like the prospect of having to go and edit generated code.

The other reason for my decision is that I'm going to jump into this without a fully developed data model and expect to have to change things as I go along. I'm figuring this will be easier if there are fewer layers.

In creating the DAL for this project I am beset by some difficulties working with the Oracle database. I was unable to get some of my queries working as stored procedures.

The main query for the dataview grid takes data from my WORK_STATS table which looks like this:
USER_ID
WORK_DATE
WORKTYPE
COUNT
ENTRY_DATE

So there's one row per worktype per day. How are we going to display that in a grid that looks like this?



WORKTYPEMONTUEWEDTHUFRI
Dollars3287323419
Donuts322827342314139

The answer is to develop a pivot table query in Oracle. Thanks to help from Ask Tom,
I've come up with this little jewel:

SELECT WORKTYPE_DESC,
MAX(decode(WDAY,'MON',CNT,NULL)) MON,
MAX(decode(WDAY,'TUE',CNT,NULL)) TUE,
MAX(decode(WDAY,'WED',CNT,NULL)) WED,
MAX(decode(WDAY,'THU',CNT,NULL)) THU,
MAX(decode(WDAY,'FRI',CNT,NULL)) FRI,
MAX(WORKTYPE) WORKTYPE
FROM (SELECT WORKTYPE_DESC, TO_CHAR(WORK_DATE,'DY') WDAY,
SUM(COUNT) CNT, MAX(WORKTYPE) WORKTYPE
FROM WORK_STATS_VW
WHERE WORK_DATE BETWEEN 'FirstDate' AND 'LastDate'
GROUP BY WORKTYPE_DESC, TO_CHAR(WORK_DATE,'DY'))
GROUP BY WORKTYPE_DESC

This would definitely not work in VS as the add query parser
would not allow it. I created a separate class under AppCode and added a function which invoked this query against the Oracle db.

No comments: