• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 340
  • Last Modified:

Simple T-SQL Script But Cannot Figure It Out

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
apitech
Asked:
apitech
  • 11
  • 5
  • 4
  • +1
1 Solution
 
tigin44Commented:
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
 
apitechAuthor Commented:
Thanks, Tigin!  Interesting.  How does a person know whether to use char(1) vs. varchar(20)?
0
 
tigin44Commented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
apitechAuthor Commented:
Unforunately, when I made those changes, no results were returned.  I should have had one row returned.
0
 
tigin44Commented:
could you provide sample data from both tables...
0
 
apitechAuthor Commented:
SOP30200.rpt MN010000.rpt

Attached are sample data from each table.  Hope it helps.
0
 
LowfatspreadCommented:
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
 
apitechAuthor Commented:
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
 
tigin44Commented:
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
 
apitechAuthor Commented:
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
 
Anthony PerkinsCommented:
>>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
 
apitechAuthor Commented:
An applied example would be nice.  That's why I posted on here.  

0
 
apitechAuthor Commented:
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
 
Anthony PerkinsCommented:
>>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
 
Anthony PerkinsCommented:
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
 
apitechAuthor Commented:
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
 
apitechAuthor Commented:
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
 
apitechAuthor Commented:
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
 
apitechAuthor Commented:
I'll visit this thread later in the week and hope to find the guidelines that I should have reviewed.  Thanks!
0
 
Anthony PerkinsCommented:
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
 
Anthony PerkinsCommented:
>>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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 11
  • 5
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now