p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   SQL Server 2000 (http://p2p.wrox.com/forumdisplay.php?f=20)
-   -   IF Statement in the SQL Design View (http://p2p.wrox.com/showthread.php?t=927)

cej2583 June 24th, 2003 09:30 PM

IF Statement in the SQL Design View
 
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 CEricJones16@msn.com 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



All times are GMT -4. The time now is 12:14 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.