I think that your only option is to reselect (forupdate) the header table inside the while block using the headerId that you've gotten from the lines table. Obviously you won't need the "forupdate" in your existing select if you do that.
Regards
Malcolm Burtt
-----Original Message-----
From: Stefan Osterburg [mailto:[EMAIL PROTECTED]
Sent: 28 September 2004 08:07
To: [EMAIL PROTECTED]
Subject: [development-axapta] Update in grouped sql query
Hi,
I have a problem with updating records in a grouped sql query:
I have to tables one "header"-table and one "line"-table.
As usual (e.g. in Salestable/Salesline) there can be several
records in the line table for one header record.
In both tables I have a Quantity field. Now I want to check,
if the sum of quantities in the tables lines is the same as
in the table header, if not I want to mark the table header,
to indicate the user that there is a problem.
I have the following query:
while select sum(Qty),headerid from linetable
group by headerid
join forupdate headertable
where linetable.headerid==headertable.headerid &&
linetable.Qty!=headertable.Qty
{
headertable.Checked=checkfailed;
headertable.update();
}
The query works so far as it correctly identifies all
records with differing quantities. However the update
fails because Axapta constructs the query in the
SQL-server such that the headertable is included in the
group by and therefore does not return a complete
record but something grouped.
Any ideas how I can restructure the query to do what I want?
Stefan Osterburg
Yahoo! Groups Sponsor
ADVERTISEMENT
<http://us.ard.yahoo.com/SIG=129qsitor/M=295196.4901138.6071305.3001176/D=groups/S=1705006764:HM/EXP=1096441789/A=2128215/R=0/SIG=10se96mf6/*http://companion.yahoo.com> click here
<http://us.adserver.yahoo.com/l?M=295196.4901138.6071305.3001176/D=groups/S=:HM/A=2128215/rand=955832240>
_____
Yahoo! Groups Links
* To visit your group on the web, go to:
http://groups.yahoo.com/group/development-axapta/
* To unsubscribe from this group, send an email to:
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
* Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service <http://docs.yahoo.com/info/terms/> .
[Non-text portions of this message have been removed]
| Yahoo! Groups Sponsor | |
|
|
Yahoo! Groups Links
- To visit your group on the web, go to:
http://groups.yahoo.com/group/development-axapta/
- To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
- Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
