Hello,
In the group by use just <field name>, and not <table name>.<field name>
Barry.
-----Original Message-----
From: pepejeans2k [mailto:[EMAIL PROTECTED]
Sent: Friday, 25 February 2005 6:16 PM
To: development-axapta@yahoogroups.com
Subject: [development-axapta] Re: What is the BEST practice using Class
with Sql Statement
Thanks for Replying,
I've tried to put something like this, but <group by _ProdBom.ItemId>
cause an error showing the table does not contain this field. Any idea
what is the correct syntax for below group by statement with index
point to ProdStatusIdx?
Select Sum(QtyBomCalc)
from _ProdBom
join tableId from _ProdTable
group by _ProdBom.ItemId
where _ProdBom.ItemId == _itemId
&& _ProdBom.ProdId == _ProdTable.ProdId
&& (_ProdTable.ProdStatus >= ProdStatus::Scheduled
&& _ProdTable.ProdStatus <= ProdStatus::StartedUp)
&& _ProdTable.ProdType == ProdType::Vendor;
I've put this in a class with a few function similar to this
statement, create a test report calling this class looping thru all
ItemId in InventTable, the task manager shown memory use by SQL Server
start from 24 Mb go up to > 800Mb and never come down even the report
is finish, close and exit from Axapta. The next time run the same
report again, memory go up starting from 800 Mb. Really appreciate if
someone can provide some guide.
--- In development-axapta@yahoogroups.com, Sonny Wibawa Adi
<[EMAIL PROTECTED]> wrote:
>
> Hi, Yong,
>
>
> I read your code and found a non stable result at line:
>
> from _ProdBom join _ProdTable
> group by ItemId
>
> Because, You want to sum ProdBom table, joined with ProdTable that
is grouped by ItemId, but you don't specify which prodTable.ItemId is.
So, I change the code into:
>
> join tableId from _ProdTable //group by ItemId -> which itemId ?
All ?
>
> If you want to sum the quantity for all prodTable which having
relation and conditions you have written (all filter except ItemId).
>
> So, if you want to speed up the query, this is my suggestion:
>
> Select Sum(QtyBomCalc) from _ProdBom
> where _ProdBom.ItemId == _itemId //probably you may add
ProdStatus filter (see: ProdStatusIdx)
> join tableId from _ProdTable //group by ItemId -> which itemId ?
All ?
> where _ProdBom.ProdId == _ProdTable.ProdId
> && _ProdTable.ProdStatus >= ProdStatus::Scheduled
> && _ProdTable.ProdStatus <= ProdStatus::StartedUp
> && _ProdTable.ProdType == ProdType::Vendor;
>
> For reducing memory usage, I still don't have a time to check that.
>
> Other suggestions may give a better result, though.
>
>
> Regards,
>
> Sonny Wibawa Adi
>
>
> pepejeans2k <[EMAIL PROTECTED]> wrote:
>
>
> Hi,
>
> I've created a static class that are reuseable in many reports. The
> problem is that it's slow and causing SQL to eat up a lot of physical
> memory.
>
> Can anyone of you pls take a look at these static class and advice
> what is the best practice to reduce the memory usage in SQL.
>
> Below is one of the example where I select all ItemId from Item
> Master, call below static class and pass in ItemId one by one as I
> need to display each item in rows.
>
>
> static InventQty GetExtWIPQty(ItemId _itemid)
> {
> ProdTable _ProdTable;
> ProdBom _ProdBom;
> ;
>
> Select ItemId, Sum(QtyBomCalc)
> from _ProdBom join _ProdTable
> group by ItemId
> where _ProdBom.ItemId == _itemId
> && _ProdBom.ProdId == _ProdTable.ProdId
> && (_ProdTable.ProdStatus == ProdStatus::Scheduled
> || _ProdTable.ProdStatus == ProdStatus::Released
> || _ProdTable.ProdStatus == ProdStatus::StartedUp)
> && _ProdTable.ProdType == ProdType::Vendor;
>
> return _ProdBom.QtyBOMCalc;
> }
>
> Thks in adv,
> Best rgs,
> Yong.
>
>
>
>
>
>
>
> Yahoo! Groups Sponsor
> Get unlimited calls to
>
> U.S./Canada
>
>
> ---------------------------------
> 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.
>
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>
> [Non-text portions of this message have been removed]
Yahoo! Groups Links
| 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.
