March 16, 2006 at 6:56 am
Does anyone here know MySQL and MS-SQL?
I have a MySQL query that I am trying to convert to MS-SQL, but everytime I run it in SQL Query Analyzer, it returns results inconsistent when I run the MySQL query.
In the MySQL query, a value appears in either the totPaid, totCancel, or TotOpen column. And that same value appears in the totBill Amt column.
But in my MS-SQL translation, the totPaid, totCancel, and TotOpen columns are always blank. And I can't figure out why.
Here is the MySQL Query:
create temporary table copynettemp
select DEkeyValue as JobNumber, min(DATE_FORMAT(DEenteredDate, '%Y-%m-%d')) as PayDay
from diaryentry, workrequest
where 1=1
AND DATE_FORMAT(WRenteredDate, '%Y-%m-%d') between '$minDate' and '$maxDate'
and DEentryTypeID = 'ENRPAY'
group by DEkeyValue
RunSQL 'this line is from a PHP script that I am converting to ASP
select
WRid as 'Job'
, DATE_FORMAT(WRenteredDate, '%m/%d') as Dt
, WRcurrentStatus as Stat
, WRclaimNumber as 'Claim'
, CONCAT_WS(' ', req.COcontactNameFirst, req.COcontactNameLast) AS 'Claim Rep'
, DATE_FORMAT(WRstatusUpdated, '%m/%d/%y') as Updated
, If(!isnull(PayDay), (DEaltKeyValue * 1), 0) as totPaid
, If(WRcurrentStatus = 110, (DEaltKeyValue * 1), 0) as totCancel
, If(!isnull(PayDay) || WRcurrentStatus = 110, 0, (DEaltKeyValue * 1)) as totOpen
, DEaltKeyValue * 1 as 'totBill Amt'
from
workrequest
left join copynettemp as cnt on (WRid = JobNumber)
left join clientoffice as req on (WRrequestorID = req.COid)
left join clientoffice as billTo on (WRbillToID = billTo.COid)
left join clientoffice as sendTo on (WRsendToID = sendTo.COid)
, diaryentry
WHERE 1=1
AND DATE_FORMAT(WRenteredDate, '%Y-%m-%d') between '$minDate' and '$maxDate'
AND DEkeyValue = WRid
AND DEentryTypeID = 'ENRINV'
AND DEdeletedDate is null
AND WRjobTypeID != 'TRJTINT'
group by WRid
ORDER BY WRenteredDate
And here is query in MS-SQL that I have translated:
SELECT DEkeyValue AS JobNumber, DEentryTypeID, DEenteredDate AS PayDay INTO #copynettemp
FROM diaryentry, workrequest
WHERE 1=1
AND WRenteredDate BETWEEN '2005-04-03 00:00:00' AND '2005-05-20 00:00:00'
AND DEentryTypeID='ENRPAY'
GO
SELECT WRid, WRenteredDate, WRcurrentStatus, WRclaimNumber, WRstatusUpdated, WRsendToID, WRbillToID, WRrequestorID,
IsNull(sendto.COcontactNameLast,'NA') AS 'Send To',
IsNull(billto.COcontactNameLast,'NA') AS 'Bill To',
IsNull(req.COcontactNameLast,'NA') AS 'Client Rep',
DEaltKeyValue=CASE
WHEN PayDay IS NOT NULL
THEN (SELECT DEaltKeyValue AS TotPaid)
WHEN WRcurrentStatus=110
THEN (SELECT DEaltKeyValue AS TotCancel)
WHEN PayDay IS NOT NULL OR WRcurrentStatus=110
THEN (SELECT DEaltKeyValue AS TotOpen)
END
FROM workrequest
LEFT JOIN #copynettemp AS cnt ON WRid=JobNumber
LEFT JOIN clientoffice sendto ON WRsendToID=sendto.COid
LEFT JOIN clientoffice billto ON WRsendToID=billto.COid
LEFT JOIN clientoffice req ON WRsendToID=req.COid
, diaryentry
WHERE 1=1
AND WRenteredDate BETWEEN '2005-04-03 00:00:00' AND '2005-05-20 00:00:00'
AND DEkeyValue=WRid
AND diaryentry.DEentryTypeID='ENRINV'
AND DEdeletedDate Is Null
AND WRjobTypeID <> 'TRJTINT'
GO
March 16, 2006 at 7:57 am
Something is missing
in the mssql query your using a case statement to supply a value for DEaltKeyValue, it does not look like your outputting the same amount of columns.
Also structuring the case this way is improper
SELECT WRid, WRenteredDate, WRcurrentStatus, WRclaimNumber, WRstatusUpdated, WRsendToID, WRbillToID, WRrequestorID,
IsNull(sendto.COcontactNameLast,'NA') AS 'Send To',
IsNull(billto.COcontactNameLast,'NA') AS 'Bill To',
IsNull(req.COcontactNameLast,'NA') AS 'Client Rep',
, coalesce(PayDay,(DEaltKeyValue * 1), 0) as totPaid
, case WRcurrentStatus when 110 then (DEaltKeyValue * 1)
else 0 end as totCancel
, case when payday is null and WRcurrentStatus = 110 then 0
else (DEaltKeyValue * 1) end as totOpen
, DEaltKeyValue * 1 as 'totBill Amt'
FROM workrequest
LEFT JOIN #copynettemp AS cnt ON WRid=JobNumber
LEFT JOIN clientoffice sendto ON WRsendToID=sendto.COid
LEFT JOIN clientoffice billto ON WRsendToID=billto.COid
LEFT JOIN clientoffice req ON WRsendToID=req.COid
, diaryentry
WHERE 1=1
AND WRenteredDate BETWEEN '2005-04-03 00:00:00' AND '2005-05-20 00:00:00'
AND DEkeyValue=WRid
AND diaryentry.DEentryTypeID='ENRINV'
AND DEdeletedDate Is Null
AND WRjobTypeID <> 'TRJTINT'
Actually having a case there makes absolutely no sense. I took it out and made the same # fields returned
March 16, 2006 at 8:02 am
Wow, that's a lot simpler-looking than my original query...
Also, when I ran your query, I received a "Syntax error converting the varchar value '40.39' to a column of data type int." error.
March 16, 2006 at 8:20 am
try this
SELECT WRid, WRenteredDate, WRcurrentStatus, WRclaimNumber, WRstatusUpdated, WRsendToID, WRbillToID, WRrequestorID,
IsNull(sendto.COcontactNameLast,'NA') AS 'Send To',
IsNull(billto.COcontactNameLast,'NA') AS 'Bill To',
IsNull(req.COcontactNameLast,'NA') AS 'Client Rep',
, coalesce(PayDay,(DEaltKeyValue * 1), 0.00) as totPaid
, case WRcurrentStatus when 110 then (DEaltKeyValue * 1)
else 0.00 end as totCancel
, case when payday is null and WRcurrentStatus = 110 then 0.00
else (DEaltKeyValue * 1) end as totOpen
, DEaltKeyValue * 1 as 'totBill Amt'
FROM workrequest
LEFT JOIN #copynettemp AS cnt ON WRid=JobNumber
LEFT JOIN clientoffice sendto ON WRsendToID=sendto.COid
LEFT JOIN clientoffice billto ON WRsendToID=billto.COid
LEFT JOIN clientoffice req ON WRsendToID=req.COid
, diaryentry
WHERE 1=1
AND WRenteredDate BETWEEN '2005-04-03 00:00:00' AND '2005-05-20 00:00:00'
AND DEkeyValue=WRid
AND diaryentry.DEentryTypeID='ENRINV'
AND DEdeletedDate Is Null
AND WRjobTypeID <> 'TRJTINT'
you may need to perform a data type cast on those fields, not sure
March 16, 2006 at 8:33 am
Oh ok, so something like:
SELECT WRid, WRenteredDate, WRcurrentStatus, WRclaimNumber, WRstatusUpdated, WRsendToID, WRbillToID, WRrequestorID,
IsNull(sendto.COcontactNameLast,'NA') AS 'Send To',
IsNull(billto.COcontactNameLast,'NA') AS 'Bill To',
IsNull(req.COcontactNameLast,'NA') AS 'Client Rep'
, coalesce(PayDay,(DEaltKeyValue * 1), CAST('0.00' AS Int)) as totPaid
, case WRcurrentStatus when 110 then (DEaltKeyValue * 1)
else CAST('0.00' AS Int) end as totCancel
, case when payday is null and WRcurrentStatus = 110 then CAST('0.00' AS Int)
else (DEaltKeyValue * 1) end as totOpen
, DEaltKeyValue * 1 as 'totBill Amt'
FROM workrequest
LEFT JOIN #copynettemp AS cnt ON WRid=JobNumber
LEFT JOIN clientoffice sendto ON WRsendToID=sendto.COid
LEFT JOIN clientoffice billto ON WRsendToID=billto.COid
LEFT JOIN clientoffice req ON WRsendToID=req.COid
, diaryentry
WHERE 1=1
AND WRenteredDate BETWEEN '2005-04-03 00:00:00' AND '2005-05-20 00:00:00'
AND DEkeyValue=WRid
AND diaryentry.DEentryTypeID='ENRINV'
AND DEdeletedDate Is Null
AND WRjobTypeID <> 'TRJTINT'
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply