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.TblCompareEvents'; column does not allow nulls. INSERT fails.

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],'')))

Open in new window

Pawan Kumar

Hi,
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!

Pawan Kumar

Hi,
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],'')))

Open in new window

Hope it helps!

PeterBaileyUk

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 "24040237201603NOTHINGHEREASPREVNULL2017
"240402372016032017

Prakash Samariya

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

Open in new window

to get your result

in this case the pk for the insert table would become "24040237201603NOTHINGHEREASPREVNULL2017
"240402372016032017

Pawan Kumar

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-

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],'')))

Open in new window

Hope it helps!

PeterBaileyUk

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:

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 + '],'''')))'

Open in new window

PeterBaileyUk

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].[MANUFACTURED_TO],' to data type int.

PeterBaileyUk

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 + '],'''')))'

Open in new window

Pawan Kumar

Hi Peter,
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 + '],'''')))'

Open in new window

Hope it helps!

ASKER CERTIFIED SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PeterBaileyUk

ASKER

Looks great will close off and start testing

Pawan Kumar

Glad to help !