Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
elsietina
Starting Member
12 Posts |
 Posted - 2002-01-15 : 14:09:25
|
| I am having problems converting this code to sQl server.SELECT AOA_ODs_2001_10_08.*, (Not IsNull([PC1].[SpecialtyCode])) Or (Not IsNull([PC2].[SpecialtyCode])) AS IsPrimaryCare, (Nz([Prac_TYP],"")="90") Or (Nz([MAJ_PRAC],"")="RET") Or (Nz([MIN_PRAC],"")="RET") Or (Nz([PROF_EMP],"")="90") Or (Nz([DUESCAT],0)=9) AS IsRetiredFROM (AOA_ODs_2001_10_08 LEFT JOIN PrimaryCareSpecialtyList AS PC1 ON AOA_ODs_2001_10_08.MAJ_PRAC = PC1.SpecialtyCode) LEFT JOIN PrimaryCareSpecialtyList AS PC2 ON AOA_ODs_2001_10_08.MIN_PRAC = PC2.SpecialtyCode; |
|
|
mono
Starting Member
36 Posts |
Posted - 2002-01-15 : 14:25:19
|
| In SQL Server IsNull means "if the first argument is null then return the second argument else return the first argument" i.e. it more like Access's Nz.Rewrite "Nz(x, y)" as "isnull(x, y)"and "(not) isnull(x)" as "x is (not) null" though that expression not useable as a column as such.Your IsPrimaryCare expression should be something like:(case when ([PC1].[SpecialtyCode] is not null) or ([PC2].[SpecialtyCode] is not null) then 1 else 0 end)The IsRetired retired expression will be:(case when (isnull([Prac_TYP],"") = "90") or (isnull([MAJ_PRAC],"") ="RET") or .... then 1 else 0 end)Check the BOL for CASE, ISNULL and IS NULL. ISNULL is just a shorthand for a longer CASE expression, as is COALESCE which may also be of interest.HTH, |
 |
|
|
|
|
|
|
|
Converting Access query to MS SQL Server View