asked on

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

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'

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

tigin44

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rhservan

ASKER

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.

SOLUTION

dougaug

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rhservan

ASKER

Dougaug, is that a double quote or 2 singles or does it matter?

rhservan

ASKER

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.

SOLUTION

Lowfatspread

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

dougaug

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rhservan

ASKER

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.

rhservan

ASKER

Or, I need:

FreeOfCharge > 0 built into an MDX expression