Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2001
    Location
    Akron
    Posts
    11

    Unanswered: SQL Conditional Statements

    I'm converting a MS Access query over to a SQL2K view.

    How can I convert the following query statement?

    IIF([curCost]==0, 0, curContractPrice/curCost) ...

    Simple, but I'm not sure how to do this in SQL2K.

    I Know that I'm able to do the following

    sngMarkUp =
    CASE
    WHEN curCost = 0 THEN 0
    ELSE curContractPrice/curCost
    END

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    if value is true
    do this
    else
    do this

    Or if more than 1 statement following if/else use begin/end:

    if value is true
    begin
    do this
    and this
    end
    else
    begin
    do this
    and this
    end

    The case statement is normally used when you have more than 2 conditions.

  3. #3
    Join Date
    Sep 2001
    Location
    Akron
    Posts
    11

    TRUE BUT...

    This works with maybe a TQL statement under say Query Analyzer.
    I looking to produce a conditional IF statement in a View.

    For example the MS Access Statement:

    SELECT curContractPrice, curCost, IIF(curCost == 0, 0, curContractPrice/curCost)
    FROM tblProducts

    The IIF() Function is not used is SQL2K. What should be done?
    I'm trying to prevent a divided-by-zero error. Is there a function that allows me to do this?

    I think this problem is very simple. I just do not know how to do it.

    Originally posted by rnealejr
    if value is true
    do this
    else
    do this

    Or if more than 1 statement following if/else use begin/end:

    if value is true
    begin
    do this
    and this
    end
    else
    begin
    do this
    and this
    end

    The case statement is normally used when you have more than 2 conditions.

  4. #4
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    Your right on the CASE statement, it is your only straight translation of IIF. Your other option is to create a User Defined Function, since you are using SQL2000. However the effort to do this doesn't seem to make any sense since it would not reduce coding and add another layer as well as addition object to maintain.

  5. #5
    Join Date
    Mar 2002
    Location
    India
    Posts
    3
    Use CASE - WHEN function.

  6. #6
    Join Date
    Sep 2001
    Location
    Akron
    Posts
    11

    Well

    Does this work in a View? I tried. I would like to calculate this value in
    a view. I know this will work in the Query Analyzer.

    Originally posted by sanjoy1
    Use CASE - WHEN function.

  7. #7
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    Sure

    CREATE VIEW myView
    AS
    SELECT CASE WHEN x=1 THEN 'No' ELSE 'Yes' END AS [Answer]

    Blah, Blah, Blah

    SELECT *
    FROM myView

  8. #8
    Join Date
    Sep 2001
    Location
    Akron
    Posts
    11

    More Information

    I was able to find this on the MS knowledge Base
    Data Designer Does Not Support CASE Statements in Views :

    http://support.microsoft.com/search/...w.aspx?scid=kb;en-us;Q194014


    Originally posted by achorozy
    Sure

    CREATE VIEW myView
    AS
    SELECT CASE WHEN x=1 THEN 'No' ELSE 'Yes' END AS [Answer]

    Blah, Blah, Blah

    SELECT *
    FROM myView

  9. #9
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    Looks like there are limitations with Data Designer, as far as is it valid to use CASE statement in a VIEW, you can.

  10. #10
    Join Date
    Oct 2002
    Location
    Western part of Georgia, USA
    Posts
    123

    Unhappy Conditional SQL

    Thanks in advance for any help given!!

    I am new to db'ing and in need of some direction.

    I need to query a table that has info that is collected every 2 minutes over a 2 hour period (data goes in as datetime). The problem is that the data needs to be assigned to a shift (a, b, c or d). A and b shift begins and ends at 7:00Am and 7:00PM respectively. C and d begins and ends at 7:00Pm and 7:00AM. A and b are easy at the data is entered in a single date. The problem I have is that C and D span across two days (7pm to 7am) and I need to sum the data across two days on an hourly basis. My question is: is it possible to write a conditional sql statement that would check for shift and then based on that shift do something like this?
    if shift is C or D and time is between 7PM and 12AM
    select sum(ColTime)
    from mytable
    Where ColDate = Getdate()
    group by convert(ColDate to Hour)
    if shift is c or d and time is between 12AM and 7AM
    select sum(ColTime)
    Where ColDate = Get()
    group by convert(ColDate to Hour)

  11. #11
    Join Date
    Feb 2002
    Posts
    2,232
    How is the other query defined - from 7am to 7pm ? Why can't you just do the same as the 7am-7pm but just make your where clause include both the 12am-7am and 7pm-12am ?

  12. #12
    Join Date
    Oct 2002
    Location
    Western part of Georgia, USA
    Posts
    123
    The table is populated by a PLC. It looks something like this:
    ColDateTime: ColShift: ColLine: ColRunTime: ColDnTime:
    8/23/2002 7:00PM C 1 1.5 .5
    8/23/2002 7:02PM C 1 2.0 0.0
    8/23/2002 7:04PM C 1 2.0 0.0
    ... .. .. ... ...
    8/23/2002 12:02AM C 1 2.0 0.0


    At the end of each hour I need to do a summary of the data. I don't know how to do 3 things: 1. Group by Hour. 2. Conditional SQL. 3. Group by on shift where the shift spans 2 days.

    Again thanks for any help received.
    Lee

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •