September 16, 2005 at 8:36 am
With the help of RGR'us, I modified a query from MySQL to SQL Server.
Here is the code:
CASE WHEN Payday IS NOT NULL THEN altValue ELSE 0 END AS totPaid
,CASE WHEN WRcurrentStatus = 110 THEN altValue ELSE 0 END AS totCancel
,CASE WHEN Payday IS NOT NULL OR WRcurrentStatus = 110 THEN 0 ELSE altValue END AS totOpen
,altValue AS totBillAmount
FROM workrequest
Now I keep receiving this error:
Syntax error converting the varchar value '84.56' to a column of data type int.
Just for reference,
PayDay is datetime(8)
WRcurrentStatus is decimal(5)
altValue is varchar(6)
So my guess is that this query is trying to convert altValue to an int type. But in the query, I don't see that anywhere.
Can anyone see something that I'm missing?
Thank you
September 16, 2005 at 8:41 am
Go with ElSE '0' instead of ELSE 0
September 16, 2005 at 8:42 am
Or actually you could simply change the altvalue to a decimal datatype (assuming it's doable and still the correct design).
September 16, 2005 at 10:34 am
I am not sure if the statment is going to do want you want, but nonetheless I think the two possible values in the case need to match.
eg.
case when snee then 'a' else 'b' end
case when snee then 0 else 1 end
case when snee then 1.5 else 2.5 end
so...
CASE WHEN Payday IS NOT NULL THEN altValue ELSE 0.0 END AS totPaid
,CASE WHEN WRcurrentStatus = 110 THEN altValue ELSE 0.0 END AS totCancel
,CASE WHEN Payday IS NOT NULL OR WRcurrentStatus = 110 THEN 0.0 ELSE altValue END AS totOpen
,altValue AS totBillAmount
FROM workrequest
If that doesn't work perhaps: cast(0 as decimal) ??
Or mopre convolutedly:
ISNULL(CASE WHEN Payday IS NOT NULL THEN altValue ELSE NULL END) AS totPaid
...Not sure if that will work, ISNULL() converts nulls to 0 but not sure if you can nest the case.
just ideas for you.
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)
September 16, 2005 at 10:39 am
Whoops, sorry you need to provide the replacement value for isnull()
Not sure if this is very efficient but you could use a derived statement:
select isnull(t.totPaid,0), isnull(t.totCancel,0), isnull(t.totOpen,0), isnull(t.totBillAmount,0)
From (select...
CASE WHEN Payday IS NOT NULL THEN altValue ELSE NULL END AS totPaid
,CASE WHEN WRcurrentStatus = 110 THEN altValue ELSE NULL END AS totCancel
,CASE WHEN Payday IS NOT NULL OR WRcurrentStatus = 110 THEN NULL ELSE altValue END AS totOpen
,altValue AS totBillAmount
FROM workrequest) t
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)
September 16, 2005 at 11:21 am
Chisholmd
Thank you for your response. I understand your SQL statement, but I'm not sure where it would fit in the overall query.
Here is the total query:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cnettemp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[cnettemp]
GO
SELECT DEkeyValue AS JobNumber, DEenteredDate AS PayDay INTO cnettemp
FROM diaryentry inner join workrequest on DEkeyValue=WRid
WHERE WRenteredDate BETWEEN '2005-04-04 00:00:00' AND '2005-04-11 00:00:00'
AND DEentryTypeID='ENRPAY'
GO
SELECT WRid,WRsendToID, WRbillToID, WRrequestorID,
IsNull(sendto.COcontactNameLast,'NA') AS 'Send To',
IsNull(billto.COcontactNameLast,'NA') AS 'Bill To',
IsNull(req.COcontactNameLast,'NA') AS 'Client Rep',
CASE WHEN Payday IS NOT NULL THEN altValue ELSE '0' END AS totPaid
,CASE WHEN WRcurrentStatus = 110 THEN altValue ELSE '0' END AS totCancel
,CASE WHEN Payday IS NOT NULL OR WRcurrentStatus = 110 THEN '0' ELSE altValue END AS totOpen
,altValue AS totBillAmount
FROM workrequest
LEFT JOIN cnettemp AS cnt ON WRid=JobNumber
LEFT JOIN clientoffice sendto ON WRsendToID=sendto.COid
LEFT JOIN clientoffice billto ON WRbillToID=billto.COid
LEFT JOIN clientoffice req ON WRrequestorID=req.COid
, diaryentry
WHERE 1=1
AND WRenteredDate >= '2005-04-03' AND WRenteredDate < '2005-05-03'
AND DEkeyValue=WRid
AND diaryentry.DEentryTypeID='ENRINV'
AND DEdeletedDate Is Null
AND WRjobTypeID <> 'TRJTINT'
ORDER BY WRid
GO
My resultset is still off a bit....still working on it. I can't wait til we hire a programmer and I can go back to Photoshop!
Thank you.
September 16, 2005 at 11:23 am
Hello RGR'us, thank you for your comment, changing it to '0' got rid of the error.
Thank you dear
September 16, 2005 at 12:18 pm
"My resultset is still off a bit....still working on it. I can't wait til we hire a programmer and I can go back to Photoshop!"
LOL Your doing great!
If the '0' is working for you then thats good enough.
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply