Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL - Filtered on QTY field only in a View

Posted on 2012-03-28
7
Medium Priority
?
380 Views
Last Modified: 2012-03-28
New to using SQL - I created a view and need to add a field for the Quantity Field, I need this field to exclude one product line in my table.  However, I need the Product Line on the entire table but just filtered for the quantity field. Is there a way to do this?  

For example - My QTY field includes Freight.  My $$ fields need to include the Freight $$'s so I can't eliminate the Freight from the entire table I just don't want the QTY field to include the Freight numbers, as it doubles the Qty Amount.  

In Crystal - I would create a formula as:

If {ProductLine} = "FRGT" then 0 else {Qty}

Any help would be great!  Trying to not do this report in Crystal.  

Thank you.

Using MS SQL Management Studio - 2008 R2 - Free version
0
Comment
Question by:eleale
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 13

Expert Comment

by:AielloJ
ID: 37777831
eleale:

Can you post your data model?

Regards,

AielloJ
0
 

Author Comment

by:eleale
ID: 37777869
AielloJ:

Is this what you need?  See attached.

Thanks.
EXPERTS-SQL.docx
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 37777917
SOmething like

Case ProductLine
      "FRGT"  :  Cost
      Others  :  Cost * Qty
End  As ExtendedCost

mlmcc
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:eleale
ID: 37777928
Hi Mlmcc:

Where in SQL do you excute a Case Statement? Is it part of the SQL statement?

Thanks.
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 2000 total points
ID: 37777942
It is part of the SELECT

SELECT fields, CASE .... , fields
FROM ....

I don't have a SQL database here so the syntax is from memory.

mlmcc
0
 

Author Comment

by:eleale
ID: 37777947
I will give it a try - Thank you.
0
 

Author Closing Comment

by:eleale
ID: 37778469
Thank you!
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question