SQL Server Performance

Query Help

Discussion in 'SQL Server 2005 General Developer Questions' started by robroo, Sep 19, 2006.

  1. robroo New Member

    I'm new to SQL. I've used Access to create queries and some basic knowledge of sql, however, I need help with a query.

    I'd like to create a new field based on a field in a table. In ACCESS, I could do this in my query by naming the variable and using an if then statement.

    How do I do this in sql - an udf or store procedure?

    What I have is a customer master table with addresses and a sales table with invoice dollars. I want my query to sort by the state if the customer's country is blank, or in the USA. If the customer is in Canada, then Canada, and all others would be International.

    Can anyone help me?

  2. Adriaan New Member

    Welcome to SQL Server!

    Your question seems to go three ways. Do you want to see all Canadian customers first, then all non-Canadian customers? Use a CASE construct to evaluate the Country column, and use a number to work out which one(s) come(s) first.

    ORDER BY CASE Customer.Country WHEN 'Canada' THEN 0 ELSE 1 END, Customer.Country, Customer.CustomerName

    I've added the Country column after that, so that the non-Canadian customers are sorted by Country too.
  3. robroo New Member

    Thanks for the help with the sort. I can get the report to sort the way I want, but I don't know how to define a new field. Do I need a user defined function or a stored procedure?
  4. Adriaan New Member

    For this sorting problem, you don't need a field - what I showed you was how to use an expression that evaluates existing columns and constant expressions.

    But if you do need to add a column to a table, then check out the ALTER TABLE syntax in Books Online.

Share This Page