Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 24th, 2003, 09:30 PM
Authorized User
 
Join Date: Jun 2003
Location: Brentwood, TN, USA.
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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
__________________
CEJ
Reply With Quote
  #2 (permalink)  
Old June 24th, 2003, 09:39 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Sydney, NSW, Australia.
Posts: 215
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Replace your IFF statement with:
Code:
CASE Divisor
    WHEN 0 THEN 0
    ELSE SumOfUnitsMA/Divisor
END
regards
David Cameron
Reply With Quote
  #3 (permalink)  
Old June 24th, 2003, 09:52 PM
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
  #4 (permalink)  
Old June 24th, 2003, 09:58 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Sydney, NSW, Australia.
Posts: 215
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #5 (permalink)  
Old June 24th, 2003, 10:23 PM
Authorized User
 
Join Date: Jun 2003
Location: Brentwood, TN, USA.
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #6 (permalink)  
Old June 24th, 2003, 10:25 PM
Authorized User
 
Join Date: Jun 2003
Location: Brentwood, TN, USA.
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #7 (permalink)  
Old July 17th, 2006, 05:32 AM
Registered User
 
Join Date: Jul 2006
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
cannot view the design of the .aspx page sansircar ASP.NET 1.0 and 1.1 Professional 1 October 24th, 2006 01:31 PM
Report change in Design View echovue Access VBA 4 March 3rd, 2006 04:31 PM
Blank Design View ??? smuger Dreamweaver (all versions) 4 October 11th, 2004 04:40 PM
how to enable the design view yylee Access VBA 2 October 4th, 2004 02:23 PM
Could not open in design view!! texasraven ASP.NET 1.x and 2.0 Application Design 4 March 17th, 2004 03:22 PM



All times are GMT -4. The time now is 09:46 PM.


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