[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Can I make ELSE return an empty cell instead of zero?

Posted on 2011-10-21
10
Medium Priority
?
189 Views
Last Modified: 2012-05-12
Can I make ELSE return an empty cell instead of zero:

CASE PayCode
WHEN 9 THEN OriginalPrice
ELSE 0
END





Here is a sample of the section I am working on.
TRUNCATE TABLE FactBalloonSales;
INSERT INTO
                          dbo.FactBalloonSales
            (
            , WorkDateKey
            , EmployeeKey
            , EquipmentKey
            , LotKey
            , CustomerKey
                                          , NoChargeAmount
SELECT       
            , [WorkDayKey] = CONVERT(BIGINT, CONVERT(VARCHAR, workday, 112))
            , [EmployeeKey]= coalesce(de.EmployeeKey,0)
            , [EquipmentKey]= 1
            , [LotLocationKey] = COALESCE(L.LotKey,0)
            , [CustomerKey] = 0
                                           , [FreeOfCharge] =
                                             CASE PayCode
                                             WHEN 9 THEN OriginalPrice
                                             ELSE 0
                                             END
                                             ,[OriginalPrice]
FROM                                   Server.Table (nolock)
JOIN  DimLot L
                  ON t.locationid = l.balloontypeid
                  LEFT JOIN PaymentTypeDim p                                                                  
                  ON p.paymenttypekey = t.paycode      
                  LEFT JOIN DimTime dt
                  ON  DATEPART(hh,(t.dtout)) = dt.[Hour24]
                  AND DATEPART(mi,(t.dtout)) = dt.[Minute]
                  
                  
                  LEFT JOIN #EmpHours EH
                  ON EH.LocationID = L.EquipmentID
                  and t.dtout between EH.PunchIn and EH.PunchOut
                  
                  LEFT JOIN DimEmployee DE
                  ON DE.EmployeeID = EH.EmployeeID
      
                                    
WHERE
      t.dtout >= '09/15/2011' --'10/1/2010'--'4/14/2011' --'5/12/2011'
0
Comment
Question by:rhservan
10 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 400 total points
ID: 37008971
Try this:

CASE PayCode
WHEN 9 THEN OriginalPrice
ELSE Null
END
0
 
LVL 26

Assisted Solution

by:tigin44
tigin44 earned 400 total points
ID: 37008973
use

CASE PayCode
WHEN 9 THEN OriginalPrice
ELSE NULL
END
0
 

Author Comment

by:rhservan
ID: 37009060
I can't use null or zero they are not considered to be an empty cell.

I need the cell to be EMPTYas the column is being used in a count for nonempty cells.

zero or null are considered to be nonempty even though they hold no value.

This is sorta how my count will go:
Count(OriginalPrice) on nonempty cells.
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 11

Assisted Solution

by:dougaug
dougaug earned 800 total points
ID: 37009312
Try to convert it to varchar (or another text type):

                                             [FreeOfCharge] =
                                             CASE PayCode
                                             WHEN 9 THEN cast (OriginalPrice as varchar)
                                             ELSE ''
                                             END
0
 

Author Comment

by:rhservan
ID: 37009534
Dougaug, is that a double quote or 2 singles or does it matter?    
0
 

Author Comment

by:rhservan
ID: 37009552
Actually it won't work with a double quote, so I used to singles.  I altered the stored procedure, ran my update with that stored procedure and same results as before.  I only get zeroes in the cell.
0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 400 total points
ID: 37015333
if the column contains NULL then count(columnname) should ignore it from the count....

can you explain in more detail what you are attempting to do?

(e.g. start off by explaining why you call a "column" a cell....)
0
 
LVL 11

Assisted Solution

by:dougaug
dougaug earned 800 total points
ID: 37015676
What do you mean cell? Are you copying something to a worksheet like Excel?

Answering question 37009534, there are two single quotes.
0
 

Author Comment

by:rhservan
ID: 37019160
The partial Stored Procedure above creates Fact table and inserts data used in SSAS cube.   SSAS can use Empty or nonempty cells as filter, as I currently understand.

The part of the script I am referencing is:
 , [FreeOfCharge] =
   CASE PayCode
   WHEN 9 THEN OriginalPrice
   ELSE 0
   END

I would prefer to manage it right here in this part of the script, since I am having difficulty gaining support for writing an expression for name calcualtions in the cube which will count only the non zero rows.
0
 

Author Comment

by:rhservan
ID: 37019464
Or, I need:

FreeOfCharge > 0 built into an MDX expression
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Integration Management Part 2
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

873 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