UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Conversion Failed When Converting Nvarchar To Int?, Any Versions    
 
   
BruceM
post Nov 13 2017, 02:49 PM
Post#1


UtterAccess VIP
Posts: 6,987
Joined: 24-May 10
From: Downeast Maine


SQL Server 2016.

QUOTE
Conversion failed when converting the nvarchar value 'This is a comment' to data type int.


Of course that isn't the actual comment. I haven't been able to sort out why I am getting the above error on this line:

SELECT ...
ZLog.Comments + IsNull(C.CustID, Char(13) + Char(10) + 'Cust: ' + ZLog.Customer) AS Comments

ZLog.Comments and ZLog.Customer are both text, from a table imported from an Excel spreadsheet. That is, I imported the spreadsheet as a SQL Server table. C.CustID is an integer (int).

I tried CAST(ZLog.Comments AS nvarchar(250)) + IsNull...etc., but the message remained when I tried to execute the Select query. I tried CAST on other fields, and maybe there is a combination that will finally resolve this, but is it really as complicated as this?

This code is from a view that I am using with some success to wrangle non-normalized data, but this one has me stumped. ZLog is the SQL Server table created from the original spreadsheet data. The logic is this:

C.CustID (int) is the ID field of the customer. For various reasons not every customer is entered into the system, particularly for older records. If C.CustID in null, use the text in the Customer field (nvarchar) from ZLog to identify the customer on a new line in the Comments field.

Please accept that it is valid for the CustID field to be null. It isn't exactly true, but this question is about solving the expression, so I used a summary explanation.

Scenarios:
1) ZLog.Comments is "This is a comment", CustID is null, ZLog.Customer (customer name) is "Omega Corp."). Result:

This is a comment
Cust: Omega Corp.


2) ZLog.Comments is "This is a comment", CustID is not null). Result:

This is a comment


3) ZLog.Comments is null, CustID is null, ZLog.Customer (customer name) is "Omega Corp."). Result:

Cust: Omega Corp.
Go to the top of the page
 
MadPiet
post Nov 13 2017, 02:57 PM
Post#2



Posts: 2,225
Joined: 27-February 09



1. do you really need NVARCHAR()? those are double-byte.
2. Where's the CAST of the number to a VARCHAR() ? Without it, the + is being interpreted as "add", not "concatenate". So if you cast the numeric parts of the expression to strings first then you can use + to append one chunk of text to another, because then they're both text.
Go to the top of the page
 
BruceM
post Nov 13 2017, 03:41 PM
Post#3


UtterAccess VIP
Posts: 6,987
Joined: 24-May 10
From: Downeast Maine


QUOTE
Do you really need NVARCHAR()? Those are double-byte.

I have no idea if I need it. Do you mean as opposed to varchar? I have generally been using nvarchar for text fields because it seemed to be the most flexible choice, but it will be some time before I am able to follow the discussions on the subject.

However, I think I understand that you are saying the CAST needs to be used to cast the int field as text, otherwise SQL Server treats the + signs as mathematical rather than concatenation operators. I tried that, and it seems to work, but I realized the logic is incorrect. When there is a C.CustID value, ignore the ZLog.Customer field. I tried the following expression (which I think avoids the original problem, although if so it is a result rather than the intent):
CODE
COALESCE
  (ZLog.Comments,'')
  +
  CASE
    WHEN C.CustID Is Not Null
    THEN ''
    ELSE
      CASE
        WHEN ZLog.Customer Is Null
        THEN ''
        ELSE Char(13) + Char(10) + 'Cust: ' + ZLog.Customer
AS Comments

I get the following error:

Msg 156, Level 15, State 1, Line 28
Incorrect syntax near the keyword 'AS'.

Line 28 is the AS Comments line (I have it broken down into lines as shown here). This error is often about a missing parentheses or something like that, but I cannot find the culprit. Any ideas?

Also, I know I have to avoid the new line if ZLog.Comments is null, but I'll post this as is and go back to work on that.

Edit: Left out the END from the CASE. Working on that.
Edit 2: Back again. I think I have it. There may be a way to do this with fewer words, but thanks to your observation I may be back on track.

CODE
COALESCE
  (ZLog.Comments,'')
  +
  CASE
    WHEN C.CustID Is Not Null
    THEN ''
    ELSE
      CASE
        WHEN ZLog.Customer Is Null
        THEN ''
        ELSE
          CASE
            WHEN ZLog.Comments Is Null
            THEN ''
            ELSE Char(13) + Char(10) + 'Cust: ' + ZLog.Customer
          END
      END
  END
AS Comments

Just one thing I'm wondering for now: The expression may create multiple lines within the field. Does SSMS provide a way to look at those lines when executing the query? I expect I could use reporting services, or else save the SQL as a view and use Access to do such formatting, but I'm just wondering if there is a quick way to test a multiple-line result.
Go to the top of the page
 
MadPiet
post Nov 13 2017, 06:39 PM
Post#4



Posts: 2,225
Joined: 27-February 09



If it's a T-SQL query, you can just run it.

EXEC MyStoredProc @param1 = 'Book', @param2 = 5;

Otherwise, F5 is your friend.
Go to the top of the page
 
BruceM
post Nov 14 2017, 07:24 AM
Post#5


UtterAccess VIP
Posts: 6,987
Joined: 24-May 10
From: Downeast Maine


Thanks. I have discovered F5, but can't find a way to see more than one line when viewing the result. A stored procedure sounds like the way to go.
Go to the top of the page
 
MadPiet
post Nov 14 2017, 09:55 AM
Post#6



Posts: 2,225
Joined: 27-February 09



Does your stored procedure return data? I'm confused, because when I run a stored procedure, the results window opens, and I can see the first maybe 20 rows, and then scroll through the rest. If that's the case then your environment in SSMS isn't configured properly.
Go to the top of the page
 
BruceM
post Nov 14 2017, 11:28 AM
Post#7


UtterAccess VIP
Posts: 6,987
Joined: 24-May 10
From: Downeast Maine


Remember, I'm asking because SQL Server is new to me. I have no idea why byte byte is better or worse than any other option, for instance, so please don't think I am doing things for definite reasons. I'm feeling my way through this, trying things to see what works while I get a better handle on the SSMS interface and language, and how to work with SQL Server from the Access front end (linked tables, executing stored procedures, and so forth).

I haven't written the stored procedure yet. Rather, I built the query with the parameters built in, and ran it directly. Now that it is working I can see about using a stored procedure that accepts parameters rather than hard coding the parameters every time, but until I could figure out if it works at all I saw no point to building a stored procedure. Now that it works I can turn my attention to the stored procedure.

To stress a point I thought I had made, I do see the results as you describe when I use F5 (or other means) to execute the query. To look at a selection of records from a view or table I choose the option to see the first 1000 rows (which I change to TOP 100 PERCENT when I want to see all of the records). All I am asking (and I believe the answer is No) is whether I can change the row height in the results I see in SSMS, to verify a multi-line Comments field is showing the data as expected. If not, no worries. I can look at the linked data in the Access front end, once I insert the query result into a table. I expect there are options with the SSMS reporting services too, but since I can't learn everything at once I will leave that for another time.
Go to the top of the page
 
cheekybuddha
post Nov 14 2017, 01:51 PM
Post#8


UtterAccess VIP
Posts: 9,265
Joined: 6-December 03
From: Telegraph Hill


Hi,

It's often more helpful if you post the whole SQL.

Try:
CODE
SELECT ...
ZLog.Comments + CASE WHEN C.CustID IS NULL THEN Char(13) + Char(10) + 'Cust: ' + ZLog.Customer ELSE '' END AS Comments
...


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
BruceM
post Nov 14 2017, 03:21 PM
Post#9


UtterAccess VIP
Posts: 6,987
Joined: 24-May 10
From: Downeast Maine


Thank you for your interest. The SQL consists of a series of fields following SELECT, then the calculated field in question, then a series of joins.

The calculated field was the main problem. MadPiet set me on the right track when he pointed out that the int field needs to be cast as text, otherwise SQL Server will try to apply the + sign as a math operator rather than a concatenation operator, which of course fails with text fields. I didn't have to worry about that in Access with the ampersand as the concatenation operator.

After that I realized I needed to do some logical tests before performing the concatenation, and with that it was no longer necessary to cast the int field a text since the concatenation was never going to involve that field. My logic as well as my expression writing was flawed in my early attempts.

My second code example in post #3 shows the successful version of the calculated field. I can post the SQL, but since there is identifiable information in the field names I would have to rework it first. I can do that if it would be of value, but since the problem with the calculated field is solved and I can go ahead with migrating the data from the spreadsheet, my main interest at this point is doing the calculation more efficiently.

Given the explanation above, please let me know if you think the full SQL would be of value.
Go to the top of the page
 
cheekybuddha
post Nov 15 2017, 05:04 AM
Post#10


UtterAccess VIP
Posts: 9,265
Joined: 6-December 03
From: Telegraph Hill


>> please let me know if you think the full SQL would be of value. <<

No, it really only would have been of value at the beginning, to see the expression in context.

But since you have a working solution, all is well!

Continued success in getting to grips with the horror that is T-SQL!

thumbup.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 
MadPiet
post Nov 16 2017, 08:04 PM
Post#11



Posts: 2,225
Joined: 27-February 09



Horror of T-SQL? So much more sensible than the query/DAO disaster in Access, for sure!!
Go to the top of the page
 
cheekybuddha
post Nov 16 2017, 08:15 PM
Post#12


UtterAccess VIP
Posts: 9,265
Joined: 6-December 03
From: Telegraph Hill


Yes, Access SQL is certainly an abomination, but 'GO'? C'mon, really, [censored]?!!

tongue.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 
MadPiet
post Nov 16 2017, 09:27 PM
Post#13



Posts: 2,225
Joined: 27-February 09



What's wrong with GO?

One of the really cool things you can do with GO... Say you have some code you want to run 50 times...

GO 50

=)
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    20th November 2017 - 02:53 AM