Help on Select "Unique"

DistinctDistinct Member Posts: 2
edited 2007-11-27 in Dynamics AX
Hi

I’m not really a Axapta developer (you’ve been warned)

My problem is related to the fact that Axapta SQL doesn’t support “Select distinct”.

I have two tables:

1: ProjJournalTrans which contains (among others) the fields; emplid, qty, categoryid, projid, projtransdate

2. Empltable with the field emplid (and others..)

A dialog asks for
Projid
FromDate
Todate

Based on these inputs I would like to print a report which does the following:

1. select all distinct emplid where the projid is equal to dialog(projid)
2. Run a sum(qty) for each emplid regardless of projid based on the date interval set in the fromdate and todate.


public boolean fetch()
{
QueryRun    qr;

qr = new QueryRun(this);
  if(qr.prompt())
  {
    while (qr.next())
    {

       Empltable = qr.get(tablenum(empltable));


While Select * from Empltable
{
While select emplid, projid, projtransdate from ProjJournalTrans
Where ProjJournalTrans.projid == dialog_Projid
&& ProjJournlTrans.emplid == empltable.emplid
&& ProjJournaltrans.Projtransdate <= Todate
&& ProjJournaltrans.Projtransdate => Fromdate
	{
		While select sum(qty) from projJournaltrans 
		Where Emptable.emplid == ProjJournaltrans.emplid
			{
				No clue as to what goes here
			}

}


}
Where empltable.emplid = proJournalid.emp



if (!this.send(empltable))
               {
            return true;
        }
           }

  }

    return true;
}

Am I anyway near the solution?
:?:
Sign In or Register to comment.