January 7, 2010 at 7:03 pm
I have the below code that I am trying to write, but it is giving me the above error message:
insert into #BillingResults(ServiceLineID, ClientID, BillingType, ReportType, DisplayOrder, ServiceLineDescription)
(
SELECT Distinct r2.ServiceLineID, r2.ClientID, n.BillingType, r.ReportType, r2.DisplayOrder, r2.ServiceLineDescription
From @NumbersByWeek n
Cross Join
(
Select 'Billing' as ReportType
UNION
Select 'Margin' as ReportType
) r
Cross Join
(
--get a row for each serviceline and a row for each client
--we'll join to the map later to populate the serviceline numbers for all clients that belong to it
Select Distinct c.ServiceLineID, 0 as clientid, displayorder, servicelinedescription
From
custidtocustmap ccr
INNER JOIN clientrollup c (NOLOCK) ON c.rollupid = ccr.parentcustid
INNER JOIN serviceline s (NOLOCK) ON c.servicelineid = s.servicelineid
where ccr.custrowid IN (select clientid from @NumbersByWeek)
and (c.servicelineid = @ServiceLineID Or @ServiceLineID = -1)
UNION
select distinct 0 as servicelineid, n.clientid, 0 as displayorder,
from @NumbersByWeek n
UNION --consolidated (if asked for)4
select -1,-1,-1
from ServiceLine
where @ServiceLineID = -1
) r2
)
The issue is with this select statement: select -1,-1,-1, -1
Because servicelinedescription is a varchar(255), I cannot enter -1, what can I put in the select statement as a wild car to make it work?
Thanks for all your help!!!
January 7, 2010 at 8:40 pm
Hi,
It’s very hard to assume the table schema in your statement, however the union operation says from first line data type is so must maintain to all union columns
Col1 ServiceLineID, Col2 clientid. Col3 displayorder, Col4 servicelinedescription
UNION
Col1 servicelineid, Col2 clientid, Col3 displayorder, col4 null
UNION
Col1 -1, Col2 -1, Col3 -1, Col4 ‘-1’
Convert the last column value from int to varchar.
select -1,-1,-1,'-1'
January 7, 2010 at 8:54 pm
I cant read your full err msg but it seems you are trying to insert the value 'staffing' to column whose data type does not support char/varchar data type. Check that and as the previous post mentioned data types of all UNION columns must be the same.
"Keep Trying"
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply