apitech
asked on
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
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))ASKER
Thanks, Tigin! Interesting. How does a person know whether to use char(1) vs. varchar(20)?
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..
ofcourse you may need to modify it.. char(1) returns only the first characters of the strings... which leads you to wrong result set..
ASKER
Unforunately, when I made those changes, no results were returned. I should have had one row returned.
could you provide sample data from both tables...
ASKER
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?
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?
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
>>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.
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.
ASKER
An applied example would be nice. That's why I posted on here.
ASKER
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),MN010
and SOP30200.SOPTYPE = '1'
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),MN010
and SOP30200.SOPTYPE = '1'
>>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.
I am sorry you took it that way. Put yourself in my position and ask yourself what you would have done.
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.
Good luck in all your endeavors.
ASKER
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!
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!
ASKER
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!
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!
ASKER
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.
ASKER
I'll visit this thread later in the week and hope to find the guidelines that I should have reviewed. Thanks!
Here you go from https://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."
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."
>>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.
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.
from SOP30200
INNER JOIN MN010000 on convert(varchar(20),SOP302
where SOP30200.SOPTYPE = '1'