Frustrating SubQuery Won't Total

Can someone help me determine why my subquery below is not totaling. Without
having to dig to deeply into the logic and data, I am hoping that seeing the
working/non working versions together might reveal something obvious. I have
removed the other selected columns from the main query.
I have tried using distinct and grouping in the subquery and using grouping
in the outer query to no avail.
Version 1 'unexpected' result with syntax is as it would be normally in the
query
TaxablePremium
--
1000.00
2000.00
Version 2 expected result with the FKs that would be passed from the outer
query assigned
TaxablePremium
--
3000.00
SELECT distinct
/* SubQuery version 1 - syntax is as it would be normally in the query*/
(SELECT SUM(QuotePremium.Premium)
FROM QuotePremium
INNER JOIN QuoteLine ON QuotePremium.QuoteLineID = QuoteLine.ID
INNER JOIN QuoteTaxPremium ON QuotePremium.ID =
QuoteTaxPremium.QuotePremiumID
WHERE (QuoteLine.InvoiceID = ILI1.InvoiceId)
AND (QuoteTaxPremium.TaxLineID in(InvoiceLineItemTax.taxlobId))
) as TaxablePremium
/* SubQuery version 2- FKs that would be passed from the outer query are
assigned */
(SELECT SUM(QuotePremium.Premium)
FROM QuotePremium
INNER JOIN QuoteLine ON QuotePremium.QuoteLineID = QuoteLine.ID
INNER JOIN QuoteTaxPremium ON QuotePremium.ID =
QuoteTaxPremium.QuotePremiumID
WHERE (QuoteLine.InvoiceID = 5947) AND (QuoteTaxPremium.TaxLineID IN
(4298, 4353)))
AS TaxablePremium
FROM InvoiceLineItem ILI1
INNER JOIN InvoiceLineItemTax ON ILI1.ID =
InvoiceLineItemTax.InvoiceLineItemID
INNER JOIN TaxLOB ON InvoiceLineItemTax.TaxLOBID = TaxLOB.ID
INNER JOIN Tax ON TaxLOB.TaxID = Tax.ID
INNER JOIN Invoice ON ILI1.InvoiceID = Invoice.ID
INNER JOIN Office Office1 ON Invoice.InsuredID = Office1.ID
INNER JOIN InvoiceType ON Invoice.InvoiceTypeID = InvoiceType.ID
INNER JOIN Office Office2 ON Tax.RegulatoryAuthorityID = Office2.ID
WHERE (InvoiceLineItemTax.FilingPartyType = 'A')
AND (Invoice.IsFutureTransaction = 0) AND (Invoice.APTaxBalance <> 0)
AND (ILI1.IsContraDistribution = 0) AND (ILI1.LineItemAccountingType = 2)
AND (ILI1.Balance <> 0) AND (NOT (Invoice.AMSPostDate IS NULL))
AND (Office2.ID = @.ReglAuthId) AND (Tax.ID = @.TaxId)
AND (Invoice.InvoiceNumber =@.InvNbr1)Mike
If you show us a table structute with sample data we'd probably suggest
something.
In the first query you JOIN InvoiceID to ILI1.InvoiceId and here is I think
a problem. Try to look at your tables more carefully to see what is going
on when you're trying to JOIN them and to assign values.
"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:OZtxqQY7FHA.3752@.tk2msftngp13.phx.gbl...
> Can someone help me determine why my subquery below is not totaling.
> Without having to dig to deeply into the logic and data, I am hoping that
> seeing the working/non working versions together might reveal something
> obvious. I have removed the other selected columns from the main query.
> I have tried using distinct and grouping in the subquery and using
> grouping in the outer query to no avail.
> Version 1 'unexpected' result with syntax is as it would be normally in
> the query
> TaxablePremium
> --
> 1000.00
> 2000.00
> Version 2 expected result with the FKs that would be passed from the outer
> query assigned
> TaxablePremium
> --
> 3000.00
>
> SELECT distinct
> /* SubQuery version 1 - syntax is as it would be normally in the query*/
> (SELECT SUM(QuotePremium.Premium)
> FROM QuotePremium
> INNER JOIN QuoteLine ON QuotePremium.QuoteLineID = QuoteLine.ID
> INNER JOIN QuoteTaxPremium ON QuotePremium.ID =
> QuoteTaxPremium.QuotePremiumID
> WHERE (QuoteLine.InvoiceID = ILI1.InvoiceId)
> AND (QuoteTaxPremium.TaxLineID in(InvoiceLineItemTax.taxlobId))
> ) as TaxablePremium
> /* SubQuery version 2- FKs that would be passed from the outer query are
> assigned */
> (SELECT SUM(QuotePremium.Premium)
> FROM QuotePremium
> INNER JOIN QuoteLine ON QuotePremium.QuoteLineID = QuoteLine.ID
> INNER JOIN QuoteTaxPremium ON QuotePremium.ID =
> QuoteTaxPremium.QuotePremiumID
> WHERE (QuoteLine.InvoiceID = 5947) AND (QuoteTaxPremium.TaxLineID IN
> (4298, 4353)))
> AS TaxablePremium
> FROM InvoiceLineItem ILI1
> INNER JOIN InvoiceLineItemTax ON ILI1.ID =
> InvoiceLineItemTax.InvoiceLineItemID
> INNER JOIN TaxLOB ON InvoiceLineItemTax.TaxLOBID = TaxLOB.ID
> INNER JOIN Tax ON TaxLOB.TaxID = Tax.ID
> INNER JOIN Invoice ON ILI1.InvoiceID = Invoice.ID
> INNER JOIN Office Office1 ON Invoice.InsuredID = Office1.ID
> INNER JOIN InvoiceType ON Invoice.InvoiceTypeID = InvoiceType.ID
> INNER JOIN Office Office2 ON Tax.RegulatoryAuthorityID = Office2.ID
> WHERE (InvoiceLineItemTax.FilingPartyType = 'A')
> AND (Invoice.IsFutureTransaction = 0) AND (Invoice.APTaxBalance <> 0)
> AND (ILI1.IsContraDistribution = 0) AND (ILI1.LineItemAccountingType = 2)
> AND (ILI1.Balance <> 0) AND (NOT (Invoice.AMSPostDate IS NULL))
> AND (Office2.ID = @.ReglAuthId) AND (Tax.ID = @.TaxId)
> AND (Invoice.InvoiceNumber =@.InvNbr1)
>|||"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:OZtxqQY7FHA.3752@.tk2msftngp13.phx.gbl...
> Can someone help me determine why my subquery below is not
totaling. Without
> having to dig to deeply into the logic and data, I am hoping that
seeing the
> working/non working versions together might reveal something
obvious. I have
> removed the other selected columns from the main query.
> I have tried using distinct and grouping in the subquery and using
grouping
> in the outer query to no avail.
> Version 1 'unexpected' result with syntax is as it would be
normally in the
> query
> TaxablePremium
> --
> 1000.00
> 2000.00
> Version 2 expected result with the FKs that would be passed from
the outer
> query assigned
> TaxablePremium
> --
> 3000.00
>
> SELECT distinct
> /* SubQuery version 1 - syntax is as it would be normally in the
query*/
> (SELECT SUM(QuotePremium.Premium)
> FROM QuotePremium
> INNER JOIN QuoteLine ON QuotePremium.QuoteLineID = QuoteLine.ID
> INNER JOIN QuoteTaxPremium ON QuotePremium.ID =
> QuoteTaxPremium.QuotePremiumID
> WHERE (QuoteLine.InvoiceID = ILI1.InvoiceId)
> AND (QuoteTaxPremium.TaxLineID
in(InvoiceLineItemTax.taxlobId))
> ) as TaxablePremium
> /* SubQuery version 2- FKs that would be passed from the outer
query are
> assigned */
> (SELECT SUM(QuotePremium.Premium)
> FROM QuotePremium
> INNER JOIN QuoteLine ON QuotePremium.QuoteLineID = QuoteLine.ID
> INNER JOIN QuoteTaxPremium ON QuotePremium.ID =
> QuoteTaxPremium.QuotePremiumID
> WHERE (QuoteLine.InvoiceID = 5947) AND
(QuoteTaxPremium.TaxLineID IN
> (4298, 4353)))
> AS TaxablePremium
> FROM InvoiceLineItem ILI1
> INNER JOIN InvoiceLineItemTax ON ILI1.ID =
> InvoiceLineItemTax.InvoiceLineItemID
> INNER JOIN TaxLOB ON InvoiceLineItemTax.TaxLOBID = TaxLOB.ID
> INNER JOIN Tax ON TaxLOB.TaxID = Tax.ID
> INNER JOIN Invoice ON ILI1.InvoiceID = Invoice.ID
> INNER JOIN Office Office1 ON Invoice.InsuredID = Office1.ID
> INNER JOIN InvoiceType ON Invoice.InvoiceTypeID = InvoiceType.ID
> INNER JOIN Office Office2 ON Tax.RegulatoryAuthorityID =
Office2.ID
> WHERE (InvoiceLineItemTax.FilingPartyType = 'A')
> AND (Invoice.IsFutureTransaction = 0) AND (Invoice.APTaxBalance <>
0)
> AND (ILI1.IsContraDistribution = 0) AND
(ILI1.LineItemAccountingType = 2)
> AND (ILI1.Balance <> 0) AND (NOT (Invoice.AMSPostDate IS NULL))
> AND (Office2.ID = @.ReglAuthId) AND (Tax.ID = @.TaxId)
> AND (Invoice.InvoiceNumber =@.InvNbr1)
>
Mike Harbinger,
Without a fully reproducible script to test with
(http://www.aspfaq.com/etiquette.asp?id=5006), it is difficult to
say for sure.
However, I do spot something interesting.
Query 1:

>AND (QuoteTaxPremium.TaxLineID in(InvoiceLineItemTax.taxlobId))
This seems a bit unusual at first glance. Why specify IN (the
shortcut for <this> OR <that> OR <the other> ) when there is but one
column named? I would have thought it should just be:
AND (QuoteTaxPremium.TaxLineID = InvoiceLineItemTax.taxlobId)
And then I see:
Query 2:

> WHERE (QuoteLine.InvoiceID = 5947)
> AND (QuoteTaxPremium.TaxLineID IN (4298, 4353)))
> AS TaxablePremium
This is supposed to be the equivalent of Query 1, only with literals
assigned. Except that two literals are assigned in the IN() list.
In Query 1, only 1 value will be present.
This makes Query 1 slightly different from what is implied in the
construction of the IN() in Query 2.
Is it enough of a difference to be causing the problem in question?
My apologies, but I can't say for sure.
Sincererely,
Chris O.|||Uri
Thank you for your prompt reply.
Below is the data from my earlier sample post.
I have omitted only those columns that are not
material to this portion of the result set.
What we have is an invoice that contains an
insurance premium amount and a tax amount. I am
trying to show a specific premium which is subject
to a specific tax.
That would be indicated by an invoice that has
a tax amount (ILI1) and a premium (QuotePremium)
joined on TaxLOB and Tax.
Path to tax via premium amount:
Invoice\QuoteLine\QuotePremium\QuoteTaxP
remium\TaxLOB\Tax
Path to tax via tax amount:
Invoice\InvoiceLineItem (as ILI1)\InvicelineItemTax\TaxLOB\Tax
Table- QuoteLine
ID InvoiceID
20370 5947
20369 5947
Table- QuotePremium
ID QuoteLineID Premium
19865 20370 1000
19864 20369 2000
Table- QuoteTaxPremium
ID QuotePremiumID TaxLineID
181069 19865 4298
181070 19864 4353
InvoiceLineItemTax.InvoiceLineItemID
50405
50405
InvoiceLineItemTax.TaxLOBID
4298
4353
Table- TaxLOB
ID TaxID
4298 54
4353 54
Tax.ID
54
54
Invoice.ID
5947
5947
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23QmpQJZ7FHA.3200@.TK2MSFTNGP11.phx.gbl...
> Mike
> If you show us a table structute with sample data we'd probably suggest
> something.
>
> In the first query you JOIN InvoiceID to ILI1.InvoiceId and here is I
> think a problem. Try to look at your tables more carefully to see what is
> going on when you're trying to JOIN them and to assign values.
>
> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
> news:OZtxqQY7FHA.3752@.tk2msftngp13.phx.gbl...
>|||Chris
Actually the IN was recently added in debugging and didn't affect anything.
It originally was '='
There are 2 records in the test data because there are 2 when the query is
run resulting in the
2 premium amounts (1,000 and 2,000). What I don't understand is why the SUM
is not totalling
no matter how many records there are. Perhaps I don't fully understand how
the function works.
My apologies for not following protocol and thank you for passing along that
link. I was reluctant to bother you with the entire script and was hoping I
had missed something fairly obvious. In order for me to replicate the data
set I would have to dump many tables and force you to to deal with FK
constraints. I was not aware you would be willing to deal with all that. I
am reviewing the link to see if I can properly ge the data togehter.
Many thanks for your time on this!!
Mike
"Chris2" <rainofsteel.NOTVALID@.GETRIDOF.luminousrain.com> wrote in message
news:372dnfkES4z6ih3enZ2dnUVZ_s6dnZ2d@.co
mcast.com...
> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
> news:OZtxqQY7FHA.3752@.tk2msftngp13.phx.gbl...
> totaling. Without
> seeing the
> obvious. I have
> grouping
> normally in the
> the outer
> query*/
> in(InvoiceLineItemTax.taxlobId))
> query are
> (QuoteTaxPremium.TaxLineID IN
> Office2.ID
> 0)
> (ILI1.LineItemAccountingType = 2)
> Mike Harbinger,
> Without a fully reproducible script to test with
> (http://www.aspfaq.com/etiquette.asp?id=5006), it is difficult to
> say for sure.
> However, I do spot something interesting.
> Query 1:
>
> This seems a bit unusual at first glance. Why specify IN (the
> shortcut for <this> OR <that> OR <the other> ) when there is but one
> column named? I would have thought it should just be:
> AND (QuoteTaxPremium.TaxLineID = InvoiceLineItemTax.taxlobId)
>
> And then I see:
> Query 2:
>
> This is supposed to be the equivalent of Query 1, only with literals
> assigned. Except that two literals are assigned in the IN() list.
> In Query 1, only 1 value will be present.
> This makes Query 1 slightly different from what is implied in the
> construction of the IN() in Query 2.
> Is it enough of a difference to be causing the problem in question?
> My apologies, but I can't say for sure.
>
> Sincererely,
> Chris O.
>|||"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:%23edXata7FHA.2012@.TK2MSFTNGP14.phx.gbl...
>
> "Chris2" <rainofsteel.NOTVALID@.GETRIDOF.luminousrain.com> wrote in
message
> news:372dnfkES4z6ih3enZ2dnUVZ_s6dnZ2d@.co
mcast.com...
<snip>
> Chris
> Actually the IN was recently added in debugging and didn't affect
anything.
> It originally was '='
> There are 2 records in the test data because there are 2 when the
query is
> run resulting in the
> 2 premium amounts (1,000 and 2,000). What I don't understand is
why the SUM
> is not totalling
> no matter how many records there are. Perhaps I don't fully
understand how
> the function works.
> My apologies for not following protocol and thank you for passing
along that
> link. I was reluctant to bother you with the entire script and was
hoping I
> had missed something fairly obvious. In order for me to replicate
the data
> set I would have to dump many tables and force you to to deal with
FK
> constraints. I was not aware you would be willing to deal with all
that. I
> am reviewing the link to see if I can properly ge the data
togehter.
> Many thanks for your time on this!!
> Mike
Mike,
Please don't hestitate to force me to deal with the necessities of
your database! :)
As for dumping several tables, we only need a few key rows pere
table, with the data embedded in INSERT statements.
With a script along the lines of . . .
CREATE TABLE . . .
CREATE TABLE . . .
INSERT . . .
INSERT . . .
EXPECTED RESULTS . . .
QUERY/STORED PROC/ETC. . . .
ACTUAL RESULTS . . .
DROP TABLE . . .
DROP TABLE . . .
Anyone here can copy and paste a script like the above into QA or
Express Manager (or even OSQL!), and away we go with trying to fix
your problem instead of trying to figure out the answers for any of
a number of intermediary questions that are necessary to reaching a
solution.
Sincerely,
Chris O.