Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Simple T-SQL Script But Cannot Figure It Out

Posted on 2011-02-25
21
Medium Priority
?
330 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
ID: 34983024
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
ID: 34983224
Thanks, Tigin!  Interesting.  How does a person know whether to use char(1) vs. varchar(20)?
0
 
LVL 26

Expert Comment

by:tigin44
ID: 34983295
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 Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 1

Author Comment

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

Expert Comment

by:tigin44
ID: 34983502
could you provide sample data from both tables...
0
 
LVL 1

Author Comment

by:apitech
ID: 34983870
SOP30200.rpt MN010000.rpt

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

Expert Comment

by:Lowfatspread
ID: 34983938
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
ID: 34984001
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 2000 total points
ID: 34984088
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
ID: 34984362
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34988159
>>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
ID: 34989226
An applied example would be nice.  That's why I posted on here.  

0
 
LVL 1

Author Comment

by:apitech
ID: 34989267
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
ID: 34989658
>>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
ID: 34989676
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
ID: 34991370
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
ID: 34991381
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
ID: 34991808
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
ID: 34991817
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
ID: 34994305
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
ID: 34994357
>>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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

971 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