 |
BorlandTalk.com
Borland discussion newsgroups
|
| View previous topic :: View next topic |
| Author |
Message |
aj
Guest
|
 Posted: Mon Apr 19, 2004 7:51 pm Post subject: Re: NestedSelectHelp |
 |
|
Mike:
Each record in the T2, for a pcode, can have a different cost. The sql
seems to be summing incorrectly. I have a pcode of B-345 that has a
total received of 273 in T2 and QtyIssd of 182.
When I run the SQL it shows a QtyRcvd of 16883 QtyIssd of 182(this is
correct) and QtyRemaining of 16701.
What would cause the QtyRcvd and QtyRemaining to be incorrect?
Thanks
Mike Walsh wrote:
| Quote: | "aj" <aj (AT) netscape (DOT) net> wrote in message
news:4062dd36 (AT) newsgroups (DOT) borland.com...
Mike you had the relationships correct. I have the code running
Assuming that the code I gave you is close....
The statement I gave you was
select
PCode,
Recvd = sum (T2.QtyRcvd), -- May not be needed
Issd = sum (T3.QtyIssd), -- May not be needed
QtyRemaining = isnull (sum (T2.QtyRcvd), 0) - isnull (sum (T3.QtyIssd), 0)
from T1, T2
left outer join T3 on (T3.RcvdID = T2.RcvdID)
where (T2.PCode = T1.PCode) and (t2.DateRcvd < :inDate) and (T1.Inv = 1)
group by PCode
This query takes the record from T2 where the items were received and
matches them up with the items issued. The cost that is in the T2 record
should match up nicely. If we add some lines that may show you what I
mean...
select
PCode,
Recvd = sum (T2.QtyRcvd), -- May not be needed
Issd = sum (T3.QtyIssd), -- May not be needed
QtyRemaining = isnull (sum (T2.QtyRcvd), 0) - isnull (sum (T3.QtyIssd),
0),
Cost = sum (isnull (T2.UnitCost, 0) * isnull (T2.QtyRcvd, 0)),
IssdAmt = sum (isnull (T2.UnitCost, 0) * isnull (T3.QtIissd, 0))
from T1, T2
left outer join T3 on (T3.RcvdID = T2.RcvdID)
where (T2.PCode = T1.PCode) and (t2.DateRcvd < :inDate) and (T1.Inv = 1)
group by PCode
This should give you the Cost spent on the items, and the amount of money
for the issued items (if the unit cost is used for this factoring. If its
not, I don't know where the price comes from, maybe T3.UnitPrice?)
Mike
|
|
|
| Back to top |
|
 |
Mike Walsh
Guest
|
| Posted: Tue Apr 20, 2004 12:21 pm Post subject: Re: NestedSelectHelp |
|
|
"aj" <aj (AT) netscape (DOT) net> wrote
| Quote: | Mike:
Each record in the T2, for a pcode, can have a different cost. The sql
seems to be summing incorrectly. I have a pcode of B-345 that has a
total received of 273 in T2 and QtyIssd of 182.
When I run the SQL it shows a QtyRcvd of 16883 QtyIssd of 182(this is
correct) and QtyRemaining of 16701.
What would cause the QtyRcvd and QtyRemaining to be incorrect?
Thanks
|
aj,
Somehow, we're getting too many records from the T2 join. Either other
records are joined, or we have some records joined multiple times. Try
running your query in the query analyzer. This time, change the select set
to be single fields, (not sums) and remove the group by clause. This should
give you the records that the current query is looking at to generate the
results. With a difference of that magnitude of records, I think it will be
obvious which records are causing the problem. Then its a simple matter to
change the where clause to eliminate the problem.
Sorry I can't be exact, but I don't have your data here so I'm still mostly
guessing about some of it.
Mike Walsh
|
|
| Back to top |
|
|
aj
Guest
|
| Posted: Tue Apr 20, 2004 3:31 pm Post subject: Re: NestedSelectHelp |
|
|
Mike:
I know what is happeniong now, just not sure how to correct the sql.
For every record in T2 there can be multiple recoprds in T3.
t2
RcvdID PCode QtyRcvd Cost DateRcvd
1 123 5 5.00 01/01/2004
2 234 10 8.00 01/09/2004
3 123 20 7.00 01/09/2004
t3
IssdID RcvdID QtyIssd DateIssd
1 1 4 01/10/2004
2 2 5 01/10/2004
3 2 2 01/10/2004
4 2 -3 01/10/2004
5 1 10 01/11/2004
6 1 5 01/12/2004
results expected if recvddate <= 01/11/2004
For PCode = 123 QtyRemain = 6 TotalCost = 42.00
For PCode = 234 QtyRemain = 6 TotalCost = 48.00
results I am getting
123 5 5.00
123 5 5.00
123 20 7.00
234 10 8.00
234 10 8.00
234 10 8.00
For PCode = 123 QtyRemain = 30
For PCode = 234 QtyRemain = 30
What is happening is the result set is returning a record for each
issueid. If PCode 123 has 3 records in t2 and in t3 there are 4 records
for each one in t2, then it is summing the cost from the record in t2
multiple times. Does this help make it clear?
Thanks
Mike Walsh wrote:
| Quote: | "aj"
news:40842eed$1 (AT) newsgroups (DOT) borland.com...
Mike:
Each record in the T2, for a pcode, can have a different cost. The sql
seems to be summing incorrectly. I have a pcode of B-345 that has a
total received of 273 in T2 and QtyIssd of 182.
When I run the SQL it shows a QtyRcvd of 16883 QtyIssd of 182(this is
correct) and QtyRemaining of 16701.
What would cause the QtyRcvd and QtyRemaining to be incorrect?
Thanks
aj,
Somehow, we're getting too many records from the T2 join. Either other
records are joined, or we have some records joined multiple times. Try
running your query in the query analyzer. This time, change the select set
to be single fields, (not sums) and remove the group by clause. This should
give you the records that the current query is looking at to generate the
results. With a difference of that magnitude of records, I think it will be
obvious which records are causing the problem. Then its a simple matter to
change the where clause to eliminate the problem.
Sorry I can't be exact, but I don't have your data here so I'm still mostly
guessing about some of it.
Mike Walsh
|
|
|
| Back to top |
|
|
Mike Walsh
Guest
|
| Posted: Tue Apr 20, 2004 8:51 pm Post subject: Re: NestedSelectHelp |
|
|
| Quote: |
What is happening is the result set is returning a record for each
issueid. If PCode 123 has 3 records in t2 and in t3 there are 4 records
for each one in t2, then it is summing the cost from the record in t2
multiple times. Does this help make it clear?
|
aj,
I took the information you provided, and created a test db with the three T
tables. I then worked out a query that seems to fit your bill. I't kind of
ugly, but I think it works.
There is a minor problem with it though. The problem isn't anything in the
query itself, but in the ado components. I use the :inDate parameter a
couple of times. With the ADO components, for each instance a you get a
different parameter object. Personally, I find this annoying, and believe
it's a bug, but Microsoft seems to have designed it this way. Some posts in this
group suggest setting the value of each of the parameter objects, but I find
that too eaasy to forget one, especially if you modify the query in the
future. What I found to work around the 'feature' is to use a sql variable
and assign it the value of the parameter. In this way, the parameter is only
used once and you can use the parameters.paramvalues method to set it. So,
here's a query. Let me know if it works.
declare
@InDate datetime
set @InDate = :InDate
select
T1.PCode,
Recvd = (select sum (isnull (T2.QtyRcvd, 0)) from T2 where (T2.PCode =
T1.PCode) and (T2.DateRcvd <= @InDate)),
Issd = (
select
sum (isnull (T3.QtyIssd, 0))
from T3
where (T3.RcvdId in (
select
RcvdId
from T2 where (T2.PCode = T1.PCode) and (T2.DateRcvd <= @InDate) and
(T1.Inv = 1)
))),
Remaining =
(select sum (isnull (T2.QtyRcvd, 0)) from T2 where (T2.PCode = T1.PCode)
and (T2.DateRcvd <= @InDate)) -
(select
sum (isnull (T3.QtyIssd, 0))
from T3
where (T3.RcvdId in (
select
RcvdId
from T2 where (T2.PCode = T1.PCode) and (T2.DateRcvd <= @InDate) and
(T1.Inv = 1)
)))
from T1
where (T1.PCode in (
select PCode from T2 where (T2.PCode = T1.PCode) and (T2.DateRcvd <=
@InDate) and (T1.Inv = 1)
))
Mike
|
|
| Back to top |
|
|
aj
Guest
|
| Posted: Wed Apr 21, 2004 2:57 pm Post subject: Re: NestedSelectHelp |
|
|
Mike:
I have to run some more dates with it, but it looks correct. I have the
unit_cost field in t2 that I need to return and group the entire result
set by. Is this the proper place to do it:
select
| Quote: | T1.PCode,
T2.Unit_cost |
...
...
from T1
| Quote: | where (T1.PCode in (
select PCode from T2 where (T2.PCode = T1.PCode) and (T2.DateRcvd <=
@InDate) and (T1.Inv = 1)
))
Group By T2.Unit_Cost |
TIA
Mike Walsh wrote:
| Quote: | What is happening is the result set is returning a record for each
issueid. If PCode 123 has 3 records in t2 and in t3 there are 4 records
for each one in t2, then it is summing the cost from the record in t2
multiple times. Does this help make it clear?
aj,
I took the information you provided, and created a test db with the three T
tables. I then worked out a query that seems to fit your bill. I't kind of
ugly, but I think it works.
There is a minor problem with it though. The problem isn't anything in the
query itself, but in the ado components. I use the :inDate parameter a
couple of times. With the ADO components, for each instance a you get a
different parameter object. Personally, I find this annoying, and believe
it's a bug, but Microsoft seems to have designed it this way. Some posts in this
group suggest setting the value of each of the parameter objects, but I find
that too eaasy to forget one, especially if you modify the query in the
future. What I found to work around the 'feature' is to use a sql variable
and assign it the value of the parameter. In this way, the parameter is only
used once and you can use the parameters.paramvalues method to set it. So,
here's a query. Let me know if it works.
declare
@InDate datetime
set @InDate = :InDate
select
T1.PCode,
Recvd = (select sum (isnull (T2.QtyRcvd, 0)) from T2 where (T2.PCode =
T1.PCode) and (T2.DateRcvd <= @InDate)),
Issd = (
select
sum (isnull (T3.QtyIssd, 0))
from T3
where (T3.RcvdId in (
select
RcvdId
from T2 where (T2.PCode = T1.PCode) and (T2.DateRcvd <= @InDate) and
(T1.Inv = 1)
))),
Remaining =
(select sum (isnull (T2.QtyRcvd, 0)) from T2 where (T2.PCode = T1.PCode)
and (T2.DateRcvd <= @InDate)) -
(select
sum (isnull (T3.QtyIssd, 0))
from T3
where (T3.RcvdId in (
select
RcvdId
from T2 where (T2.PCode = T1.PCode) and (T2.DateRcvd <= @InDate) and
(T1.Inv = 1)
)))
from T1
where (T1.PCode in (
select PCode from T2 where (T2.PCode = T1.PCode) and (T2.DateRcvd <=
@InDate) and (T1.Inv = 1)
))
Mike
|
|
|
| Back to top |
|
|
Mike Walsh
Guest
|
| Posted: Wed Apr 21, 2004 8:10 pm Post subject: Re: NestedSelectHelp |
|
|
"aj" <aj (AT) netscape (DOT) net> wrote
| Quote: | Mike:
I have to run some more dates with it, but it looks correct. I have the
unit_cost field in t2 that I need to return and group the entire result
set by. Is this the proper place to do it:
select
T1.PCode,
T2.Unit_cost
...
...
from T1
where (T1.PCode in (
select PCode from T2 where (T2.PCode = T1.PCode) and (T2.DateRcvd <=
@InDate) and (T1.Inv = 1)
))
Group By T2.Unit_Cost
TIA
|
aj,
Nope, that won't work. First, this query doesn't include the T2 table in the
main body, second simply adding T2 to the main query will re-introduce the
issue of adding too many records, and third, if we include the group by in
the query, we'll get all sorts of errors due to other fields not being
aggregate functions. Basically, when you use a group by statement, you have
to group by all of the returned fields that don't perform some sort of
aggregate function, like sum, avg, max, min, etc...
The way that this query is structured, you'll get a single PCode record from
the T1 table. Linked to it are the quantity of that PCode issued and
received. I'm not sure how you want to factor in the UnitCost, since a
single T1 record could have several T2 records with it. Or, do you mean that
you want a returned record for each T2 record, not just each T1 one?
Mike
|
|
| Back to top |
|
|
Mike Walsh
Guest
|
| Posted: Wed Apr 21, 2004 8:28 pm Post subject: Re: NestedSelectHelp |
|
|
aj,
After I sent that other post, I thought about it a bit more, and I think you
can add a couple more subqueries to the original query. In the select
statement of the post I posted yesterday, add these two. I think they are
right.
Cost = (select sum (isnull (T2.QtyRcvd, 0) * isnull (T2.UnitCost, 0)) from
T2 where (T2.PCode = T1.PCode) and (T2.DateRcvd <= @InDate)),
RemainCost =
(select sum (isnull (T2.QtyRcvd, 0) * isnull (T2.UnitCost, 0)) from T2
where (T2.PCode = T1.PCode) and (T2.DateRcvd <= @InDate)) -
(select
sum (isnull (T3.QtyIssd, 0) * isnull (T2.UnitCost, 0))
from T3, T2
where (T2.RcvdID = T3.RcvdID) and (T3.RcvdId in (
select
RcvdId
from T2 where (T2.PCode = T1.PCode) and (T2.DateRcvd <= @InDate) and
(T1.Inv = 1)
)))
The only problem I see at the moment is with the data you listed previously.
According to my understanding...
T1 is a list of the products.
T2 is a list of the shipments received for various products contained in T1.
T3 shows the products being issued, and tracks back to the original
receiving record from T2.
If this is correct, your data indicates that for RcvdId 1 you received 5
items, but issued 19 of them in IssdId's 1, 5 and 6. This could cause some
odd numbers in your results.
BTW - in my queries, I'm using the isnull function a lot. If you're sure
that the fields referenced can never be null, you could remove them from the
statements. Personally I like having them there just in case...
Mike
|
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
