IF Statement in the SQL Design View

cej2583 · June 24th, 2003, 09:30 PM

I'm converting queries from Access 2000 to SQL views. I need to covert the following expression from Access 2000 to SQL.

Volume: IIf([Divisor]=0,0,[SumOfUnitsMA]/[Divisor])

Volume is a field alias and [Divisor],[SumOfUnitsMA] are fields within the same view/query.

Is there a way to express this in SQL Server Enterprise Manager View Deisgn (in the COLUMN column).

Please email me at [email protected] if you have any questions.

Thanks for any assistance !!!

CEJ

David Cameron · June 24th, 2003, 09:39 PM

Replace your IFF statement with:

Code:

CASE Divisor
    WHEN 0 THEN 0
    ELSE SumOfUnitsMA/Divisor
END

regards
David Cameron

cej2583 · June 24th, 2003, 09:52 PM

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

David Cameron · June 24th, 2003, 09:58 PM

Is this what you want?

Code:

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?],   
    CASE Divisor
        WHEN 0 THEN 0
        ELSE SumOfUnitsMA/Divisor
    END 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)

regards
David Cameron

cej2583 · June 24th, 2003, 10:23 PM

An Error Occurs and says that CASE is not supported

Thanks!!!

Eric

Quote:

quote:Originally posted by David Cameron
Is this what you want?

Code:

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?],   
    CASE Divisor
        WHEN 0 THEN 0
        ELSE SumOfUnitsMA/Divisor
    END 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)

regards
David Cameron

CEJ

cej2583 · June 24th, 2003, 10:25 PM

David:

For reasons unknown the Error has disappeared and the view works!!!

Thank you very very much!!

Quote:

quote:Originally posted by David Cameron
Is this what you want?

Code:

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?],   
    CASE Divisor
        WHEN 0 THEN 0
        ELSE SumOfUnitsMA/Divisor
    END 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)

regards
David Cameron

CEJ

peeches · July 17th, 2006, 05:32 AM

Hi. I seem to be having a similar problem. I want to select columns based on the value of another column.

This is what I'm looking for:

if final = 0 then
select rate as rate_sug, depot as depot_sug from callinglistdetails
else
select rate as rate_fin, depot as depot_fin from callinglistdetails