Foxpro conversion
-
I am having difficulty converting a query to TSQL the reason being the isnull in the nested iif statement. When I rewrite the code to the following it generates an error on the expression.
original
Select painscode,covtype,papatcode,ssn,rerefcode,insidnum as poliid,palastname as lastname,pafirstname as firstname,dob,npatage as age,sex as gender,;
admdate as notifdate,iif(not isnull(lt),left(lt,10),iif(isnull(estclpwk),space(10),str(estclpwk)))as esweeks,;
iif(readmit>1,"X"," ") as readmit,dailymax as dailyben,;
iif(isnull(provcat),space(10),iif(provcat = "A1",str(units/visits),provcat+ space(10)))as HrsPDay,visits/weeks as dayspwk,;
rediscdate,dx1 as primarydx,iif(rediscdate <> { / / },2,iif(not isnull(penauth),3,1)) as pendstatus,;
rate from xprt5i into cursor xprt5j
My rewrite:
SELECT PAINSCODE, COVTYPE, PAPATCODE, SSN, REREFCODE, INSIDNUM AS poliid, PALASTNAME AS lastname, PAFIRSTNAME AS firstname, DOB,
NPATAGE AS age, SEX AS gender, ADMDATE AS notifdate, STR(ESTCIPWK) AS esweeks,
CASE readmit WHEN readmit > 1 THEN 'X' ELSE ' ' END AS readmit, dailymas AS dailyben, CASE provcat WHEN provcat = 'A1' THEN str(units / visits)
ELSE provcat + space(10) END AS hrspday, visits / weeks AS dayspwk, rediscdate, dx AS primarydx,
CASE pendstatus WHEN rediscdate = ' ' THEN 2 WHEN penauth = ' ' THEN 3 ELSE 1 END AS pendstatus
FROM XPRT5i
Thanks
John Hammond, MCSE
-
I think(?) you want CONVERT( varchar(#), ESTCIPWK) AS esweeks. # is the number of characters you need.
Try that and we'll see....
I wasn't born stupid - I had to study.
-
As Farrell pointed out, first look at your use of the STR() function. This function only accepts the float data type. You also used the STR() function like this:
WHEN provcat = 'A1' THEN Str(units / visits)
Are one of those columns ( units or vists ) of type float? If they are both int, then the result is an integer and the call to STR() will fail. Alternatively, you could cast one of the values to float using CAST or CONVERT.
Also, you might want to add some additional code to handle the case where visits = 0, which would throw a division-by-zero error.
Finally, your original FoxPro code included:
iif(rediscdate <> { / / },2,iif(not isnull(penauth),3,1)) as pendstatus
Assuming rediscdate and penauth are datetime, would this be more accurate:
CASE
WHEN rediscdate IS NOT NULL THEN 2
WHEN penauth IS NOT NULL THEN 3
ELSE 1
END AS pendstatus
I think you got a syntax problem with that final CASE - you are mixing two different forms.
What is the datatype for the pendstatus column?
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply