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) / Query within query
Microsoft Dynamics AX (Archived)

Query within query

(0) ShareShare
ReportReport
Posted on by nimi 611

Hello

I have query like this

select * from table1,table2  where table1.id == table2.id

&& table1.noofamt >= (select Noofamt from table3 where table3.id == table1.id group by id)

How to write this query in Dynamcis Ax using querybuilddatasource?

 

Thanks in Advance

Nimisha Patel

 

 

 

*This post is locked for comments

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

    Subqueries are not supported in X++ (although exists join can be translated to subqueries). You have to rewrite the query to use joins or to solve it by application logic.

    Currently I don't understand what do you want to achieve by your subquery, I would expect using of an aggregate function or so.

  • Steve Jung Profile Picture
    Steve Jung 355 on at

    I'm trying to use subselects to return the dimension name values in one row.  Since the dimension values are segregated by the ordinal value in teh dimensionattributelevelvalue table I need to subselect from there.  How do I build a query or view in the AOT to do this.  My SQL looks like this.   Your above answer seems to suggest you don't think subselects are ever necessary.  If you don;t use subselects to get these values then how would you do it?

    Select year(ledg.transdate) Fiscal_Year,

          month(ledg.transdate) Accounting_Period,

          (Select acct.mainaccountid

             From mainaccount acct,

                  dimensionattributelevelvalue dalv

             Where dalv.dimensionattributevaluegroup = davg.recid

               And acct.MainAccountID = dalv.displayvalue

               And dalv.ordinal = 1) AccountID,

          (Select acct.name

             From mainaccount acct,

                  dimensionattributelevelvalue dalv

             Where dalv.dimensionattributevaluegroup = davg.recid

               And acct.MainAccountID = dalv.displayvalue

               And dalv.ordinal = 1) AccountName,

          (Select oper.omoperatingunitnumber

             From omoperatingunit oper,

                  dimensionattributelevelvalue dalv

             Where dalv.dimensionattributevaluegroup = davg.recid  

               And dalv.ordinal = 2  

               And oper.omoperatingunitnumber = dalv.displayvalue

               And oper.omoperatingunittype = 1) DeptID,

          (Select dpty.name

             From omoperatingunit oper,

                  dirpartytable dpty,

                  dimensionattributelevelvalue dalv

             Where dalv.dimensionattributevaluegroup = davg.recid  

               And dalv.ordinal = 2  

               And oper.omoperatingunitnumber = dalv.displayvalue

               And oper.omoperatingunittype = 1

               And dpty.recid = oper.recid) DeptName,

          (Select dmft.Description

             From dimensionfinancialtag dmft,

                  dimensionattributelevelvalue dalv

              Where dalv.dimensionattributevaluegroup = davg.recid  

                    And dalv.ordinal = 3

                    And dmft.value = dalv.displayvalue

                    And dmft.FinancialTagCategory = 5637144577) Product,

           (Select dmft.Description

             From dimensionfinancialtag dmft,

                  dimensionattributelevelvalue dalv

              Where dalv.dimensionattributevaluegroup = davg.recid  

                    And dalv.ordinal = 4

                    And dmft.value = dalv.displayvalue

                    And dmft.FinancialTagCategory = 5637144576) Project,

          amountcurcredit credit_amount,

          amountcurdebit debit_amount

      From ledgerjournaltrans ledg

      CROSS JOIN dimensionattributevaluegroupcombination dvgc

      CROSS JOIN dimensionattributevaluegroup davg

    WHERE dvgc.dimensionattributevaluecombination = ledg.ledgerdimension  

      And davg.recid = dvgc.dimensionattributevaluegroup  

      And Not (Select acct.mainaccountid

                 From mainaccount acct,

                      dimensionattributelevelvalue dalv

                 Where dalv.dimensionattributevaluegroup = davg.recid

                   And acct.MainAccountID = dalv.displayvalue

                   And dalv.ordinal = 1) is null

    Union

    Select year(ledg.transdate) Fiscal_Year,

          month(ledg.transdate) Accounting_Period,

          (Select acct.mainaccountid

             From mainaccount acct,

                  dimensionattributelevelvalue dalv

             Where dalv.dimensionattributevaluegroup = davg.recid

               And acct.MainAccountID = dalv.displayvalue

               And dalv.ordinal = 1) AccountID,

          (Select acct.name

             From mainaccount acct,

                  dimensionattributelevelvalue dalv

             Where dalv.dimensionattributevaluegroup = davg.recid

               And acct.MainAccountID = dalv.displayvalue

               And dalv.ordinal = 1) AccountName,

          (Select oper.omoperatingunitnumber

             From omoperatingunit oper,

                  dimensionattributelevelvalue dalv

             Where dalv.dimensionattributevaluegroup = davg.recid  

               And dalv.ordinal = 2  

               And oper.omoperatingunitnumber = dalv.displayvalueOM

               And oper.omoperatingunittype = 1) DeptID,

          (Select dpty.name

             From omoperatingunit oper,

                  dirpartytable dpty,

                  dimensionattributelevelvalue dalv

             Where dalv.dimensionattributevaluegroup = davg.recid  

               And dalv.ordinal = 2  

               And oper.omoperatingunitnumber = dalv.displayvalue

               And oper.omoperatingunittype = 1

               And dpty.recid = oper.recid) DeptName,

          (Select dmft.Description

             From dimensionfinancialtag dmft,

                  dimensionattributelevelvalue dalv

              Where dalv.dimensionattributevaluegroup = davg.recid  

                    And dalv.ordinal = 3

                    And dmft.value = dalv.displayvalue

                    And dmft.FinancialTagCategory = 5637144577) Product,

           (Select dmft.Description

             From dimensionfinancialtag dmft,

                  dimensionattributelevelvalue dalv

              Where dalv.dimensionattributevaluegroup = davg.recid  

                    And dalv.ordinal = 4

                    And dmft.value = dalv.displayvalue

                    And dmft.FinancialTagCategory = 5637144576) Project,

          amountcurcredit credit_amount,

          amountcurdebit debit_amount

      From ledgerjournaltrans ledg

      CROSS JOIN dimensionattributevaluegroupcombination dvgc

      CROSS JOIN dimensionattributevaluegroup davg

    WHERE dvgc.dimensionattributevaluecombination = ledg.offsetledgerdimension  

      And davg.recid = dvgc.dimensionattributevaluegroup  

      And (Select acct.mainaccountid

             From mainaccount acct,

                  dimensionattributelevelvalue dalv,

                  dimensionattributevaluegroup davg2,

                  dimensionattributevaluegroupcombination davc2    

             Where davc2.dimensionattributevaluecombination = ledg.ledgerdimension  

               And davg2.recid = davc2.dimensionattributevaluegroup

               And dalv.dimensionattributevaluegroup = davg2.recid

               And acct.MainAccountID = dalv.displayvalue

               And dalv.ordinal = 1) is null              

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

    I didn't discuss whether they are necessary or not, I said that they are not supported. You either have to find some workaround or give it up. Classic workarounds include rewriting subqueries to joins and often - unfortunately - processing some parts in application layer (if no better solution exists).

    If you're happy with using views and you have AX2012, you can write subqueries as computed columns (I tried it and it really works). It's a bit tricky feature and you should use it carefully, but it may be the best solution is some cases. You would need computed columns for year() and month() functions too, although this is exactly something what you can easily handle in the application.

  • Steve Jung Profile Picture
    Steve Jung 355 on at

    Can you share an example of a subquery as a computed column?   That would be helpful.  Also, what about using embedded SQL in a Report Data Provider Class?  I'm trying that now but I'm getting a Sys Global Cache Object not initialized error when I try to use it as the data source for a report.  I haven't been able to find information on what is causing that.   Thank you.

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

    I've just written a blog post with an example. You can also find some information on MSDN.

    This thread was originally about subqueries in the Query framework. It will be better if you describe your attempts and troubles with reporting (in detail) in a new thread.

  • Steve Jung Profile Picture
    Steve Jung 355 on at

    That's working great.  Thank you.   Is it possible to use a class to define a range or to filter data for a view in another way using a class?  I haven't had much luck finding this level of detail on MSDN.  Can you suggest where I would look there for information regarding using classes to do these sorts of things?

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