• Visitors can check out the Forum FAQ by clicking this link. You have to register before you can post: click the REGISTER link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. View our Forum Privacy Policy.

SQL join query

Collapse
X
  •  
  • Filter
  • Time
  • Show
Clear All
new posts

    #11
    Originally posted by Cirrus View Post
    There are only two things that might indicate there is a God.

    The first is your liver is six times as big as it needs to be.

    The second is MS Access.
    It is just some static data I need to load into a new system and update every 6 months or so.

    As I currently do not use Access not thinking there is any point bringing another system into the mix.....

    Excel lookups it is although I will play around with CAST if I get the chance.

    Comment


      #12
      Are you 100% the VARCHAR field contains valid numbers? As well as looking at the CASE, I'd be checking using the ISNUMERIC function...
      one day at a time

      Comment


        #13
        Originally posted by oscarose View Post
        Are you 100% the VARCHAR field contains valid numbers? As well as looking at the CASE, I'd be checking using the ISNUMERIC function...
        ...and strip off any spaces.

        CAST(LTRIM(RTRIM(thatfield)) as INT)
        {emotionless greeting}

        Three Word Slogan

        Comment


          #14
          Originally posted by oscarose View Post
          Are you 100% the VARCHAR field contains valid numbers? As well as looking at the CASE, I'd be checking using the ISNUMERIC function...
          Yes

          Generally it is the cost centre which i am trying to join on but the I am trying to get the query to run using databases from 2 different systems

          In 1 this is a varchar in the other it is an int - so same data but different types.

          Same restriction on entry as well - but managed at front end not db level.

          Comment


            #15
            The big danger is data quality - the VARCHAR field might not contain just the equivalent integers.

            Just run a select cast(table.varcharcol as int) from table on the table with the varchar field.
            If it breaks, you've got data integrity issues; if it doesn't, go with the cast in the join as others have suggested. A WITH (NOLOCK) hint may speed it up if you've got performance issues.
            The greatest trick the devil ever pulled was convincing the world that he didn't exist

            Comment


              #16
              Originally posted by LondonManc View Post
              A WITH (NOLOCK) hint may speed it up if you've got performance issues.
              I don't know what kind of data OP has or how often its updated. But I would suggest if it's financial or needs to be accurate and is updated frequently then he at least understands the downsides of NOLOCK.

              Comment

              Working...
              X