NUUBE Question

PossibleSolopsist · November 29, 2017, 5:25pm

Hey everyone, I am your usual data person. (E.g. no training whatsoever but somehow working as an analyst in a research firm). For a work project we are using MS SQL Server due to the size of the data tables. OK, fine, another language I have to learn with literally zero time at all to even take a tutorial.
Basically here is the question:
Why am I getting this error "Conversion failed when converting the nvarchar value 'A44434KSH-P02-T03-3113' to data type int."?
The table column it is being placed in is nvarchar 250 and I do not know why it is "converting to data type int". I have a suspicion, but this is my first day using T-SQL.
Here's the code:

USE XXX_DATA_2017
GO

INSERT INTO [dbo].[tbl_Master_Issues](HouseholdID, IssueID, PersonID, TripID, Warning,TestNumber,DateIssueCreated)

**SELECT HouseholdID, PersonID, TripID, **
**'Trip Mode Missing' AS Warning, **
'3113' AS TestNumber,
TripID + '-3113' AS IssueID,
**GETDATE() AS DateIssueCreated **
**FROM [dbo].[tbl_2017_CRD_Trips] **
WHERE ((([dbo].[tbl_2017_CRD_Trips].Mode1) Is Null) AND (([dbo].[tbl_2017_CRD_Trips].FlagForRemoval) Is Null)) OR ((([dbo].[tbl_2017_CRD_Trips].Mode1)=77) AND (([dbo].[tbl_2017_CRD_Trips].Mode1Other) Is Null) AND (([dbo].[tbl_2017_CRD_Trips].FlagForRemoval) Is Null));

Yes, I know it has "TripID + '-3113' AS IssueID" - 'A44434KSH-P02-T03' is a tripID to which I wish to append the IssueID. Thing is, the example of code used on this project I have available uses this same thing:

SELECT
[dbo].[02_PersonsValid].PersonID + '-2514' as IssueID,

So why is that OK for this other analyst's code but not on mine?
The column is nvarchar 255 of SQL_Latin1_General_CP1_CI_AS.
My suspicion is that the "+" makes it think this is an integer calculation, but like I displayed the other analyst has used this formatting. Also, T-SQL rejects "&" saying "The data types nvarchar and varchar are incompatible in the '&' operator." Arg.

So any help would be greatly appreciated!!
Also - WHY DOES MS HAVE MULTIPLE TYPES OF SQL?! Why can they not be made compatible with eachother?
I'm not a coder but have to write in 4 languages to create/maintain a database, so I don't need extra work- but thats just complaining....

Thanks in advance!

gbritton · November 29, 2017, 6:39pm

PossibleSolopsist:

USE XXX_DATA_2017

GO

INSERT INTO [dbo].[tbl_Master_Issues](HouseholdID, IssueID, PersonID, TripID, Warning,TestNumber,DateIssueCreated)

**SELECT	HouseholdID,	PersonID, 	TripID, **

**‘Trip Mode Missing’	AS Warning,	**

‘3113’	AS TestNumber,

TripID + ‘-3113’ AS IssueID,

**GETDATE() AS DateIssueCreated	**

**FROM [dbo].[tbl_2017_CRD_Trips]		**

WHERE ((([dbo].[tbl_2017_CRD_Trips].Mode1) Is Null) AND (([dbo].[tbl_2017_CRD_Trips].FlagForRemoval) Is Null)) OR ((([dbo].[tbl_2017_CRD_Trips].Mode1)=77) AND (([dbo].[tbl_2017_CRD_Trips].Mode1Other) Is Null) AND (([dbo].[tbl_2017_CRD_Trips].FlagForRemoval) Is Null));

I simplified and reformatted it:

INSERT INTO [dbo].[tbl_Master_Issues]
    ( HouseholdID ,
      IssueID ,
      PersonID ,
      TripID ,
      Warning ,
      TestNumber ,
      DateIssueCreated )
SELECT HouseholdID ,
       PersonID ,
       TripID ,
       'Trip Mode Missing' AS Warning ,
       '3113' AS TestNumber ,
       TripID + '-3113' AS IssueID ,
       GETDATE() AS DateIssueCreated
FROM   [dbo].[tbl_2017_CRD_Trips] t
WHERE  (   t.Mode1 IS NULL
           AND t.FlagForRemoval IS NULL )
       OR
       (   t.Mode1 = 77
           AND t.Mode1Other IS NULL
           AND t.FlagForRemoval IS NULL );

now, you can see the main problem:

the column order of the select clause does not match that of the insert statement

Really only two (that I know of) MS-Access and Transact SQL for SQL Server. Oh, I forgot the special SQL hidden in Excel. Possibly there are others. "Why" is a difficult question though. There is an ANSI SQL, but no vendor implements it exactly or entirely. Plus, each vendor adds features and functions and syntax that compete and conflict with other vendors.

In practice it's not such a big deal, once you get over the initial learning curve.

Kristen · November 29, 2017, 7:04pm

Yes and No ... but mostly No!

