Results 1 to 3 of 3

Thread: Case Statment

  1. #1
    Join Date
    Jan 2005
    Location
    Wexford, Ireland
    Posts
    13

    Unanswered: Case Statment

    Hi Folks,

    I'm trying to create an output file from several joined MySql tables. I want to include a 'virtual' field in the output file whose value is determined by the value of several field in one of the tables.

    I've used the CASE statement before to do something similar however this time I have two problems which I can't resolve...

    1) I need to check that the fields in question are / are not Null. The Case statement, from what I can see, doesn't suport Null returns

    2) I'm not sure what syntax to use when checking several fields to determine the output. i.e. If ChkSo OR AmSo OR MgrSo returns a null value then the output will be 'Incomplete', otherwise the output should read 'Complete'. Everything I've tried sofar, including embedding one case statement within another, doesn't seem to work.

    Does anyone know how to get around these two issues? Maybe CASE is not the appropriate statement to be using.

    Our MySql version is 3.23.58

    I've pasted the Select statement below.

    SELECT E.ErrRef, E.LogDate, E.LoggedBy, E.ErrDate, E.ErrFund, D .FundName, E.ChkSo,E.AmSo,E.MgrSo Case E.ChkSo WHEN Null THEN 'Incomplete ELSE 'complete' END as Signoff
    FROM { oj ERRORS_FA_Errors E LEFT OUTER JOIN
    EMPLOYEES_Registered P ON E.ErrCreator = P.FFNumber } LEFT OUTER JOIN
    EMPLOYEES_Registered AS C ON E.ErrChecker = C.FFNumber LEFT OUTER JOIN
    FUNDREGISTRY_FundList AS D ON E.ErrFund = D .FundID


    Thanks,

    Dave

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select ...
         , case when E.ChkSo is null 
                  or E.AmSo  is null 
                  or E.MgrSo is null 
                then 'Incomplete'
                else 'Complete' 
             end as Signoff
      from ...
    what's up with the curly braces and the "oj" table? i've never seen that before, does it actually run correctly?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2005
    Location
    Wexford, Ireland
    Posts
    13
    Thanks Rudy....seems obvious when you see it but then it generally is.

    As for the brackets.....I'm writing the select statement in an MS DTS package and it automatically inserted the brackets & "oj". Works fine when run through either the DTS Pkg or through MySql Control Center.

    Thanks again,

    Dave

Posting Permissions

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