MySQL to SQL Server
-
Does anyone here know both MySQL and SQL Server? If so could you please help me?
I have a query in MySQL that I need to port to SQL Server. It looks pretty strange and I can't figure it out.
Here it is:
If(!isnull(PayDay), (AltValue * 1), 0) as totPaid
I know !isnull = IS NOT NULL, but that's about it....
Thank you
-
CASE WHEN PayDay IS NOT NULL THEN AltValue ELSE 0 END AS totPaid
Sounds about right??
-
It doesn't sound right...I can't imagine how it could be a CASE statement....
So, to translate, it's an option in a CASE statement
When PayDay isn't null, then it equals AltValue (AS totPaid)
If it is null, then make it Zero (AS totPaid)
Does that make me sound smart?
Thank you
-
I'll refrain from answering that question... This syntax is like the one in access and it seems like the right translation. Can you try it to see if it yields the same results?
-
Thanks, I'll give it a try
wow, you have like 5000 posts..that's crazy!
-
The crazy part is that I joined in may 2k4
. -
wow...are you employed by sqlservercentral.com?
Anyway, I wrote it out like this in SQL Server Query Analyzer:
altValue=CASE
WHEN PayDay IS NOT NULL
THEN (SELECT altValue AS TotPaid)
-
Hey, I'm a Grasshopper now!
-
What was wrong with the statement I posted?
No I'm not employed here.
-
Nothing was wrong...except that it was harder to read(at least for my eyes).
Is the version I posted the same, technically, as yours?
Here's yours:
CASE WHEN PayDay IS NOT NULL THEN AltValue ELSE 0 END AS totPaid
and mine:
altValue=CASE
WHEN PayDay IS NOT NULL
THEN (SELECT altValue AS TotPaid)
But mine doesn't have the ELSE statement. I'm not sure where you picked that up from. It must be from this part:
If(!isnull(PayDay), (AltValue * 1), 0) as totPaid
-
Does your statement actually work???
Yes the 0 comes from the red part.
Also you better get used to that syntax because it's the only way to do this in sql server.
-
It works...why wouldn't it? I just need to add an ELSE statement, and it will produce results.
But I'm going to try yours to see if it produces a different resultset
Thank you
-
My resultset was different. I think I may have translated the remaing MySQL code incorrectly. Here is the MySQL code.
, If(!isnull(PayDay), (altValue * 1), 0) as totPaid
, If(WRcurrentStatus = 110, (altValue * 1), 0) as totCancel
, If(!isnull(PayDay) || WRcurrentStatus = 110, 0, (altValue * 1)) as totOpen
, altValue * 1 as 'totBill Amt'
from
workrequest
and here is my translation:
altValue=CASE
WHEN PayDay IS NOT NULL
THEN (SELECT altValue AS TotPaid)
WHEN WRcurrentStatus=110
THEN (SELECT altValue AS TotCancel)
WHEN PayDay IS NOT NULL OR WRcurrentStatus=110
THEN (SELECT altValue AS TotOpen)
END
FROM workrequest
I am sure I'm missing something. The coder who had this project before me started to translate it, but then he quit. I'm the Photoshop girl here at the office, but I also know a bit of SQL and ASP, so I've taken over this project.
But thanks for you help thus far. I appreciate you lending your MySQL and SQL Server knowledge.
-
You need to have a case statement per column >>
, 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
-
Oh I see...I understand now. Thanks for sharing your knowledge. It's very valuable.
I will modify my SQL Server code and examine the resultset.
Again, Thank you wise one
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply