Solved

Simple T-SQL Script But Cannot Figure It Out

Posted on 2011-02-25
21
273 Views
Last Modified: 2012-05-11
Attached is some very simple T-SQL code that I cannot correctly construct for whatever reason.

I am simply trying to say "give me SOP30200.SOPNUMBE where this field is in MN010000.NOTETEXT_I (AND ONLY WHERE SOP30200.SOPNUMBE IS IN MN010000.NOTETEXT_I FOR GOODNESS SAKE!!!!)

I mean, that's very straightforward.  But, that is not the result that SQL is giving me.  For whatever reason it is givng me the results seen in the attached file.

Evidently, I can't do basic T-SQL scripting.  But, I don't get why T-SQL is defying all sense of logic.  Makes no sense to me.  whatinthefreakinworld.rpt
select 'Approved' as [Status], SOP30200.SOPNUMBE as [Quote No.], MN010000.NOTETEXT_I
from SOP30200
INNER JOIN MN010000 on convert(char(1),SOP30200.SOPNUMBE) = convert(char(1),MN010000.NOTETEXT_I)
where SOP30200.SOPTYPE = '1' and (convert(char(1),SOP30200.SOPNUMBE)) in (convert(char(1),MN010000.NOTETEXT_I))

Open in new window

0
Comment
Question by:apitech
  • 11
  • 5
  • 4
  • +1
21 Comments
 
LVL 26

Expert Comment

by:tigin44
Comment Utility
select 'Approved' as [Status], SOP30200.SOPNUMBE as [Quote No.], MN010000.NOTETEXT_I
from SOP30200
INNER JOIN MN010000 on convert(varchar(20),SOP30200.SOPNUMBE) = convert(varchar(20),MN010000.NOTETEXT_I)
where SOP30200.SOPTYPE = '1'
0
 
LVL 1

Author Comment

by:apitech
Comment Utility
Thanks, Tigin!  Interesting.  How does a person know whether to use char(1) vs. varchar(20)?
0
 
LVL 26

Expert Comment

by:tigin44
Comment Utility
your strings longer then one character and variable length .. so I changed then to varchar(20)..
ofcourse you may need to modify it..  char(1) returns only the first characters of the strings... which leads you to wrong result set..
0
 
LVL 1

Author Comment

by:apitech
Comment Utility
Unforunately, when I made those changes, no results were returned.  I should have had one row returned.
0
 
LVL 26

Expert Comment

by:tigin44
Comment Utility
could you provide sample data from both tables...
0
 
LVL 1

Author Comment

by:apitech
Comment Utility
SOP30200.rpt MN010000.rpt

Attached are sample data from each table.  Hope it helps.
0
 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
please explain what you are trying to do in terms of the data in the columns...

your current sql is working as required...

its is saying join the tables on the first character of the columns....
and there where is saying and i only want the results where soptype  = 1

the restating of the first character IN the first character isn't adding any further qualification to the processing...

it all hinges on what is in the columns you are trying to compare and there datatypes...
also what do you think you mean by IN???

in SQL terms IN is used when you are trying to compare against a list of values....
but that list is a set of discrete values (columns/expressions) not data within a single column/expression...
you maybe trying to search a comma delimited set of values stored in the notext column... (if so that requires a
completly different set of syntax...)

is it important to compare the first charatcers?
0
 
LVL 1

Author Comment

by:apitech
Comment Utility
OK.  Let me see if I can better explain what I want.  First of all, I'm not trying to get anything based on the first character, second character, or anything along those lines.

I am simply trying to say "give me SOP30200.SOPNUMBE where the data in this field is equal to exactly the data in the MN010000.NOTETEXT_I field".

I used "IN" because the equal sign was not giving me any better results.  I'm a T-SQL novice and have found that T-SQL does not always go by the boundaries of what I feel is logical, so you have to try about a billion things just to get the one very simple result that you want.

0
 
LVL 26

Accepted Solution

by:
tigin44 earned 500 total points
Comment Utility
try this

select 'Approved' as [Status], SOP30200.SOPNUMBE as [Quote No.], MN010000.NOTETEXT_I
from SOP30200
INNER JOIN MN010000 on SOP30200.SOPNUMBE = MN010000.NOTETEXT_I
where SOP30200.SOPTYPE = 1
0
 
LVL 1

Author Comment

by:apitech
Comment Utility
No, that doesn't work either.  You see, that was the first thing that I tried.  Then, I got this error:
The data types char and text are incompatible in the equal to operator.

That's when I tried "convert(char(1))" and stuff like that that I had read about to try to clear up this error.

That's when all of these problems started.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>The data types char and text are incompatible in the equal to operator.<<
That sounds like you are still using the deprecated text data type.  If you cannot change it you will have to use CAST or CONVERT as suggested previously.  It does not get simpler than that.


0
 
LVL 1

Author Comment

by:apitech
Comment Utility
An applied example would be nice.  That's why I posted on here.  

0
 
LVL 1

Author Comment

by:apitech
Comment Utility
OK, guys.  Below is the solution.  Whew!  A buddy of mine on another newsgroup (thank the Good Lord!) posted the solution that I needed.  I really, really needed this!  I am so glad a finally got the right answer!  

I can't believe that I did not get this solution sooner, more easier, and without someone earlier in this posting (without naming names) making me play a guessing game.

Very disappointing!

select 'Approved' as [Status], SOP30200.SOPNUMBE as [Quote No.], MN010000.NOTETEXT_I
from SOP30200, MN010000
where convert(varchar(max),MN010000.NOTETEXT_I) like  '%' + rtrim(SOP30200.SOPNUMBE) + '%'
and SOP30200.SOPTYPE = '1'

0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>It is very disappointing that acperkins was making me play a guessing game.<<
I am sorry you took it that way.  Put yourself in my position and ask yourself what you would have done.

0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
One thing you have to know about the members on this site and that is that many of them are very tech savy, but no one has a crystal ball.  If you do not present the full question, and by that I mean CREATE TABLE SQL script to populate it and a way we can duplicate the problem, then all we can do is offer are guesses.  Having said that most of the comments led you to the answer, and it is unfortunate and dare I say disappointing that for one that has been here some time should know that you are not following the EE Guidelines regarding closing questions.  If you need me to spell it out more clearly by quoting the EE Guidelines I can do that, too.

Good luck in all your endeavors.
0
 
LVL 1

Author Comment

by:apitech
Comment Utility
I apologize for my comments and personally to acperkins.  If you all feel like the questions should, instead, be closed by accepting other comments, I'm OK with that.

I do request that in the future--when I ask T-SQL programming questions--everyone understand that when I am in a bind and really need help that it would be best not to simply advise me to use certain "elements" of T-SQL programming and point out "as others have suggested".  That gives me nothing to go on and makes me feel even more at a loss to solve my problem and eliminate the issue.

Again, I do apologize!
0
 
LVL 1

Author Comment

by:apitech
Comment Utility
I'm canceling my request for closing based on the fact that I should have followed EE guidelines more carefully.  Below is my apology, which I have already posted.

If you would like to accept the response of either acperkins or tigin44, that's fine.  Like I said, I apologize for my outburst.  I was out of line.


I apologize for my comments and personally to acperkins.  If you all feel like the questions should, instead, be closed by accepting other comments, I'm OK with that.

I do request that in the future--when I ask T-SQL programming questions--everyone understand that when I am in a bind and really need help that it would be best not to simply advise me to use certain "elements" of T-SQL programming and point out "as others have suggested".  That gives me nothing to go on and makes me feel even more at a loss to solve my problem and eliminate the issue.

Again, I do apologize!
0
 
LVL 1

Author Comment

by:apitech
Comment Utility
Also, if someone could provide me with the necessary guidelines for closing cases so that I do not make these mistakes again I'd appreciate it!  I really do value the years of great support that you all have provided over the years and I do want to make sure that I do a better job in conforming to your policies.  Also, I will find ways to more politely express my concerns.  Honestly, I did not realize that my earlier comments were going to all be publicly posted.  I did not mean to hurt anyone's feelings.
0
 
LVL 1

Author Comment

by:apitech
Comment Utility
I'll visit this thread later in the week and hope to find the guidelines that I should have reviewed.  Thanks!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Here you go from http://www.experts-exchange.com/help.jsp#hs=29&hi=407

How do I close a question?
There are five ways:

Accept an Expert's comment as the solution
If an Expert has given you the solution to your question, or has led you to the solution, select this option.

Accept multiple solutions
If several Experts collaborated to provide a solution, use this option and split the points among them.

Accept your own comment as the solution
If you solved your problem with no assistance from any Expert, post your solution and then click the Accept As Solution
button in your own comment. Your points are automatically refunded.

Accept your own comment, and award points to Experts for their assistance
If you answered your own question, but wish to award points to Experts for their attempts to help, use this option.

Delete your question
If you are not getting the kinds of responses that will lead to a solution, you should consider deleting your question and trying again. The Delete Question button is located near the text of your original post.

If you are not certain which selection to make, click the Request Attention button, and the Moderators will help you with the proper choice.

The correct answer to some questions is "You can't do that."
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>I do request that in the future--when I ask T-SQL programming questions--everyone understand that when I am in a bind and really need help that it would be best not to simply advise me to use certain "elements" of T-SQL programming and point out "as others have suggested".<<
Unfortunately as I see it there are three problems with that:
1.  We do not know you from Adam.  By that I mean we do no know what is your level of expertise and without you explicitly telling us, we are in danger of sounding condescending if we have to "spell it out" for you.
2.  The second problem I have is that the solution had already been provided for you.  Why should I reap the rewards for something so simple (your words not mine) as give you the exact syntax when the solution has already been provided, in the very first comment.
3. Finally, as I alluded to before none of us are sitting in front of your computer and therefore no one could have possible guessed from your question that you needed a wildcard comparison using LIKE.


>>I did not mean to hurt anyone's feelings. <<
You did not even come close.  It just amazes me sometimes that members here have this wonderful resource here manned by volunteers and yet they continually abuse the system and/or take it for granted.  Why?  I have heard all type of justifications, my favorite is: "I pay my monthly fee and I expect better service than this."  If people would only realize how badly it makes them look, they may think twice before emitting such a lame excuse.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now