locked
Helping in join RRS feed

  • Question

  • Hi guys, is there any way to avoid using a cte for the join below? I've got month in this format 12/2016 and nmonth in this other 201612...the link below retrieve error in t.right...

    from

    cte2c2leftjoin Traint


    on

    c2.[Contract]=t.[contract]andc2.Nmonth=t.right([month],4)+left([month],2)

    Thanks

    Monday, October 10, 2016 9:47 AM

Answers

  • Why your cte has nMonth in a different format than [month] column in Train? Since you're creating the cte, what stops you from creating xMonth in the same format that month column in Train? It is better to not use expressions for Train table when joining.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by DIEGOCTN Monday, October 10, 2016 12:06 PM
    Monday, October 10, 2016 11:57 AM

All replies

  • And c2.Nmonth contains such values as well  201612? or 20161201

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, October 10, 2016 9:54 AM
    Answerer
  • Your question is unclear. Also, if you need to join using cte when why not create month in correct format in the cte?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, October 10, 2016 9:55 AM
  • We cannot tell this without seeing the complete statement first.

    What data type has nmonth? [N]VARCHAR?

    What is month a [N]VARCHAR?

    Monday, October 10, 2016 9:59 AM
  • Both varchar : nmonth 201612  [month] 12/2016

    nmonth from cte, [month] from Train . I can do right([month],4)+left([month],2) to have 201612 but I can't use in my join. My attempt is to do not create another cte, I can but I'd prefer to don't...

    Monday, October 10, 2016 10:45 AM
  • Perhaps this

    c2.[Contract]=t.[contract]

    andc2.Nmonth=right([t.month],4)+left([t.month],2)


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, October 10, 2016 10:47 AM
    Answerer
  • You can use expressions in a JOIN condition. Why do you think you cannot use it?

    And for the CTE part: Post your query.

    Monday, October 10, 2016 11:27 AM
  • Why your cte has nMonth in a different format than [month] column in Train? Since you're creating the cte, what stops you from creating xMonth in the same format that month column in Train? It is better to not use expressions for Train table when joining.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by DIEGOCTN Monday, October 10, 2016 12:06 PM
    Monday, October 10, 2016 11:57 AM
  • Good point Naomi. I do not like but I had to reverse the 201612 to 12/2016 in the cte. Now I am going to reverse it in the last query. I thought it could have been something smoother.

    Thanks

    Monday, October 10, 2016 12:06 PM
  • Stephan I do use expression in the Join but the one above didn't work.
    Monday, October 10, 2016 12:07 PM