using case when - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

using case when

Author

Topic

rob41
Yak Posting Veteran

67 Posts

Posted - 2009-12-22 : 13:56:25

I'm converting some code from Access, and I'm trying to convert the IIf to CASE WHEN. The orginal code is below and my modifications are under. The code still will not work for me

-original sql from access 07 query -
Iif([T Ship].[SH PU Rcvd EDI 0/1] = 1,[T Ship].[SH PU Rcvd EDI 0/1],
Iif([T Ship].[SH PU Rcvd Web 0/1] = 1,[T Ship].[SH PU Rcvd Web 0/1],0)) AS [Depart PU EDI],
Iif([T Ship].[SH Arv D Loc EDI 0/1] = 1,[T Ship].[SH Arv D Loc EDI 0/1],
Iif([T Ship].[SH Arv D Loc Web 0/1] = 1,[T Ship].[SH Arv D Loc Web 0/1],0)) AS [Arv D EDI],

-my corrections

CASE WHEN([T Ship].[SH PU Rcvd EDI 0/1] = 1 THEN [T Ship].[SH PU Rcvd EDI 0/1],
WHEN([T Ship].[SH PU Rcvd Web 0/1] = 1 THEN [T Ship].[SH PU Rcvd Web 0/1], ELSE '0')) END AS [Depart PU EDI],
WHEN([T Ship].[SH Arv D Loc EDI 0/1] = 1 THEN [T Ship].[SH Arv D Loc EDI 0/1],
WHEN([T Ship].[SH Arv D Loc Web 0/1] = 1 THEN [T Ship].[SH Arv D Loc Web 0/1], ELSE '0')) END AS [Arv D EDI],

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-12-22 : 14:06:53

I made some minor tweaks. I got rid of the () and the "," at the end of the WHEN statement. I also got rif of the '' in else statement. This because the result has to be all of one type and 1 and '1' are different types.

Jim


CASE 
     WHEN [T Ship].[SH PU Rcvd EDI 0/1] = 1 THEN [T Ship].[SH PU Rcvd EDI 0/1]  
     WHEN [T Ship].[SH PU Rcvd Web 0/1] = 1 THEN [T Ship].[SH PU Rcvd Web 0/1]   
      ELSE 0  
   END AS [Depart PU EDI],

CASE
    WHEN  [T Ship].[SH Arv D Loc EDI 0/1] = 1 THEN [T Ship].[SH Arv D Loc EDI 0/1] 
    WHEN  [T Ship].[SH Arv D Loc Web 0/1] = 1 THEN [T Ship].[SH Arv D Loc Web 0/1] 
    ELSE 0 
END AS [Arv D EDI],

Everyday I learn something that somebody else already knew

rob41
Yak Posting Veteran

67 Posts

Posted - 2009-12-22 : 15:09:35

Thanks Jim,

Worked great!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources