web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Number of records
Microsoft Dynamics AX (Archived)

Number of records

(0) ShareShare
ReportReport
Posted on by Senthil Yadav 492

Dear All,

 

                 I am in need of a query from which i will get the count of distinct Production order present in a table, i have written a query as mentioned:

static void no_of_JO(Args _args)
{
WrkCtrUtilizationTable   wrkCtrUtilization_New;

    select Count(ProductionId) from wrkCtrUtilization_New
        index hint WrkCtrGroupIdx  group by wrkCtrUtilization_New.ProductionId
              where wrkCtrUtilization_New.WrkCtrGroupId == 'COILING' &&
              wrkCtrUtilization_New.WrkCtrId == 'C101' &&
              wrkCtrUtilization_New.TransDate >= str2date("01-Jul-2012",123) &&
              wrkCtrUtilization_New.TransDate <= str2date("16-Jul-2012",123) &&
              wrkCtrUtilization_New.ProductionId != ' ';

    info(strfmt("%1",wrkCtrUtilization_New.ProductionId));
}

but this query is generating wrong output, it displays the number of records present against the Production order i.e if Prod_00001 and Prod_00002  are repeated 10 and 20 times respectively then is generating output as 10, where as it should generate the output as 2.

Please suggest me the proper query to generate the output.

Thanks all..

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 237,880 Most Valuable Professional on at

    Your expectations about the query are wrong. The query returns WrkCtrUtilizationTable records grouped by ProductionId, i.e. one record for each ProductionId. For each returned record, the number of records before grouping is calculated and returned in ProductionId field.

    In you example, the first record represents Prod_00001. The second record (not obtained by your script) represents 00002 and would return 20.

    You could use count+distinct In "normal" SQL, unfortunately it's not supported by X++, so you have to use some workaround. For example, you could simply count number of records returned by your query. Or you could select it as "select count(ProductionId) from prodTable exists join wrkCtrUtilization_New where...".

  • Senthil Yadav Profile Picture
    Senthil Yadav 492 on at

    TransDate

    ItemId

    ProducedQty

    ProductionId

    10-Jul-12

    2633

    0

    PRD11-12_005877

    10-Jul-12

    2633

    7291

    PRD11-12_005877

    14-Jul-12

    2599

    400

    PRD12-13_001751

    15-Jul-12

    2599

    0

    PRD12-13_001751

    16-Jul-12

    2599

    3918

    PRD12-13_001751

    03-Jul-12

    5104

    0

    PRD12-13_003479

    04-Jul-12

    5104

    0

    PRD12-13_003479

    07-Jul-12

    5104

    976

    PRD12-13_003479

    09-Jul-12

    4382

    2140

    PRD12-13_004160

    10-Jul-12

    4382

    7690

    PRD12-13_004160

    05-Jul-12

    3991

    3700

    PRD12-13_004444

    07-Jul-12

    3991

    9000

    PRD12-13_004444

    03-Jul-12

    5201

    0

    PRD12-13_004447

    03-Jul-12

    5201

    0

    PRD12-13_004447

    04-Jul-12

    5201

    9000

    PRD12-13_004447

     OK. thank you sir.

    I tried many ways but unable to build query. So can you please help me out in this by suggesting the query.

    Now by considering the above table WrkCtrUtilizationTable, I want output as No. Of work orders = 6

    for particular item No. and in between specified duration.

    So please tell me what query I should write for getting this output.

     

    Thanks in advance.

     

     

     

  • Suggested answer
    dolee Profile Picture
    dolee 11,279 on at

    What about create a View for your WrkCtrUtilizationTable which grouped by ProductionId.

    Then in your query you just need to count the number of records in this View.

    SELECT COUNT(ProductionID) FROM <myNewView>

    (P.S. Another way would be simply use a while select and count the number of records returned. However, I take that you probably thought of this but don't want to do it this way...)

  • Martin Dráb Profile Picture
    Martin Dráb 237,880 Most Valuable Professional on at

    Senthil, what approach have you decided to use? The one with exists join? What part of it is unclear?

  • Senthil Yadav Profile Picture
    Senthil Yadav 492 on at

    Hi Sir,

                  All the required fields are present in the same table, what query should i write to fetch the records as per my requirement, can you give me the query please.

     

  • Suggested answer
    Malcolm Burtt Profile Picture
    Malcolm Burtt 1,475 on at

    Hi

    Select count() shoudl always be done against the recId field, so your code should look like (changes on your original highlighted)....

    static void no_of_JO(Args _args)
    {
    WrkCtrUtilizationTable wrkCtrUtilization_New;

    select Count(recId) from wrkCtrUtilization_New
    index hint WrkCtrGroupIdx                      //group by wrkCtrUtilization_New.ProductionId
    where wrkCtrUtilization_New.WrkCtrGroupId == 'COILING' &&
    wrkCtrUtilization_New.WrkCtrId == 'C101' &&
    wrkCtrUtilization_New.TransDate >= str2date("01-Jul-2012",123) &&
    wrkCtrUtilization_New.TransDate <= str2date("16-Jul-2012",123) &&
    wrkCtrUtilization_New.ProductionId != ' ';

    info(strfmt("%1",wrkCtrUtilization_New.RecId));
    }

    Regards

    Malcolm

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#1
Priya_K Profile Picture

Priya_K 4

#3
MyDynamicsNAV Profile Picture

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans