While select coding question

(0) Share

Report

Posted on by Community Member

Lets look at a table structure like this one:

Item	Type
111	A
111	B
222	A
222	B
222	C

What I am trying to achieve is that if in the table any ITEM has all the 3 TYPE records then let it stay - otherwise if it just has less than 3 table records delete all those.
Is there any easier way to achieve this except using multiple select statements.

Not looking for spoonfeeding, just a showing the right path would be sufficient.

thanks in advance.

*This post is locked for comments

I have the same question (0)

All responses (9)

Answers (0)

Martin Dráb 237,882 Most Valuable Professional on at

Like (1)

Report
You can find items with less then three types with an AOT query with HAVING clause. Pseudocode:

select t group by Item having count(Type) < 3

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks Martin.

InventTableModule inventmodule;

int counter;

;

counter = 0;

while select forUpdate count(ModuleType) from inventmodule

group by inventmodule.itemid

{

if(inventmodule.ModuleType < 3)

{

ttsbegin;

inventmodule.delete();

ttscommit;

counter ++;

info(inventmodule.itemid);

if(counter==3)

break;

}

}

Unable to delete??? am I doing something wrong? Thanks Please.

Was this reply helpful? Yes No
Dick Wenning 8,705 Moderator on at

Like (0)

Report

your select for update is outside your TTSscope

solution 1

remove the forupdate and add below

ttsbegin;

inventmodule.selectforupdate(true)

inventmodule.delete();

ttscommit;

scenario 2

add ttsbegin befor the selet and ttscomit on the end

Was this reply helpful? Yes No
Martin Dráb 237,882 Most Valuable Professional on at

Like (0)

Report

Trying to delete a grouped records doesn't make sense. This will give you ItemIds which modules you want to delete. Of course, using HAVING would give you much better performance.

By the way, why are you deleting InventTableModule records?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Tried both the solutions -but stil does not work/:

static void SK_UpdateInventTableModule(Args _args)

{

InventTableModule inventmodule;

int counter;

;

counter = 0;

while select count(ModuleType) from inventmodule

group by inventmodule.itemid

{

if(inventmodule.ModuleType < 3)

{

ttsbegin;

inventmodule.selectforupdate(true);

inventmodule.delete();

ttscommit;

counter ++;

info(inventmodule.itemid);

if(counter==3)

break;

}

}

}

same result.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I just tried to debug, and looks like the inventmodule is not picking up any records (recid=0) when we reach the ttsbegin/ttcommit statement. Any ideas?

Was this reply helpful? Yes No
Dick Wenning 8,705 Moderator on at

Like (0)

Report

sorry I realize you have a group by, so you can not do any update or delete, becourse it is aggregated data.

perhaps something like this

while select forUpdate inventmoduleA exists join inventmoduleB where

inventmoduleA.ItemId == inventmoduleB.ItemId &&

inventmoduleA.recid != inventmoduleB.recid

exists join inventmoduleC where

inventmoduleB.ItemId == inventmoduleC.ItemId &&

inventmoduleB.recid != inventmoduleC.recid &&

inventmoduleA.ItemId == inventmoduleC.ItemId &&

inventmoduleA.recid != inventmoduleC.recid

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

How can I achieve HAVING in X++?

Somebody 'tried' to push in some entries to the table - and the insert was partial and now we are trying to get rid of the partial/loose records.

Was this reply helpful? Yes No
Suggested answer

Martin Dráb 237,882 Most Valuable Professional on at

Like (0)

Report
%Your code should look like this:

InventTableModule moduleCounter; InventTableModule inventTableModule; while select count(ModuleType) from moduleCounter group by ItemId { if (inventmodule.ModuleType < 3) { delete_from inventTableModule where inventTableModule.ItemId == moduleCounter.ItemId; } }

The problem is that you have to fetch data for completely all items, even if you don't need 99% of them. If you used HAVING, the filtering by number of records would happen in already in database. Unfortunately I didn't notice that you have AX 2009, which doesn't suport HAVING, therefore you can use the better way only after you upgrade to a newer version.

Was this reply helpful? Yes No