View Single Post
  #3 (permalink)  
Old June 24th, 2003, 09:52 PM
cej2583 cej2583 is offline
Authorized User
 
Join Date: Jun 2003
Location: Brentwood, TN, USA.
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default

DAVID:

First thank you for your quick response!

Second...you will have to forgive my amatuer questions. I'm am only a
mortal among the GODS of programming.

Your answer makes sense, but is there a way to put that into the view designer. My statement currently looks like this:

SELECT 'MA01' AS Entity, dbo.LabVols_MA00.Code AS Simmcode,
    '01.4549' AS RevCenter,
    dbo.LabVols_MA00.SumOfUnitsMA AS Units,
    dbo.LabVols_MA00.SumOfChargeMA AS Charges,
    dbo.LabVols_MA00.Date,
    dbo.Bridge_CDM_Activity_MA01.[Incl?],
    dbo.Bridge_CDM_Activity_MA01.Divisor,
    dbo.Bridge_CDM_Activity_MA01.[Static?], 0 AS Volume
FROM dbo.LabVols_MA00 LEFT OUTER JOIN
    dbo.LabVols_HFees ON
    dbo.LabVols_MA00.Code = dbo.LabVols_HFees.simmcode LEFT
     OUTER JOIN
    dbo.Bridge_CDM_Activity_MA01 ON
    dbo.LabVols_MA00.Code = dbo.Bridge_CDM_Activity_MA01.simmcode
WHERE (dbo.LabVols_HFees.simmcode IS NULL) AND
    (dbo.Bridge_CDM_Activity_MA01.simmcode IS NOT NULL) AND
    (dbo.Bridge_CDM_Activity_MA01.[Incl?] = - 1)

In the design view:

Column Alias Table Output Criteria
'MA01' Entity Y
Code Simmcode LabVols_MA00 Y
'01.4549' RevCenter Y
SumOfUnitsMA Units LabVols_MA00 Y
SumOfChargeMA Charges LabVols_MA00 Y
Date LabVols_MA00 Y
[Incl?] Bridge_CDM_Activity_MA01 Y = - 1
Divisor Bridge_CDM_Activity_MA01 Y
[Static?] Bridge_CDM_Activity_MA01 Y
???????????? Volume ??????????????????? Y
simmcode LabVols_HFees IS NULL
simmcode Bridge_CDM_Activity_MA01 IS NOT NULL

I want the Volume (alias) Column to change based on the value in the Divisor field

Again Thanks!






Quote:
quote:Originally posted by David Cameron
 Replace your IFF statement with:
Code:
CASE Divisor
    WHEN 0 THEN 0
    ELSE SumOfUnitsMA/Divisor
END
regards
David Cameron
CEJ
Reply With Quote