Java and JDBC: Powerful Stuff

["Paul Holm" <pholm@xxxxxxxxxxxxxxxxx>]

IMHO and FYI?.

I am continually amazed at what business problems can be accomplished with
JDBC/SQL.  Most folks I work with underestimate the power of SQL.

In terms of our recent discussions on JDBC SQL and Java, here is an example:

Business Problem:

Warranty claims may come in larges bunches when a manufacturing issue has
occurred.  For example, the grease gun on manufacturing line 2 failed last
month and we sent out 213 trailers to Schneider.  Well all 213 trailers had
bearing failures so now we have 213 warranty claims.  Similar issues happen,
and it was a requirement for us to be able to handle all 213 of these
?identical? claims as one or otherwise they?d have to be dealt with and
individually approved one by one.  However we also needed to ensure that no
claim was made and paid on that particular trailer prior to this
transaction.  Fraud and reoccurring claims have been a problem in the past
and you can?t pay a claim twice and expect to run a business.     In my
mind, this is fairly complex issue.  Group up all identical claims by
service center and make sure no claim was paid on that VIN before and then
allow multi-processing.

SQL allows all this to be done in a single statement as follows:

select count(*) as Count, 'Claims' as dupdtls, 'Approve' as dupapv, 'Reject'
as duprej, serv_company,
serv_buid, cust_company, status, prob_desc_sc, claim_total_amount,
labor_total_amount, tax_total_amount,
parts_total_amount, void from warranty.claimmast c1 where

status = 1 and void <> 'Y' and not_available is null and
not exists (select * from warranty.parts p where p.claim_id = c1.claim_id
and p.part_provider = 2) and
not exists (select * from warranty.claimmast c2 where c1.claim_id <>
c2.claim_id and c2.void <> 'Y' and c2.status <> 10 and c1.vin = c2.vin)

group by serv_company, cust_company, serv_buid, status, prob_desc_sc,
claim_total_amount, labor_total_amount, tax_total_amount,
parts_total_amount, void having count(*) > 1

order by serv_company asc, Count desc


I recommend people taking a good look at SQL before creating custom code for
business problems.  Just my .02.

Thanks,  Paul Holm

Business: 760-432-0600   Home: 760-432-6550
Cell:  760-415-8830
PlanetJ - Makers of WOW  (AKA... WebSphere on Steroids)