MSAccess to SQL Server Action Query Convert Conundrum! 1

[proximity]

Hi,

I am new SQLServer (2005 version) and have to try and convert the query below. Any pointers?


INSERT INTO tblRackList ( loc, cat, t_stamp, status )
SELECT tblRackList_Temp.loc, tblRackList_Temp.cat, tblRackList_Temp.t_stamp, IIf([tblCatNumber.status] Is Null,"N/A",[tblCatNumber.status]) AS a
FROM tblRackList_Temp LEFT JOIN tblCatNumber ON tblRackList_Temp.cat = tblCatNumber.cat
WHERE (((tblRackList_Temp.cat) Is Not Null) AND ((tblRackList_Temp.status)="PIK"));

Many thanks,

--
SM

 

[bborissov]

INSERT INTO tblRackList ( loc, cat, t_stamp, status )
SELECT tblRackList_Temp.loc, 
       tblRackList_Temp.cat,
       tblRackList_Temp.t_stamp,
       ISNULL(tblCatNumber.status,'N/A')
FROM tblRackList_Temp
     LEFT JOIN tblCatNumber 
          ON tblRackList_Temp.cat = tblCatNumber.cat
WHERE tblRackList_Temp.cat Is Not Null AND 
      tblRackList_Temp.status = 'PIK'

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[proximity]

Thanks Borislav,

That worked beautifully - and gives me a working example to check against for some other similar queries to work with.

Many thanks,

-
SM

 

[SQLSister]

IIF can also be replaced with the CASE Statement if you are doing more than just checking for nulls. And Coalesce might help in other statements as well.

"NOTHING is more important in a database than integrity." ESquared