View - substitute a field's value -- sql server new users

sql server new users : View - substitute a field's value

David Portas
2/16/2006 12:14:24 PM

[quoted text, click to view]

You can use CASE expressions in views.

SELECT ...,
CASE WHEN custid = 'xyz' THEN 'Rome' ELSE shipcity END AS shipcity
FROM ...

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

David Portas
2/16/2006 2:39:03 PM

[quoted text, click to view]

I'm not aware of any specific technical limitation.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Walter
2/16/2006 2:55:56 PM

I apologize for the cross post - not sure if these are the right groups.
MS SQL 7.0 SP3
I have a view that sums the orders for our customers. It sums the Revenue
and Qty for each month of the year for each customer, along with the ShipTo
city.
I then use the data in Crystal Report.

The issue I have is that one customer uses different ports in the ShipTo
field. My result set looks like this:
CustID1, JanQty, FebQty..., JanRev, FebRev, Shipcity1
CustID1, JanQty, FebQty..., JanRev, FebRev, Shipcity2
CustID1, JanQty, FebQty..., JanRev, FebRev, Shipcity3

The report gives me three sections for the different cities. I want to
replace the value of Shipcity to a fixed city if the CustID = 'xyz'.
I was hoping/thinking that there could be an If/Then I could use where: If
CustID = 'xyz', Then City = 'Rome'.

Can I do things like this with Views, or with Stored Procs? I am looking to
see if I can group/sum within Crystal.
Walter

Walter
2/16/2006 4:54:41 PM

Grr - that was dumb of me. Never thought about CASE in View creation.
THANKS!!
Is there a practical limit to the number of case Statements that can be
used?
Walter
[quoted text, click to view]

You can use CASE expressions in views.

SELECT ...,
CASE WHEN custid = 'xyz' THEN 'Rome' ELSE shipcity END AS shipcity
FROM ...

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server new users

home · blog · groups

Questions? Comments? Contact the dn