PeterBaileyUk
asked on
convert null in sql server
The USP creates a pk to for insert into TblCompare events.
Its falling down when it hits a null entry on the data field Fieldname as change or fieldname as prev. all the other pk components can never be null.
if fieldname prev or change (will never ever be both together as the sql found a difference) are null how do I allow the insert? I would have used NZ in access.
when its creating the PK I dont need to concatenate the null.
the error message is Msg 515, Level 16, State 2, Line 7
Cannot insert the value NULL into column 'PK_ID', table 'ClientData.dbo.TblCompare Events'; column does not allow nulls. INSERT fails.
the sql generated in this case is :
Its falling down when it hits a null entry on the data field Fieldname as change or fieldname as prev. all the other pk components can never be null.
if fieldname prev or change (will never ever be both together as the sql found a difference) are null how do I allow the insert? I would have used NZ in access.
when its creating the PK I dont need to concatenate the null.
the error message is Msg 515, Level 16, State 2, Line 7
Cannot insert the value NULL into column 'PK_ID', table 'ClientData.dbo.TblCompare
the sql generated in this case is :
INSERT INTO TblCompareEvents(ClientCode, ChangeYearMonth, Prev, Change, FieldName, VehicleCategory, PK_ID )
SELECT [tClient].[abiCode], [tClient].[BATCH] , [tClient PREV].[MANUFACTURED_TO] AS PREV
,[tClient].[MANUFACTURED_TO] AS CHANGE, 'MANUFACTURED_TO' AS FieldName, [tClient].[ABI_cat],CAST( [tClient].[abiCode] AS VARCHAR(1000))
+ CAST( [tClient].[BATCH] AS VARCHAR(1000))
+ CAST( [tClient PREV].[MANUFACTURED_TO] AS VARCHAR(1000))
+ CAST( [tClient].[MANUFACTURED_TO] AS VARCHAR(1000)) AS PK
FROM [tClient]
INNER JOIN [tClient PREV] ON [tClient].[abiCode] = [tClient PREV].abiCode
WHERE RTrim(LTrim(IsNull([tClient].[MANUFACTURED_TO],''))) <> RTrim(LTrim(IsNull([tClient PREV].[MANUFACTURED_TO],'')))
Hi,
Please try this. I have fixed the NULL issue.
Hope it helps!
Please try this. I have fixed the NULL issue.
INSERT INTO TblCompareEvents(ClientCode, ChangeYearMonth, Prev, Change, FieldName, VehicleCategory, PK_ID )
SELECT [tClient].[abiCode], [tClient].[BATCH] , [tClient PREV].[MANUFACTURED_TO] AS PREV
,[tClient].[MANUFACTURED_TO] AS CHANGE, 'MANUFACTURED_TO' AS FieldName, [tClient].[ABI_cat]
, CAST( ISNULL( [tClient].[abiCode] , '') AS VARCHAR(1000))
+ CAST( ISNULL( [tClient].[BATCH] , '') AS VARCHAR(1000))
+ CAST( ISNULL( [tClient PREV].[MANUFACTURED_TO] , '') AS VARCHAR(1000))
+ CAST( ISNULL( [tClient].[MANUFACTURED_TO] , '') AS VARCHAR(1000)) AS PK
FROM [tClient]
INNER JOIN [tClient PREV] ON [tClient].[abiCode] = [tClient PREV].abiCode
WHERE RTrim(LTrim(IsNull([tClient].[MANUFACTURED_TO],''))) <> RTrim(LTrim(IsNull([tClient PREV].[MANUFACTURED_TO],'')))
Hope it helps!
ASKER
the ordinary query picks up manufactured_to rows where the data maybe went from no prev date (a null) to a value. I need to do the insert on those. even if the null
here's the example
abiCode BATCH PREV CHANGE FieldName ABI_cat PK Resultdiff
24040237 201603 NULL 2017 MANUFACTURED_TO C NULL FALSE
32131052 201408 NULL 2016 MANUFACTURED_TO C NULL FALSE
these are valid data changes.
in this case the pk for the insert table would become "24040237201603NOTHINGHERE ASPREVNULL 2017
"240402372016032017
here's the example
abiCode BATCH PREV CHANGE FieldName ABI_cat PK Resultdiff
24040237 201603 NULL 2017 MANUFACTURED_TO C NULL FALSE
32131052 201408 NULL 2016 MANUFACTURED_TO C NULL FALSE
these are valid data changes.
in this case the pk for the insert table would become "24040237201603NOTHINGHERE
"240402372016032017
Minor change in PawanKumar's comment [ID: 42001137]
, CAST( ISNULL( [tClient].[abiCode] , '') AS VARCHAR(1000))
+ CAST( ISNULL( [tClient].[BATCH] , '') AS VARCHAR(1000))
+ CAST( ISNULL( [tClient PREV].[MANUFACTURED_TO] , 'NOTHINGHEREASPREVNULL') AS VARCHAR(1000))
+ CAST( ISNULL( [tClient].[MANUFACTURED_TO] , '') AS VARCHAR(1000)) AS PK
to get your result
in this case the pk for the insert table would become "24040237201603NOTHINGHEREASPREVNULL 2017
"240402372016032017
Hi,
Please try this -
@Prakash - I have already provided that change in my last comment. :)
@Peter - Have you tried my last suggestion. If not please try below-
Hope it helps!
Please try this -
@Prakash - I have already provided that change in my last comment. :)
@Peter - Have you tried my last suggestion. If not please try below-
INSERT INTO TblCompareEvents(ClientCode, ChangeYearMonth, Prev, Change, FieldName, VehicleCategory, PK_ID )
SELECT [tClient].[abiCode], [tClient].[BATCH] , [tClient PREV].[MANUFACTURED_TO] AS PREV
,[tClient].[MANUFACTURED_TO] AS CHANGE, 'MANUFACTURED_TO' AS FieldName, [tClient].[ABI_cat]
, CAST( ISNULL( [tClient].[abiCode] , '') AS VARCHAR(1000))
+ CAST( ISNULL( [tClient].[BATCH] , '') AS VARCHAR(1000))
+ CAST( ISNULL( [tClient PREV].[MANUFACTURED_TO] , 'NOTHINGHEREASPREVNULL') AS VARCHAR(1000))
+ CAST( ISNULL( [tClient].[MANUFACTURED_TO] , '') AS VARCHAR(1000)) AS PK
FROM [tClient]
INNER JOIN [tClient PREV] ON [tClient].[abiCode] = [tClient PREV].abiCode
WHERE RTrim(LTrim(IsNull([tClient].[MANUFACTURED_TO],''))) <> RTrim(LTrim(IsNull([tClient PREV].[MANUFACTURED_TO],'')))
Hope it helps!
ASKER
I pasted the solution but the syntax all went crazy so typed in the is null by hand.
Should i add an isnull in the select on those prev and change fields to?
currently I have:
Should i add an isnull in the select on those prev and change fields to?
currently I have:
INSERT INTO TblCompareEvents(ClientCode, ChangeYearMonth, Prev, Change, FieldName, VehicleCategory, PK_ID )
SELECT [' +@TableName + '].[' +@PKName +']' + ', [' +@TableName + '].[BATCH] , [' +@TableName + ' PREV].[' +@FieldName + '] AS PREV
,[' +@TableName + '].['+@FieldName +'] AS CHANGE, ''' +@FieldName +''' AS FieldName, [' + @TableName + '].[' +@VehCat + '],' +
'CAST( ISNULL([' +@TableName + '].[' +@PKName +'],'') AS VARCHAR(1000))
+ CAST( ISNULL([' +@TableName + '].[BATCH],'') AS VARCHAR(1000))
+ CAST( ISNULL([' +@TableName + ' PREV].[' +@FieldName + '],'') AS VARCHAR(1000))
+ CAST( ISNULL([' +@TableName + '].['+@FieldName +'],'') AS VARCHAR(1000)) AS PK'+ '
FROM [' + @TableName+ ']'+'
INNER JOIN [' +@TableName +' PREV] ON [' + @TableName + '].[' +@PKName +'] = ['+@TableName + ' PREV].' + @PKName+'
WHERE RTrim(LTrim(IsNull([' +@TableName +'].[' +@FieldName + '],''''))) <> RTrim(LTrim(IsNull([' +@TableName +' PREV].[' + @FieldName + '],'''')))'
ASKER
I forgot the error it gave was
Msg 245, Level 16, State 1, Line 7
Conversion failed when converting the varchar value ') AS VARCHAR(1000))
+ CAST( ISNULL([tClient].[MANUFACT URED_TO],' to data type int.
Msg 245, Level 16, State 1, Line 7
Conversion failed when converting the varchar value ') AS VARCHAR(1000))
+ CAST( ISNULL([tClient].[MANUFACT
ASKER
I added to the select part too on the prev and change columns
INSERT INTO TblCompareEvents(ClientCode, ChangeYearMonth, Prev, Change, FieldName, VehicleCategory, PK_ID )
SELECT [' +@TableName + '].[' +@PKName +']' + ', [' +@TableName + '].[BATCH] , isnull([' +@TableName + ' PREV].[' +@FieldName + '],'') AS PREV
,isnull([' +@TableName + '].['+@FieldName +'],'') AS CHANGE, ''' +@FieldName +''' AS FieldName, [' + @TableName + '].[' +@VehCat + '],' +
'CAST( ISNULL([' +@TableName + '].[' +@PKName +'],'') AS VARCHAR(1000))
+ CAST( ISNULL([' +@TableName + '].[BATCH],'') AS VARCHAR(1000))
+ CAST( ISNULL([' +@TableName + ' PREV].[' +@FieldName + '],'') AS VARCHAR(1000))
+ CAST( ISNULL([' +@TableName + '].['+@FieldName +'],'') AS VARCHAR(1000)) AS PK'+ '
FROM [' + @TableName+ ']'+'
INNER JOIN [' +@TableName +' PREV] ON [' + @TableName + '].[' +@PKName +'] = ['+@TableName + ' PREV].' + @PKName+'
WHERE RTrim(LTrim(IsNull([' +@TableName +'].[' +@FieldName + '],''''))) <> RTrim(LTrim(IsNull([' +@TableName +' PREV].[' + @FieldName + '],'''')))'
Hi Peter,
Please try this -
Currently I am replacing NULL with '' (EmptyStrings) , you can provide that data you want NULL to replace with.
Hope it helps!
Please try this -
Currently I am replacing NULL with '' (EmptyStrings) , you can provide that data you want NULL to replace with.
INSERT INTO TblCompareEvents(ClientCode, ChangeYearMonth, Prev, Change, FieldName, VehicleCategory, PK_ID )
SELECT [' +@TableName + '].[' +@PKName +']' + ', [' +@TableName + '].[BATCH] , [' +@TableName + ' PREV].[' +@FieldName + '] AS PREV
,[' +@TableName + '].['+@FieldName +'] AS CHANGE, ''' +@FieldName +''' AS FieldName, [' + @TableName + '].[' +@VehCat + '],' +
'ISNULL( CAST( [' +@TableName + '].[' +@PKName +'] AS VARCHAR(1000)) ,'')
+ ISNULL( CAST( [' +@TableName + '].[BATCH] AS VARCHAR(1000)) ,'')
+ ISNULL( CAST( [' +@TableName + ' PREV].[' +@FieldName + '] AS VARCHAR(1000)) ,'')
+ ISNULL( CAST( [' +@TableName + '].['+@FieldName +'] AS VARCHAR(1000)) ,'') AS PK'+ '
FROM [' + @TableName+ ']'+'
INNER JOIN [' +@TableName +' PREV] ON [' + @TableName + '].[' +@PKName +'] = ['+@TableName + ' PREV].' + @PKName+'
WHERE RTrim(LTrim(IsNull([' +@TableName +'].[' +@FieldName + '],''''))) <> RTrim(LTrim(IsNull([' +@TableName +' PREV].[' + @FieldName + '],'''')))'
Hope it helps!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Looks great will close off and start testing
Glad to help !
Yes Primary Key will no allow NULLs.
Your select query is not returning any thing. Please check.
Are you getting data from the select query.
Hope it helps!