query issue need help with substring i believe..

[DaCodeman0013]

This query...

ALTER PROCEDURE sp_CoreTrack1
AS

SET NOCOUNT ON

INSERT CoreTracCustomerName
(
REC,
CCODE,
ATTN,
STREET,
CITY,
STATE,
ZIP,
FULL_NAME,
CON_FNAME,
CON_LNAME,
SS#
)
SELECT DISTINCT ct.CUSTNUM,
ct.TYPEGROUP,
ct.ATTN,
ct.STREET,
ct.CITY,
ct.STATE,
ct.ZIP,
ct.NAME,
SUBSTRING(ct.NAME, 1, NULLIF(CHARINDEX(' ', ct.NAME) - 1, -1)),
SUBSTRING(ct.NAME, CHARINDEX(' ', ct.NAME) + 1, LEN(ct.NAME)),
ct.SS#
FROM CoreTrack AS ct
WHERE ct.SS# > '0'
AND NOT EXISTS (SELECT * FROM CoreTracCustomerName AS ctcn WHERE ctcn.REC = ct.CUSTNUM)

SELECT @@ROWCOUNT



This returns the following error:



Server: Msg 8152, Level 16, State 9, Procedure sp_coretrack1, Line 6
String or binary data would be truncated.
The statement has been terminated.



Any suggestions?

 

[LNBruno]

A column length is not large enough to accomodate one of the values you're trying to insert.

< M!ke >
[small]I can say nothing, which is cowardly, I can lie, which is immoral, or I can tell the truth, which will upset people. - Tiki Barber[/small]

 

[DaCodeman0013]

how do i find out which one it is.. they should all be the same length in both tables... also is the substring a way to fix this? it has been suggested but i have no idea what that means...

 

[LNBruno]

Table column lengths:

SELECT 
       Table_Name
       , Column_Name
       , Ordinal_Position           POS
       , Column_Default             Col_Default
       , Is_Nullable                Nulls
       , Data_Type
       , Character_Maximum_Length   CharMax
       , Numeric_Precision          Num_Prec
       , Numeric_Scale              Num_Scale
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'CoreTracCustomerName'
ORDER BY ORDINAL_POSITION

As for the SELECT portion, I'd suggest wrapping each column name with: Max(DataLength


< M!ke >
[small]I can say nothing, which is cowardly, I can lie, which is immoral, or I can tell the truth, which will upset people. - Tiki Barber[/small]

 

[DaCodeman0013]

how does the max data length thing work?

 

[LNBruno]

Best way to find out is try it:

SELECT Max(DataLength(STREET))
  FROM CoreTrack

< M!ke >
[small]I can say nothing, which is cowardly, I can lie, which is immoral, or I can tell the truth, which will upset people. - Tiki Barber[/small]

 

[DaCodeman0013]

Same error here is my code did i do it wrong?


ALTER PROCEDURE sp_CoreTrack1
AS

SET NOCOUNT ON

INSERT CoreTracCustomerName
(
REC,
CCODE,
ATTN,
STREET,
CITY,
STATE,
ZIP,
FULL_NAME,
CON_FNAME,
CON_LNAME,
SS
)
SELECT DISTINCT Max(DataLength(ct.CUSTNUM)),
Max(DataLength(ct.TYPEGROUP)),
Max(DataLength(ct.ATTN)),
Max(DataLength(ct.street)),
Max(DataLength(ct.CITY)),
Max(DataLength(ct.STATE)),
Max(DataLength(ct.ZIP)),
Max(DataLength(ct.NAME)),
SUBSTRING(ct.NAME, 1, NULLIF(CHARINDEX(' ', ct.NAME) - 1, -1)),
SUBSTRING(ct.NAME, CHARINDEX(' ', ct.NAME) + 1, LEN(ct.NAME)),
Max(DataLength(ct.SS#))
FROM CoreTrack AS ct
WHERE ct.SS# is not null

SELECT @@ROWCOUNT

 

[LNBruno]

Um. Yeah, that's not it.

Once you know the column lengths from the first query, then you need to find out what the value lengths are that you're trying to insert. So run this:

SELECT Max(DataLength(ct.CUSTNUM)),
       Max(DataLength(ct.TYPEGROUP)),
       Max(DataLength(ct.ATTN)),
       Max(DataLength(ct.street)),
       Max(DataLength(ct.CITY)),
       Max(DataLength(ct.STATE)),
       Max(DataLength(ct.ZIP)),
       Max(DataLength(ct.NAME)),
       Max(DataLength(ct.SS#))
FROM   CoreTrack AS ct
WHERE  ct.SS# is not null

and see if you have a value larger than the allowed column length. If you can rule that out, THEN you can take a look at the SUBSTRINGs....

Make sense?

< M!ke >
[small]I can say nothing, which is cowardly, I can lie, which is immoral, or I can tell the truth, which will upset people. - Tiki Barber[/small]

 

[DaCodeman0013]

7 2 30 30 22 2 10 30 9


thats what i get from that one...

 

[DaCodeman0013]

7 2 30 30 22 2 10 35 12 17 11


thats corecraccustomername

 

[DaCodeman0013]

it looks like ss# is the issue one is 11 one is 9

 

[DaCodeman0013]

ok so what i need to do is take that one with 11 and take it down to 9 by removing dashes is that possible? or would it be easier to just take the one thats 9 and make it 11? if thats the case how do i resize the vield to 11?

 

[LNBruno]

To remove the dashes:

SELECT REPLACE(SS#,'-','')
  FROM CoreTrack

< M!ke >
[small]I can say nothing, which is cowardly, I can lie, which is immoral, or I can tell the truth, which will upset people. - Tiki Barber[/small]

 

[DaCodeman0013]

i want to increase the sizes of these 2 fields how do i do that?

CON_FNAME,CON_LNAME

 

[LNBruno]

Look up ALTER TABLE....

< M!ke >
[small]I can say nothing, which is cowardly, I can lie, which is immoral, or I can tell the truth, which will upset people. - Tiki Barber[/small]

 

[DaCodeman0013]

ok thank you!