MVSFORUMS.com

tcurrier · Intermediate Joined: 10 Feb 2006 Posts: 188 Topics: 68

On our database, we have a table that contains vehicles (maximum 6 vehicles per 'policy')

I have a requirement to only list policies that have only motorcycles on them.

Just for the sake of this example, the vehicle table is VEHICLE, and the
vehicle type (motorcycle, passenger vehicle, trailer, etc.) is VEH_TYPE,
and the policy # is POL_NO (POL_NO is the table 'key')

I don't have a problem with doing counts on on entire table, but when it comes to counting and comparing within policies, I'm stuck. Obviously I
need to do a total count of vehicles per policy, then a count of motorcycles per policy, then compare the 2 counts. If they're equal, then
I print out the policy #.

Thanks for any help with this.

jsharon1248 · Posted: Wed Sep 10, 2008 8:31 am Post subject:

It sounds like you need a GROUP BY and a HAVING. If you determine the motorcycle count and the automobile count for each policy, you'd code something like:

tcurrier · Intermediate Joined: 10 Feb 2006 Posts: 188 Topics: 68

Yes, thanks, but that is exactly my problem.... I don't know the code format for capturing 2 separate counts (automobile and motorcycle) on the same policy.

tcurrier · Intermediate Joined: 10 Feb 2006 Posts: 188 Topics: 68

Thought it might help to show how the table looks.... I only want
to display the POL_NO for policies 2222222 and 4444444 (as they only have motorcycles on them). I would prefer to see all the rows for those policies , but I'd settle for just the unique POL_NO.

jsharon1248 · Posted: Wed Sep 10, 2008 10:41 am Post subject:

You'll need to use a pair of SUM'd columns to generate the counts. Something like this:

jsharon1248 · Posted: Wed Sep 10, 2008 10:49 am Post subject:

Missed the part about selecting all the rows for the motorcycle only policies. You can do that by nesting the SELECT.

jsharon1248 · Posted: Wed Sep 10, 2008 10:53 am Post subject:

I see syntax errors, so you can obviously see that this is untested sql. Here's minor corrections:

tcurrier · Intermediate Joined: 10 Feb 2006 Posts: 188 Topics: 68

From your first example, I get the following error:
(POL_SUFFIX is considered the 10th digit of the policy #)

kolusu · Posted: Wed Sep 10, 2008 11:25 am Post subject:

tcurrier · Intermediate Joined: 10 Feb 2006 Posts: 188 Topics: 68

Kolusu,

I got the following error with your example :

[code:1:8d3efc832e]SELECT *
FROM N090DSNM.H2591DB.TCSAU_VEHI
WHERE POL_NUM IN (SELECT POL_NUM
FROM N090DSNM.H2591DB.TCSAU_VEHI
WHERE VEHICLE_TYPE = 'M'
AND POL_NUM NOT IN (SELECT POL_NUM
FROM N090DSNM.H2591DB.TCSAU_VEHI
WHERE VEHICLE_TYPE

jsharon1248 · Posted: Wed Sep 10, 2008 11:57 am Post subject:

I don't remember if you can reference the SUM function in the HAVING clause for z/OS. So you have one of two options:

tcurrier · Intermediate Joined: 10 Feb 2006 Posts: 188 Topics: 68

Latest attempt: (I added POL_SUFFIX as this is considered part of the policy number)

kolusu · Posted: Wed Sep 10, 2008 12:56 pm Post subject:

tcurrier,

Do you just want to pick policy numbers that ONLY have motorcycle as veh type? if that is the case then try this sql

jsharon1248 · Posted: Wed Sep 10, 2008 1:22 pm Post subject:

For the sql you provided, move the HAVING predicates to the ON clause:

NASCAR9 · Posted: Wed Sep 10, 2008 1:26 pm Post subject:

I would like to see the Explain for both of these queries.
_________________
Thanks,
NASCAR9