Fancy Query Problem

Haven't tried it but how about trying a union. The syntax may be wrong and
it may not be optimized but ...

******
select firm, Sum(nn), Sum(yy) from
(Select Firm, 1 as nn, 0 as yy
from table1, table2
where over1 = 'N' and over2 = 'N' and table1 ...
Union
select firm, 0 as nn, 1 as yy
from table1, table2
where (over1 = 'Y' or over2 = 'Y' and table1 ...
union
select firm, 0 as nn, 0 as yy
from table1
where firm not in (select distinct firm from table2)
)
group by firm

********

"kevin knudson" <klk@knudsons.com> wrote:
>
>I have a two tables joined that consists of 3 fields (actually more but
simplified)
>Table1 Table2(Status)
>Firm Over1 Over2
>A Y N
>A N N
>B N N
>C N Y
>
>What I what is a row for each Firm that shows the total of N / N, and total
>of Y in either.
>Firm NN YY
>A 1 1
>B 1 0
>C 0 1
>
>
>Technially I also need to include Firms with no status records, but thats
>not inmportant. I have created views for NN, and YorY, but this doesn't
>seem to solve things.
>
>I really need to do this without Temp tables, long story.
>
>Note this is UDB on AIX.
>
>Any ideas ?
>
>Thanks Much
>
>KlK, MCSE
>

Thanks Guys, I'll give them a try and report back. I had tried a Union and
couldn't get it right. I see how yours is different.

The correlated sub query sound interesting. Hopefully UDB supports it.

KlK, MCSE

"Colin McGuigan" <cmcguigan@imany.com> wrote:
>
>"kevin knudson" <klk@knudsons.com> wrote in message
>news:3d3ed117$1@10.1.10.29...
>>
>> I have a two tables joined that consists of 3 fields (actually more but
>simplified)
>> Table1 Table2(Status)
>> Firm Over1 Over2
>> A Y N
>> A N N
>> B N N
>> C N Y
>>
>> What I what is a row for each Firm that shows the total of N / N, and
>total
>> of Y in either.
>> Firm NN YY
>> A 1 1
>> B 1 0
>> C 0 1
>
>Don't know about UDB, but if it supports correlated subqueries...
>
>SELECT FirmQuery.Firm, (
> SELECT COUNT(*)
> FROM MyTable NNTable
> WHERE NNTable.Firm = FirmQuery.Firm
> AND NNTable.Over1 = 'N'
> AND NNTable.Over2 = 'N'
>) AS NN, (
> SELECT COUNT(*)
> FROM MyTable YYTable
> WHERE YYTable.Firm = FirmQuery.Firm
> AND (YYTable.Over1 = 'Y' OR YYTable.Over2 = 'Y')
>) AS YY
>FROM (
> SELECT DISTINCT Firm
> FROM MyTable
>) FirmQuery
>
>--
>Colin McGuigan
>
>

Hey guys thanks much. My hat is off to both off you. These were SOME queries.
Colin I went with your path, thanks to Mark for the effort

Here's the full version, I abbrev. the example

SELECT
TTT.ORG.ORG_DESC_X AS "Firm",
(
SELECT
COUNT(*)
FROM
TTT.INV
JOIN TTT.STATUS ON TTT.INV.INV_ID_K = TTT.STATUS.INV_ID_K
WHERE
TTT.INV.LAW_FIRM_ID_N = FIRMQUERY.LAW_FIRM_ID_N
AND
TTT.STATUS.STATUS_C = 'MNPR'
AND
TTT.STATUS.VLD_TO_D IS NULL
AND
TTT.INV.INV_OVR_THRSHLD = 'N'
AND
TTT.INV.CLM_OVR_THRSHLD = 'N'
) AS NN,
(
SELECT
COUNT(*)
FROM
TTT.INV
JOIN
TTT.STATUS ON TTT.INV.INV_ID_K = TTT.STATUS.INV_ID_K
WHERE
TTT.INV.LAW_FIRM_ID_N = FIRMQUERY.LAW_FIRM_ID_N
AND
TTT.STATUS.STATUS_C = 'MNPR'
AND
TTT.STATUS.VLD_TO_D is null
AND
(TTT.INV.INV_OVR_THRSHLD = 'Y'
OR
TTT.INV.CLM_OVR_THRSHLD = 'Y')

) AS "Y or Y"

FROM
(SELECT DISTINCT TTT.INV.LAW_FIRM_ID_N FROM TTT.INV) FIRMQUERY
JOIN
TTT.ORG ON FIRMQUERY.LAW_FIRM_ID_N = TTT.ORG.TAX_ID_C
GROUP BY
TTT.ORG.ORG_DESC_X,
FIRMQUERY.LAW_FIRM_ID_N
ORDER BY
TTT.ORG.ORG_DESC_X,
FIRMQUERY.LAW_FIRM_ID_N

Hope it's readable.

Couldn't have done it without your help. I'm pretty good with SQL, this
proves though. No matter how good you think you are, there's always someone
better.

KlK, MCSE

"Markn" <m@n.com> wrote:
>
>I believe it does (I don't know if DB2 calls it that). I'm pretty sure
I
>have one running somewhere. Yep. Found it. Just gotta get that syntax
>right. It looks to be the same. Note: I've found that subqueries are typically
>slower. Let us know what you find out.
>
>
>
>"kevin knudson" <klk@DontEmailMe.com> wrote:
>>
>>Thanks Guys, I'll give them a try and report back. I had tried a Union
>and
>>couldn't get it right. I see how yours is different.
>>
>>The correlated sub query sound interesting. Hopefully UDB supports it.
>>
>>KlK, MCSE
>>
>>"Colin McGuigan" <cmcguigan@imany.com> wrote:
>>>
>>>"kevin knudson" <klk@knudsons.com> wrote in message
>>>news:3d3ed117$1@10.1.10.29...
>>>>
>>>> I have a two tables joined that consists of 3 fields (actually more
but
>>>simplified)
>>>> Table1 Table2(Status)
>>>> Firm Over1 Over2
>>>> A Y N
>>>> A N N
>>>> B N N
>>>> C N Y
>>>>
>>>> What I what is a row for each Firm that shows the total of N / N, and
>>>total
>>>> of Y in either.
>>>> Firm NN YY
>>>> A 1 1
>>>> B 1 0
>>>> C 0 1
>>>
>>>Don't know about UDB, but if it supports correlated subqueries...
>>>
>>>SELECT FirmQuery.Firm, (
>>> SELECT COUNT(*)
>>> FROM MyTable NNTable
>>> WHERE NNTable.Firm = FirmQuery.Firm
>>> AND NNTable.Over1 = 'N'
>>> AND NNTable.Over2 = 'N'
>>>) AS NN, (
>>> SELECT COUNT(*)
>>> FROM MyTable YYTable
>>> WHERE YYTable.Firm = FirmQuery.Firm
>>> AND (YYTable.Over1 = 'Y' OR YYTable.Over2 = 'Y')
>>>) AS YY
>>>FROM (
>>> SELECT DISTINCT Firm
>>> FROM MyTable
>>>) FirmQuery
>>>
>>>--
>>>Colin McGuigan
>>>
>>>
>>
>

DW, Thanks also
Yours is very fast but I cannot get the Join structure correct to include
those with nothing in table2, i.e. yy =0 and nn = 0
Don't understand I tried the full outer. But the real data is 3 tables joined.

Well I gave the other to the developers, they'll have to use it that way.

Thanks ALL again.

KlK, MCSE

"dw" <dw@dontemailme.com> wrote:
>
>SQL 7.0 supports the following. Not sure DB2 will.
>
>select a.firm,
> sum(case WHEN b.over1 = 'Y' or b.over2 = 'Y' then 1 else 0 END) AS YY,
> SUM(case WHEN b.over1 = 'N' AND b.over2 = 'N' then 1 else 0 END) AS NN
>FROM table1 a LEFT join table2 b on a.firm = b.firm
>group by a.firm
>
>
>"kevin knudson" <klk@DontEmailMe.com> wrote:
>>
>>Hey guys thanks much. My hat is off to both off you. These were SOME
queries.
>>Colin I went with your path, thanks to Mark for the effort
>>
>>Here's the full version, I abbrev. the example
>>
>> SELECT
>> TTT.ORG.ORG_DESC_X AS "Firm",
>> (
>> SELECT
>> COUNT(*)
>> FROM
>> TTT.INV
>> JOIN TTT.STATUS ON TTT.INV.INV_ID_K = TTT.STATUS.INV_ID_K
>> WHERE
>> TTT.INV.LAW_FIRM_ID_N = FIRMQUERY.LAW_FIRM_ID_N
>> AND
>> TTT.STATUS.STATUS_C = 'MNPR'
>> AND
>> TTT.STATUS.VLD_TO_D IS NULL
>> AND
>> TTT.INV.INV_OVR_THRSHLD = 'N'
>> AND
>> TTT.INV.CLM_OVR_THRSHLD = 'N'
>> ) AS NN,
>> (
>> SELECT
>> COUNT(*)
>> FROM
>> TTT.INV
>> JOIN
>> TTT.STATUS ON TTT.INV.INV_ID_K = TTT.STATUS.INV_ID_K
>> WHERE
>> TTT.INV.LAW_FIRM_ID_N = FIRMQUERY.LAW_FIRM_ID_N
>> AND
>> TTT.STATUS.STATUS_C = 'MNPR'
>> AND
>> TTT.STATUS.VLD_TO_D is null
>> AND
>> (TTT.INV.INV_OVR_THRSHLD = 'Y'
>> OR
>> TTT.INV.CLM_OVR_THRSHLD = 'Y')
>>
>> ) AS "Y or Y"
>>
>> FROM
>> (SELECT DISTINCT TTT.INV.LAW_FIRM_ID_N FROM TTT.INV) FIRMQUERY
>> JOIN
>> TTT.ORG ON FIRMQUERY.LAW_FIRM_ID_N = TTT.ORG.TAX_ID_C
>> GROUP BY
>> TTT.ORG.ORG_DESC_X,
>> FIRMQUERY.LAW_FIRM_ID_N
>> ORDER BY
>> TTT.ORG.ORG_DESC_X,
>> FIRMQUERY.LAW_FIRM_ID_N
>>
>>
>>Hope it's readable.
>>
>>Couldn't have done it without your help. I'm pretty good with SQL, this
>>proves though. No matter how good you think you are, there's always someone
>>better.
>>
>>KlK, MCSE
>>
>>
>>"Markn" <m@n.com> wrote:
>>>
>>>I believe it does (I don't know if DB2 calls it that). I'm pretty sure
>>I
>>>have one running somewhere. Yep. Found it. Just gotta get that syntax
>>>right. It looks to be the same. Note: I've found that subqueries are
>typically
>>>slower. Let us know what you find out.
>>>
>>>
>>>
>>>"kevin knudson" <klk@DontEmailMe.com> wrote:
>>>>
>>>>Thanks Guys, I'll give them a try and report back. I had tried a Union
>>>and
>>>>couldn't get it right. I see how yours is different.
>>>>
>>>>The correlated sub query sound interesting. Hopefully UDB supports it.
>>>>
>>>>KlK, MCSE
>>>>
>>>>"Colin McGuigan" <cmcguigan@imany.com> wrote:
>>>>>
>>>>>"kevin knudson" <klk@knudsons.com> wrote in message
>>>>>news:3d3ed117$1@10.1.10.29...
>>>>>>
>>>>>> I have a two tables joined that consists of 3 fields (actually more
>>but
>>>>>simplified)
>>>>>> Table1 Table2(Status)
>>>>>> Firm Over1 Over2
>>>>>> A Y N
>>>>>> A N N
>>>>>> B N N
>>>>>> C N Y
>>>>>>
>>>>>> What I what is a row for each Firm that shows the total of N / N,
and
>>>>>total
>>>>>> of Y in either.
>>>>>> Firm NN YY
>>>>>> A 1 1
>>>>>> B 1 0
>>>>>> C 0 1
>>>>>
>>>>>Don't know about UDB, but if it supports correlated subqueries...
>>>>>
>>>>>SELECT FirmQuery.Firm, (
>>>>> SELECT COUNT(*)
>>>>> FROM MyTable NNTable
>>>>> WHERE NNTable.Firm = FirmQuery.Firm
>>>>> AND NNTable.Over1 = 'N'
>>>>> AND NNTable.Over2 = 'N'
>>>>>) AS NN, (
>>>>> SELECT COUNT(*)
>>>>> FROM MyTable YYTable
>>>>> WHERE YYTable.Firm = FirmQuery.Firm
>>>>> AND (YYTable.Over1 = 'Y' OR YYTable.Over2 = 'Y')
>>>>>) AS YY
>>>>>FROM (
>>>>> SELECT DISTINCT Firm
>>>>> FROM MyTable
>>>>>) FirmQuery
>>>>>
>>>>>--
>>>>>Colin McGuigan
>>>>>
>>>>>
>>>>
>>>
>>
>

First I think your table1.field = table2.field is a join, just using pre ANSI
92 syntax. I try and push here (not always successfully) to use the latest
syntax etc.

Depending on the SQL code our developers do generally create it themselves,
but it is reviewed by the DBA staff. When you get more complex, this was
close to over my head, we do get involved and assist.

I am actually reviewing more and more, I have fixed a number of statements
with huge improvements. It also helps me to better understand their data.

KlK, MCSE

"MarkN" <m@N.com> wrote:
>
>kevin,
> Then in his query, instead of doing Joins (which I don't do for inner
joins
>- too confusing), do table1.field = table2.field and then outer join the
>3rd table. You will also need to do a value statement on the columns to
>convert nulls to 0. Or just toss on the extra union statement, but I think
>the other will be faster.
>
>Your developers don't do their own SQL? :) Haven't had that since I left
>the Mainframe.
>
>Mark
>
>"kevin knudson" <klk@DontEmailMe.com> wrote:
>>
>>DW, Thanks also
>>Yours is very fast but I cannot get the Join structure correct to include
>>those with nothing in table2, i.e. yy =0 and nn = 0
>>Don't understand I tried the full outer. But the real data is 3 tables
>joined.
>>
>>Well I gave the other to the developers, they'll have to use it that way.
>>
>>Thanks ALL again.
>>
>>KlK, MCSE
>>
>>"dw" <dw@dontemailme.com> wrote:
>>>
>>>SQL 7.0 supports the following. Not sure DB2 will.
>>>
>>>select a.firm,
>>> sum(case WHEN b.over1 = 'Y' or b.over2 = 'Y' then 1 else 0 END) AS
YY,
>>> SUM(case WHEN b.over1 = 'N' AND b.over2 = 'N' then 1 else 0 END) AS
>NN
>>>FROM table1 a LEFT join table2 b on a.firm = b.firm
>>>group by a.firm
>>>
>>>
>>>"kevin knudson" <klk@DontEmailMe.com> wrote:
>>>>
>>>>Hey guys thanks much. My hat is off to both off you. These were SOME
>>queries.
>>>>Colin I went with your path, thanks to Mark for the effort
>>>>
>>>>Here's the full version, I abbrev. the example
>>>>
>>>> SELECT
>>>> TTT.ORG.ORG_DESC_X AS "Firm",
>>>> (
>>>> SELECT
>>>> COUNT(*)
>>>> FROM
>>>> TTT.INV
>>>> JOIN TTT.STATUS ON TTT.INV.INV_ID_K = TTT.STATUS.INV_ID_K
>>>> WHERE
>>>> TTT.INV.LAW_FIRM_ID_N = FIRMQUERY.LAW_FIRM_ID_N
>>>> AND
>>>> TTT.STATUS.STATUS_C = 'MNPR'
>>>> AND
>>>> TTT.STATUS.VLD_TO_D IS NULL
>>>> AND
>>>> TTT.INV.INV_OVR_THRSHLD = 'N'
>>>> AND
>>>> TTT.INV.CLM_OVR_THRSHLD = 'N'
>>>> ) AS NN,
>>>> (
>>>> SELECT
>>>> COUNT(*)
>>>> FROM
>>>> TTT.INV
>>>> JOIN
>>>> TTT.STATUS ON TTT.INV.INV_ID_K = TTT.STATUS.INV_ID_K
>>>> WHERE
>>>> TTT.INV.LAW_FIRM_ID_N = FIRMQUERY.LAW_FIRM_ID_N
>>>> AND
>>>> TTT.STATUS.STATUS_C = 'MNPR'
>>>> AND
>>>> TTT.STATUS.VLD_TO_D is null
>>>> AND
>>>> (TTT.INV.INV_OVR_THRSHLD = 'Y'
>>>> OR
>>>> TTT.INV.CLM_OVR_THRSHLD = 'Y')
>>>>
>>>> ) AS "Y or Y"
>>>>
>>>> FROM
>>>> (SELECT DISTINCT TTT.INV.LAW_FIRM_ID_N FROM TTT.INV) FIRMQUERY
>>>> JOIN
>>>> TTT.ORG ON FIRMQUERY.LAW_FIRM_ID_N = TTT.ORG.TAX_ID_C
>>>> GROUP BY
>>>> TTT.ORG.ORG_DESC_X,
>>>> FIRMQUERY.LAW_FIRM_ID_N
>>>> ORDER BY
>>>> TTT.ORG.ORG_DESC_X,
>>>> FIRMQUERY.LAW_FIRM_ID_N
>>>>
>>>>
>>>>Hope it's readable.
>>>>
>>>>Couldn't have done it without your help. I'm pretty good with SQL, this
>>>>proves though. No matter how good you think you are, there's always
someone
>>>>better.
>>>>
>>>>KlK, MCSE
>>>>
>>>>
>>>>"Markn" <m@n.com> wrote:
>>>>>
>>>>>I believe it does (I don't know if DB2 calls it that). I'm pretty sure
>>>>I
>>>>>have one running somewhere. Yep. Found it. Just gotta get that syntax
>>>>>right. It looks to be the same. Note: I've found that subqueries are
>>>typically
>>>>>slower. Let us know what you find out.
>>>>>
>>>>>
>>>>>
>>>>>"kevin knudson" <klk@DontEmailMe.com> wrote:
>>>>>>
>>>>>>Thanks Guys, I'll give them a try and report back. I had tried a Union
>>>>>and
>>>>>>couldn't get it right. I see how yours is different.
>>>>>>
>>>>>>The correlated sub query sound interesting. Hopefully UDB supports
>it.
>>>>>>
>>>>>>KlK, MCSE
>>>>>>
>>>>>>"Colin McGuigan" <cmcguigan@imany.com> wrote:
>>>>>>>
>>>>>>>"kevin knudson" <klk@knudsons.com> wrote in message
>>>>>>>news:3d3ed117$1@10.1.10.29...
>>>>>>>>
>>>>>>>> I have a two tables joined that consists of 3 fields (actually more
>>>>but
>>>>>>>simplified)
>>>>>>>> Table1 Table2(Status)
>>>>>>>> Firm Over1 Over2
>>>>>>>> A Y N
>>>>>>>> A N N
>>>>>>>> B N N
>>>>>>>> C N Y
>>>>>>>>
>>>>>>>> What I what is a row for each Firm that shows the total of N / N,
>>and
>>>>>>>total
>>>>>>>> of Y in either.
>>>>>>>> Firm NN YY
>>>>>>>> A 1 1
>>>>>>>> B 1 0
>>>>>>>> C 0 1
>>>>>>>
>>>>>>>Don't know about UDB, but if it supports correlated subqueries...
>>>>>>>
>>>>>>>SELECT FirmQuery.Firm, (
>>>>>>> SELECT COUNT(*)
>>>>>>> FROM MyTable NNTable
>>>>>>> WHERE NNTable.Firm = FirmQuery.Firm
>>>>>>> AND NNTable.Over1 = 'N'
>>>>>>> AND NNTable.Over2 = 'N'
>>>>>>>) AS NN, (
>>>>>>> SELECT COUNT(*)
>>>>>>> FROM MyTable YYTable
>>>>>>> WHERE YYTable.Firm = FirmQuery.Firm
>>>>>>> AND (YYTable.Over1 = 'Y' OR YYTable.Over2 = 'Y')
>>>>>>>) AS YY
>>>>>>>FROM (
>>>>>>> SELECT DISTINCT Firm
>>>>>>> FROM MyTable
>>>>>>>) FirmQuery
>>>>>>>
>>>>>>>--
>>>>>>>Colin McGuigan
>>>>>>>
>>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>
>

Just saw this here, after my tirade on the other thread.

I do know what you mean about skill levels/certs, I rescued a MCDBA when
he trashed a client DB, (made a bunch o money bailing him out).

I still prefer even with our developers that are pretty good, to review their
code, it never hurts to have a second set of eyes look things over. I try
to get my stuff looked at when possible.

Basically I prefer the ANSI 92 stuff, the only times I haven't been able
to get them working are with the IAA model we are supposed to use here.

I'll throw another SQL tool, the View Builder. It allows you to build some
pretty fancy stuff, graphically. I have noticed some issue's if you try
to change complex views. But it builds them nicely, and (I know) it's included
free with SQL Server.

Sorry :-}

KlK, MCSE

"MarkN" <m@n.com> wrote:
>
>>First I think your table1.field = table2.field is a join, just using pre
>ANSI
>>92 syntax. I try and push here (not always successfully) to use the latest
>>syntax etc.
>
>Ok, but it is more easily understood and I have found I can't do some joins
>unless I code it this way. Anyway, I am moving away from writing most SQL
>by using tools, like Hibernate, that create it for me. It can write those
>JOINs for me with out me having to get cross eyed.
>
>>
>>Depending on the SQL code our developers do generally create it themselves,
>>but it is reviewed by the DBA staff. When you get more complex, this was
>>close to over my head, we do get involved and assist.
>>
>>I am actually reviewing more and more, I have fixed a number of statements
>>with huge improvements. It also helps me to better understand their data.
>
>I was messing with you. If a DBA really knows what they are doing (like
>you), this is of help. I'm not ordinary so I should't figure everyone is
>like me. Since I have left the mainframe though, I have found few that
really
>know what they are doing. I mean even certified DBAs. Not that the Mainframers
>knew what they were doing - it just is more difficult in that environment
>and things are more controlled. Although I did write all my own IMS code
>before with no DBA looking at it all or even explaining the DB structure.
> I wish they had.
>
>I'm keeping you in mind for when I run into one of those things I can't
figure
>out.