January 29th, 2013, 10:17 AM
Non indexed table join
Ok so this is kind of complicated, at least to me, and I promise I'll try to make things as clear as possible for everyone.
I'm working with one of our applications, said application is hosted offsite, and I am not allowed to change the structre of the table in question. I have written a query that pulls orders based off of an order number and the date the order was shipped.
Unfortunately the designers of this program did not think to add a column for date the order was entered, however, I do have a text field that is used for generating emails that has the date the order was accepted into my system.
What I'm trying to do is, parse this text field (ntext) and compare it to the order number field to essentially create an "index".
Right now my code for doing this looks as such.
I did try this query first using another data type with an actual indexed table, I'm thinking there is something wrong with my syntax or my join, because when I did this with the numeric test I ran the table was already being joined elsewhere.
INNER JOIN EmailLogg
INNER JOIN [Order] ON REPLACE(CAST(EmailLogg.EmailSubject AS NVARCHAR(200)), LEFT(CAST(EmailLogg.EmailSubject AS NVARCHAR(200)), charindex('er: ',EmailLogg.EmailSubject, 1) -1) +'er: ','') = [Order].[OrderNumber]
January 29th, 2013, 12:12 PM
Figured out my dilema used substring
[CODE]INNER JOIN EmailLogg
INNER JOIN [Order] ON SUBSTRING(EmailLogg.EmailSubject, charindex('Order: ',Cast(EmailLogg.EmailSubject AS NVARCHAR(4000)),1 ) +7, 100 ) = [Order].[OrderNumber]
Now I'm just waiting on table syntax from the application developer.
January 29th, 2013, 02:54 PM
first, this isn't a mysql query (the forum you posted in), so on the off chance it's microsoft sql server, i'm moving the thread to that forum
second, each JOIN should have its own ON clause, and you're missing one
January 30th, 2013, 09:20 AM
Sorry, I meant to place the thread in the MSSQL thread, because that's what it is so thank you for moving it. (Also explains why I could not find the thread in the MSSQL thread.
Originally Posted by r937
I realize that each join needs it's own ON statement, that being said, I have left out probably a good 85% of the query. Seeing that my question was a syntax question for the most part anyway.
Also its possible that I'm just retarted, but I do not see a place to mark this thread as solved, so if you could do so for me that would be awesome.
January 30th, 2013, 11:16 AM
this forum doesn't follow the practice of marking threads solved, but, hopefully, anyone else that runs across it will see the number of replies it has, and open her up only if there's really nothing else good on television or teh interwebs
January 30th, 2013, 02:01 PM
It's a good thing that this forum does not follow those rules because this topic is still giving me issues.
Originally Posted by r937
I recreated the essentials from the database in my own database it's an mssql server 2008 r2 express database, so that I could make use of the debugging features in SQL Management Studio.
The version that my Application is running on is MSSQL Server 2000 and I cannot upgrade it.
Is there any reason that this part of the query would work in MSSQL Server 2008 R2 and not in MSSQL Server 2000? The code does indeed work in my database, it works flawlessly, but I cannot get it to work in my application.
INNER JOIN [Order]
ON SUBSTRING(EmailLog.EmailSubject, CHARINDEX('Order: ',Cast(EmailLog.EmailSubject AS NVARCHAR(4000)),1 ) +7, 8 ) = [Order].OrderNumber
January 30th, 2013, 05:16 PM
someone more familiar with sql server version nuances will have to step in