title: CASE statement within a JOIN query in SQL
body: <p>This query is to print the JOINED tables with the <code>creditcard</code> column showing "cash" for every row
  where there is no card name.</p>
  
  <p>I wrote the following, but it results in error:</p>
  
  <pre><code>SELECT  st.Name Territoryname,pp.LastName SalesPerson,ps.Name ShipMethod,
          sc.CardType CreditCardType,
          soh.OrderDate,soh.TotalDue
  FROM    Sales.SalesOrderHeader soh
  JOIN    Person.Person pp
  ON      soh.SalesPersonID=pp.BusinessEntityID
  JOIN    Purchasing.ShipMethod ps
  ON      ps.ShipMethodID=soh.ShipMethodID
  JOIN    Sales.CreditCard sc (CASE WHEN sc.CardType='Distinguish' THEN 'Cash' ELSE sc.CardType     END),
  ON      sc.CreditCardID=soh.CreditCardID
  JOIN    Sales.SalesTerritory st
  ON      st.TerritoryID=soh.TerritoryID
  </code></pre>