Post reply

ISSUES TRYING TO JOIN CTE's

  • June 13, 2019 at 12:53 am

    #3653021

    Hello there!!

    I have the CTEs but is not working take for ever please I need help with this, I am new in this

    The gl_account has the name of the accounts and department (use for filters)

    The gl_detail has the account and detail information

    invoices have all information of the invoices posted and unposted (booth needed), also the vendor id

    vendor information linked to invoices to vendor id.

    I need to have  a report where I can filter by Org and Object and year which will bring posted and unposted invoices

     

    Select gl_detail.a_org, gl_detail.a_object, gl_detail.j_ref1_vendor,gl_detail.a_journal_number,gl_detail.j_credit_amount, gl_detail.j_debit_amount

    from gl_detail

    INNER JOIN gl_accounts ON gl_detail.a_org =gl_accounts.a_org andgl_detail.a_object=gl_accounts.a_object

    Select distinct Invoices.VendorId, Vendors.Id, Vendors.VendorNumber, Invoices.IsPosted,

    Invoices.CheckNumber,Invoices.InvoiceTotal,invoices.JournalYear

    from Invoices

    INNER JOIN Vendors ON Invoices.VendorId=Vendors.Id

     

    When I run the select by itself work but my join dont work

    Attachments:
    You must be logged in to view attached files.

  • June 14, 2019 at 1:10 am

    #3653609

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • June 14, 2019 at 11:48 pm

    #3654027

    A lot of people make the mistake of thinking that a CTE is rendered as a separate object.  Unless there is a "blocking operator" in the CTE, it's treated like any view or "derived table" in a FROM clause would be treated.  It is frequently better to materialize the results of a CTE as a Temp Table before trying to do any JOINs.  It's frequently referred to as the "Divide'n'Conquer" method.

    DBAs that don't allow such usage of TempDB sometimes don't understand that all that's happening with the original query may be overwhelming TempDB with "Eager Spools" or rather insane usage of "Lazy Spools".  Using a Temp Table in place of the CTE you want to join to can totally eliminate such spooling.

    As with all else in SQL Server, "It depends".  Give it a try and see what happens.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply