Help _ Error Converting data type varchar to numer

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help _ Error Converting data type varchar to numer

Author

Topic

zwheeler
Starting Member

25 Posts

Posted - 2011-09-26 : 13:40:16

Hi
I am running a query that someome else wrote however, i keep getting the above error. I tried to create a case search query but I am still getting an error.
the error is occurin for fk_s3200_005

In the select i even tried the case search

case when len([p_005.account_smalltext]) = 0 THEN null
when ISNUMERIC([p_005.account_smalltext]) = 0 THEN null
ELSE [p_005.account_smalltext]
END AS [p_005.account_smalltext]

But in the above case statement it does not recognize the field: p_005.account_smalltext ---- SO it gives different error altogether.

So i put the case in the section that defined p_005 and the select runs however, when i do the insert it provides the Conversion Error.

INSERT INTO stg_dr_afs_s3200 (
[fk_afs_id],
[fk_afs_header_id] ,
[fk_afs_project_id] ,
[fk_s3200_005], [tag_s3200_010],
[tag_s3200_020] ,
[tag_s3200_030] ,
[tag_s3200_040] ,
[tag_s3200_045] ,
[tag_s3200_050],
[tag_s3200_060] ,
[tag_s3200_070] ,
[tag_s3200_080] ,
[tag_s3200_090],
[tag_s3200_100] ,
[tag_s3200_110] ,
[tag_s3200_120] ,
[tag_s3200_140],
[tag_s3200_150],
[date_s3200_130]
)
SELECT DISTINCT
h.pk_afs_id,
an.project_afs_header_id,
an.submission_project_id,
p_005.account_smalltext,

a_010.account_smalltext,
b_020.account_smalltext,
c_030.account_smalltext,
d_040.account_smalltext,
e_045.account_smalltext,
f_050.account_smalltext,
g_060.account_smalltext,
h_070.account_smalltext,
i_080.account_smalltext,
j_090.account_smalltext,
k_100.account_smalltext,
l_110.account_smalltext,
m_120.account_smalltext,
n_140.account_smalltext,
o_150.account_smalltext,
q_130.account_date
FROM stg_dr_project_afs_s3200 an
INNER JOIN oe_mf_data_mart..mf_afs_header h
ON an.project_afs_header_id = h.fk_afs_header_id AND an.submission_project_id = h.fk_afs_project_id
LEFT JOIN (
SELECT project_afs_header_id, submission_project_id, account_smalltext
FROM stg_dr_project_afs_s3200
WHERE account_number_ref_id = 611
) a_010 ON an.project_afs_header_id = a_010.project_afs_header_id AND an.submission_project_id = a_010.submission_project_id
LEFT JOIN (
SELECT project_afs_header_id, submission_project_id, account_smalltext
FROM stg_dr_project_afs_s3200
WHERE account_number_ref_id = 612
) b_020 ON an.project_afs_header_id = b_020.project_afs_header_id AND an.submission_project_id = b_020.submission_project_id
LEFT JOIN (
SELECT project_afs_header_id, submission_project_id, account_smalltext
FROM stg_dr_project_afs_s3200
WHERE account_number_ref_id = 613
) c_030 ON an.project_afs_header_id = c_030.project_afs_header_id AND an.submission_project_id = c_030.submission_project_id
LEFT JOIN (
SELECT project_afs_header_id, submission_project_id, account_smalltext
FROM stg_dr_project_afs_s3200
WHERE account_number_ref_id = 614
) d_040 ON an.project_afs_header_id = d_040.project_afs_header_id AND an.submission_project_id = d_040.submission_project_id
LEFT JOIN (
SELECT project_afs_header_id, submission_project_id, account_smalltext
FROM stg_dr_project_afs_s3200
WHERE account_number_ref_id = 745
) e_045 ON an.project_afs_header_id = e_045.project_afs_header_id AND an.submission_project_id = e_045.submission_project_id
LEFT JOIN (
SELECT project_afs_header_id, submission_project_id, account_smalltext
FROM stg_dr_project_afs_s3200
WHERE account_number_ref_id = 615
) f_050 ON an.project_afs_header_id = f_050.project_afs_header_id AND an.submission_project_id = f_050.submission_project_id
LEFT JOIN (
SELECT project_afs_header_id, submission_project_id, account_smalltext
FROM stg_dr_project_afs_s3200
WHERE account_number_ref_id = 616
) g_060 ON an.project_afs_header_id = g_060.project_afs_header_id AND an.submission_project_id = g_060.submission_project_id
LEFT JOIN (
SELECT project_afs_header_id, submission_project_id, account_smalltext
FROM stg_dr_project_afs_s3200
WHERE account_number_ref_id = 617
) h_070 ON an.project_afs_header_id = h_070.project_afs_header_id AND an.submission_project_id = h_070.submission_project_id
LEFT JOIN (
SELECT project_afs_header_id, submission_project_id, account_smalltext
FROM stg_dr_project_afs_s3200
WHERE account_number_ref_id = 618
) i_080 ON an.project_afs_header_id = i_080.project_afs_header_id AND an.submission_project_id = i_080.submission_project_id
LEFT JOIN (
SELECT project_afs_header_id, submission_project_id, account_smalltext
FROM stg_dr_project_afs_s3200
WHERE account_number_ref_id = 619
) j_090 ON an.project_afs_header_id = j_090.project_afs_header_id AND an.submission_project_id = j_090.submission_project_id
LEFT JOIN (
SELECT project_afs_header_id, submission_project_id, account_smalltext
FROM stg_dr_project_afs_s3200
WHERE account_number_ref_id = 620
) k_100 ON an.project_afs_header_id = k_100.project_afs_header_id AND an.submission_project_id = k_100.submission_project_id
LEFT JOIN (
SELECT project_afs_header_id, submission_project_id, account_smalltext
FROM stg_dr_project_afs_s3200
WHERE account_number_ref_id = 621
) l_110 ON an.project_afs_header_id = l_110.project_afs_header_id AND an.submission_project_id = l_110.submission_project_id
LEFT JOIN (
SELECT project_afs_header_id, submission_project_id, account_smalltext
FROM stg_dr_project_afs_s3200
WHERE account_number_ref_id = 622
) m_120 ON an.project_afs_header_id = m_120.project_afs_header_id AND an.submission_project_id = m_120.submission_project_id
LEFT JOIN (
SELECT project_afs_header_id, submission_project_id, account_smalltext
FROM stg_dr_project_afs_s3200
WHERE account_number_ref_id = 746
) n_140 ON an.project_afs_header_id = n_140.project_afs_header_id AND an.submission_project_id = n_140.submission_project_id
LEFT JOIN (
SELECT project_afs_header_id, submission_project_id, account_smalltext
FROM stg_dr_project_afs_s3200
WHERE account_number_ref_id = 747
) o_150 ON an.project_afs_header_id = o_150.project_afs_header_id AND an.submission_project_id = o_150.submission_project_id
LEFT JOIN (
SELECT project_afs_header_id, submission_project_id,
case when len([account_smalltext]) = 0 THEN null
when ISNUMERIC([account_smalltext]) = 0 THEN null
ELSE [account_smalltext]
END AS [account_smalltext]

FROM stg_dr_project_afs_s3200
WHERE account_number_ref_id = 887

) p_005 ON an.project_afs_header_id = p_005.project_afs_header_id AND an.submission_project_id = p_005.submission_project_id
LEFT JOIN (
SELECT project_afs_header_id, submission_project_id, account_date
FROM stg_dr_project_afs_s3200
WHERE account_number_ref_id = 670
) q_130 ON an.project_afs_header_id = q_130.project_afs_header_id AND an.submission_project_id = q_130.submission_project_id

Any insight would be greatly appreciated

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-26 : 13:48:58

try casting null to numeric

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources