ISSUES TRYING TO JOIN CTE's

  • Ysa

    Mr or Mrs. 500

    Points: 522

    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.
  • Site Owners

    SSC Guru

    Points: 80385

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

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

  • Jeff Moden

    SSC Guru

    Points: 994516

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

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

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