April 29, 2008 at 6:24 am
If you want to just delete those rows - then
DELETE
FROM raw_data[raw_payload]
Where raw_payload not like '%+%+%'
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 29, 2008 at 6:48 am
WHEN I RUN THIS:
INSERT INTO dbo.raw_data_pro
VALUES ('raw_seq_no','raw_date','raw_dev_id','raw_rec_type','raw_payload','raw_processed')
Select *
FROM raw_data[raw_payload]
Where raw_payload like '%+%+%'
I GET THIS ERROR...
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
(2898292 row(s) affected)
April 29, 2008 at 7:38 am
INSERT INTO dbo.raw_data_pro
Select *
FROM raw_data[raw_payload]
Where raw_payload like '%+%+%'
This works but the parsing does not....
SELECT raw_payload,
LEFT(raw_payload+'+++', CHARINDEX('+', raw_payload+'+++', 1)-1),
CAST(NULLIF(
SUBSTRING(raw_payload+'+++',
CHARINDEX('+', raw_payload+'+++', 1) + 1,
CHARINDEX('+', raw_payload+'+++',
CHARINDEX('+', raw_payload, 1) +1) -
(CHARINDEX('+', raw_payload+'+++', 1) + 1))
--,'')
AS FLOAT),
CAST(NULLIF(
REPLACE(
SUBSTRING(raw_payload+'+++',
CHARINDEX('+', raw_payload+'+++',
CHARINDEX('+', raw_payload+'+++', 1) +1) + 1,
LEN(raw_payload) - CHARINDEX('+', raw_payload,
CHARINDEX('+', raw_payload+'+++', 1) +1) + 1)
,'+','')
--,'')
AS FLOAT)
FROM raw_data_pro
ERROR
Server: Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'AS'.
April 29, 2008 at 7:44 am
You uncommented the "beginning" of the NULLIF, but not the end:
SELECT raw_payload,
LEFT(raw_payload+'+++', CHARINDEX('+', raw_payload+'+++', 1)-1),
CAST(NULLIF(
SUBSTRING(raw_payload+'+++',
CHARINDEX('+', raw_payload+'+++', 1) + 1,
CHARINDEX('+', raw_payload+'+++',
CHARINDEX('+', raw_payload, 1) +1) -
(CHARINDEX('+', raw_payload+'+++', 1) + 1))
,'') --<---CHANGE THIS LINE
AS FLOAT),
CAST(NULLIF(
REPLACE(
SUBSTRING(raw_payload+'+++',
CHARINDEX('+', raw_payload+'+++',
CHARINDEX('+', raw_payload+'+++', 1) +1) + 1,
LEN(raw_payload) - CHARINDEX('+', raw_payload,
CHARINDEX('+', raw_payload+'+++', 1) +1) + 1)
,'+','')
,'') --<---CHANGE THIS LINE
AS FLOAT)
FROM raw_data_pro
You would then need to read through and see what is not returning a numeric value in column 2 or 3, and somehow handle that.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 29, 2008 at 7:49 am
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.
New Error after uncommented
April 29, 2008 at 8:11 am
Then either your column # 2 or column # 3 is returning something that can't be cast as a float. Meaning - it's not a number.
So take the CAST out, and see if it flies:
SELECT raw_payload,
LEFT(raw_payload+'+++', CHARINDEX('+', raw_payload+'+++', 1)-1),
NULLIF(
SUBSTRING(raw_payload+'+++',
CHARINDEX('+', raw_payload+'+++', 1) + 1,
CHARINDEX('+', raw_payload+'+++',
CHARINDEX('+', raw_payload, 1) +1) -
(CHARINDEX('+', raw_payload+'+++', 1) + 1))
,'') --<---CHANGE THIS LINE
,
NULLIF(
REPLACE(
SUBSTRING(raw_payload+'+++',
CHARINDEX('+', raw_payload+'+++',
CHARINDEX('+', raw_payload+'+++', 1) +1) + 1,
LEN(raw_payload) - CHARINDEX('+', raw_payload,
CHARINDEX('+', raw_payload+'+++', 1) +1) + 1)
,'+','')
,'') --<---CHANGE THIS LINE
FROM raw_data_pro
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 29, 2008 at 8:37 am
That worked Thanks All,
Now one more missing peice...
I copy good data into a new table then I run the parsing query which seperates my data the way I want it now I want to Insert Into a new table with 3 fields
"Description Field would be Text" a "Usage Field would be a Number or decimal ??? " and a "Adjustable field would be a Number or decimal ???"
SAMPLE OF DATA IN THREE (NO COLUMN NAMES)
UV 100.0000 101.0000
TLMV 5620.0000 -25.0000
April 29, 2008 at 9:56 am
n1pcman (4/29/2008)
That worked Thanks All,Now one more missing peice...
I copy good data into a new table then I run the parsing query which seperates my data the way I want it now I want to Insert Into a new table with 3 fields
"Description Field would be Text" a "Usage Field would be a Number or decimal ??? " and a "Adjustable field would be a Number or decimal ???"
SAMPLE OF DATA IN THREE (NO COLUMN NAMES)
UV 100.0000 101.0000
TLMV 5620.0000 -25.0000
Based on the errors you've been reporting ("Error converting varchar to float") there is at least an instance somewhere of either the usage or the adjustable column not holding numeric data (meaning - it's alphanumeric).
Try setting just the usage field to be numeric, and try your insert. If it fails, then it's not holding only numeric data, and you can go find out what you need to get rid of (apparently more "bad data").
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 8 posts - 31 through 37 (of 37 total)
You must be logged in to reply to this topic. Login to reply