|
Home > Archive > MS SQL Server > February 2006 > View - substitute a field's value
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
View - substitute a field's value
|
|
| Walter 2006-02-17, 7:23 am |
| 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
| |
| David Portas 2006-02-17, 7:23 am |
| Walter wrote:
> 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
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
--
| |
| Walter 2006-02-17, 7:23 am |
| 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
"David Portas" < REMOVE_BEFORE_REPLYI
NG_dportas@acm.org> wrote in message
news:1140120864.811078.300790@g44g2000cwa.googlegroups.com...
Walter wrote:
> 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
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 2006-02-17, 7:23 am |
| Walter wrote:
> Is there a practical limit to the number of case Statements that can be
> used?
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
--
|
|
|
|
|