Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / SysComputedColumn meth...
Finance | Project Operations, Human Resources, ...
Answered

SysComputedColumn methods - how to join 2 tables to a view and return a value

(0) ShareShare
ReportReport
Posted on by Lakshmi Karambakkam 1,289
Hi,
 
To write a view method, I have to join 2 tables to a view. I cannot create another view for 2 tables as the two tables are inventtrans and inventtransorigin.
 
I want to write methods to avoid performance issues.
 
How to write a SysComputedColumn method for this view?
 
Lakshmi
  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 230,569 Most Valuable Professional on at
    SysComputedColumn methods - how to join 2 tables to a view and return a value
    If you use group by, the only fields that will have a value defined are the fields you group by and those you aggregate (such as using count() function). Trying to use other fields is wrong.
     
    I guess you want to group by these fields too.
     
  • Lakshmi Karambakkam Profile Picture
    Lakshmi Karambakkam 1,289 on at
    SysComputedColumn methods - how to join 2 tables to a view and return a value
    Hi,

    My primary table is SalesPackingSlipTrackingInformation which has packingslip, trackingNumber and salesid.
     
    PackingSlipId TrackingNumber SalesID
    Pack1 Track1 SalesID1
    Pack2 Track1 SalesID1
    Pack3 Track2 SalesID1
    Pack4 Track3 SalesID1
    Pack5 Track4 SalesID2
    Pack6 Track5 SalesID2

    I created a  view to add InvoiceID and InvoiceDate fields for every record in SalesPackingSlipTrackingInformation table. I also have to add some fields from CustPackingSlipJour related toPackingSlipid.

    To add these fields, I created a view as sql query below.

    select SALESPACKINGSLIPTRACKINGINFORMATION.recid, SALESPACKINGSLIPTRACKINGINFORMATION.trackingnumber, inventtrans.packingslipid
    from SALESPACKINGSLIPTRACKINGINFORMATION 
     join custpackingslipjour 
    on SALESPACKINGSLIPTRACKINGINFORMATION.salesID = custpackingslipjour.SALESID
    and SALESPACKINGSLIPTRACKINGINFORMATION.PACKINGSLIPID = custpackingslipjour.PACKINGSLIPID
    and SALESPACKINGSLIPTRACKINGINFORMATION.DELIVERYDATE = custpackingslipjour.DELIVERYDATE
    left outer join inventtrans on SALESPACKINGSLIPTRACKINGINFORMATION.packingslipid = inventtrans.packingslipid
    left outer join RDSdeliveryTrackingtable on SALESPACKINGSLIPTRACKINGINFORMATION.TRACKINGNUMBER = RDSdeliveryTrackingtable.TRACKINGNUMBER
    and SALESPACKINGSLIPTRACKINGINFORMATION.RDSCARRIERCODE = RDSdeliveryTrackingtable.carriercode
    group by SALESPACKINGSLIPTRACKINGINFORMATION.recid, SALESPACKINGSLIPTRACKINGINFORMATION.trackingnumber, inventtrans.packingslipid

    The above query runs faster. But when I try to add other fields like InvoiceID and InvoiceDate, fields related to CustPackingSlipJour to View that are not part of group by fields, the view is throwing error that they are not part of group by fields.

    How to add non group by fields without performance issues?
     
     
    Lakshmi
  • Layan Jwei Profile Picture
    Layan Jwei 7,340 Super User 2024 Season 2 on at
    SysComputedColumn methods - how to join 2 tables to a view and return a value
    Hi Lakshmi,
     
    Open SQL server, expand AxDB node, expand views node, look for dbo.YourView. When you find it, right click on it and click view designer.
    Then take the SQL statement run it. Maybe also share it with us here to help you
     
    Thanks,
    Layan Jweihan
  • Lakshmi Karambakkam Profile Picture
    Lakshmi Karambakkam 1,289 on at
    SysComputedColumn methods - how to join 2 tables to a view and return a value
    Martin,
     
    Could you guide me the steps of how to open view definition in SQLserver management studio and tips that will help me to find errors and fix these relations?
     
    Lakshmi
  • Bharani Preetham Peraka Profile Picture
    Bharani Preetham Pe... 3,587 Super User 2024 Season 1 on at
    SysComputedColumn methods - how to join 2 tables to a view and return a value
    Your requirement is not very clear. If the form is having performance issues you can give some range before the form opens. But you missed giving us the complete requirement. InvenTrans and InventTransOrigin tables joining with a view doesn't give us full requirement. 
     
    As Martin suggested there can be some other reason like let's say some display methods or some other code in execute query or some database transaction related code. So please give us full requirement here.
  • Martin Dráb Profile Picture
    Martin Dráb 230,569 Most Valuable Professional on at
    SysComputedColumn methods - how to join 2 tables to a view and return a value
    You seem to be saying that you need InventTrans, that using InventTrans is too slow you solve the problem by using a computed column. But that seems to solve nothing; you'll still need InventTrans there.
     
    If the query is the same, it's irrelevant whether you run it in a view or in a computed column. And if you want to use a different query, then you can do it in a view as well. But in fact, doing it in a computed column would be worse for performance, because you'd end up with a subquery for each row instead of a simple join.
     
    Joining InventTrans and InventTransOrigin doesn't normally takes 30 minutes. Instead of just giving up, find the bug you have there. First of all, make sure that the relation is correct.
     
    If you aren't aware of it, note that you can open your view definition in SQL Server Management Studio and check whether SQL code there is what you want. You can also run the code and investigate the execution plan and you try making changes to the query and test them there.
  • Lakshmi Karambakkam Profile Picture
    Lakshmi Karambakkam 1,289 on at
    SysComputedColumn methods - how to join 2 tables to a view and return a value
    if another view is created using inventtrans tables and attached to the first view, the form is opening after 30 minutes. inventtrans is such a huge table, so there is a performance issue.
    To remove performance issue, I wanted to write view methods.
     
    What is the syntax for joining two tables using syscomputed columns?
  • Martin Dráb Profile Picture
    Martin Dráb 230,569 Most Valuable Professional on at
    SysComputedColumn methods - how to join 2 tables to a view and return a value
    Please tell us more about your requirement and the problem you're struggling with. Also, your statement that the tables are InventTrans and InventTransOrigin doesn't explain why you can't create a view.

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

Congratulations 2024 Spotlight Honorees

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December

Congratulations to our December super stars! 🥳

Start Your Super User Journey Pt 2

Join the ranks of our community heros! 🦹

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,883 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,569 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans