I have written a query for a formatted search that will do the following:
I set up a UDT that has entries for all zip codes in Colorado. There are no entries for other states. Also in this table, is a UDF that has the TaxCode. I set up a formatted search that populates the tax code field in Business Partner based on the Ship to zip code. This worked well with the following query:
SELECT T0.U_TaxCode FROM [dbo].[@COTC] T0 where T0.Code=$[crd1.zipcode]
However, I need to modify this to include a default code when there is no entry in the table for the entered zipcode. I am trying to use the Case function as in the following query:
SELECT T0.U_TaxCode FROM [dbo].[@COTC] T0 where T0.Code=$[crd1.zipcode]
CASE
WHEN U_TaxCode IS NULL Then 'OS'
ELSE U_TaxCode
END
OS is the default code. This is not working. Any suggestions other than entering every zip code into my table?