SQL uses operator precedence. So if you say A + B then SQL will decide if the two things being combined should be "elevated" to a Numeric calculation.

So if you say String + String then the result is string concatenation.

If you say Number + Number then its mathematical addition

However, more tricky and often annoying is String + Number. That gets elevated to Mathematical-Addition, and an implicit (that's the annoying bit, coz you may not know it is happening) conversion of the String to Number, and THEN add the Number.

If all the strings are valid for conversion to numbers (including blank strings [numeric value = 0] or NULL [numeric value = NULL]), then it will work just fine.

But if, say, tomorrow, one of the strings is NOT Numeric than BANG ... you will get an implicit data conversion error at run-time.

The reason this is annoying is that you can, by chance, have strings in testing that all happen to be valid for numeric conversion, and never see the error ... until the users start using it.

So basically be very careful NOT to mix data types because SQL will do its best to use implicit conversion.

I recommend that you have a rule that if you mix datatypes you ALWAYS have an EXPLICIT Conversion / Cast so they are the same datatype. That includes going from INT to BIGINT

SELECT CONVERT(varchar(123), [dbo].[02_PersonsValid].PersonID )+ ‘-2514’ as IssueID

[You need to set an appropriate size for "123" of course ... we use "20" for all numeric conversions and "40" for GUIDs and various for "dates"]

By the by, you do not need to use [dbo].[02_PersonsValid]. prefix for the column - if nothing else it makes the SQL code very hard to read.

If the column name is unique within the query then you can just use the column name. Although there are schools of thought that recommend always having a table-alias prefix.

Where not unique you do need to specify the table, but in order to avoid having the full Schema and Table name as the prefix you can give the Table an Alias name:

SELECT [dbo].[02_PersonsValid].PersonID
FROM [dbo].[02_PersonsValid]

can be changed to the more readable

SELECT PV.PersonID
FROM [dbo].[02_PersonsValid] AS PV

PossibleSolopsist · November 29, 2017, 7:28pm

Hey thanks alot, that helped! It is kind of a rugby versus football experience my first day with T-SQL.

Kristen · November 29, 2017, 8:28pm

There you go with those data types again - Rugby is Football, whereas Football might be Soccer ... or American Football .. or indeed might even be something else - Gaelic or Australasian Rules Football maybe?

jeffw8713 · November 29, 2017, 8:36pm

As of SQL Server 2012 we now have a new operator that can eliminate a lot of these issues. Instead of concatenating with the plus operator - use the CONCAT function.

SELECT CONCAT('a', 'b', 1, 2, 0.00) will treat each value as a string regardless of actual data type passed. This also handles NULL values which can cause issues with the plus operator.

Traditionally we would do this for null values:

SELECT value1 + isnull(value2, '') + isnull(value3, '')

And we would do something like this - for non-character data:

SELECT value1 + isnull(cast(value2 As varchar(10)), '') + isnull(cast(value3 As varchar(10)), '')

Now - we can do this:

SELECT CONCAT(value1, value2, value3)

And if value2 is NULL or numeric it is converted to a string and concatenated.

CAVEAT: the returned data type will be VARCHAR but the actual size may not be what is expected. This statement generated a VARCHAR(67): SELECT CONCAT('a', 'b', 1, 2, 0.00) - this statement returned a VARCHAR(30): SELECT CONCAT('a', 'b', 1, 2, '0.00').

Kristen · November 29, 2017, 8:53pm

I keep forgetting to remind myself to use that ... Old Dog + New Trick and all that

Kristen · November 29, 2017, 8:55pm

That's the only bit that gets me. I quite often rely on NULL string propagating to force the resulting overall concatenation to BE Null ...

jeffw8713 · November 29, 2017, 10:27pm

I would have to say that in most cases of concatenation we would not want nulls to propagate. That would be a problem for your scenario but then again you can just rely on the overloaded plus operator to function as is...

Kristen · November 30, 2017, 7:50am

Yes, in fairness that's true for me too, but its a bit like explicit casting instead of implicit - we do that everywhere we mean it s its clear we knew that there was a potential problem. Same with NULL propagation, we use COALESCE/NULLIF everywhere we don't want it to happen, so the programmer's intent is clear.

But ... I am certain we do it on NOT NULL columns too, just to be 1000% sure that there will not now, nor ever, be NULLs that propagate ... so I suppose once I start using it for everything I'll wonder how I ever managed without it before!! I'm back to "Old Dog + New Trick and all that"

PossibleSolopsist · November 30, 2017, 4:19pm

Oh thank you! this will make things easier. T-SQL is not that different from SQL, but when you are in a crunch with a huge number of queries to convert to the server it can be very frustrating to find the error and then figure out the proper new format. I have always had such a positive experience in SQL forums, any time I have had a problem it seems like legions of people jump up to help.

PossibleSolopsist · November 30, 2017, 5:19pm

Haha this is how I feel! I'm on the grass and a ball is coming at me, is this a 'mark' moment or am I about to 'foul the ball'?!