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

what's up with the curly braces and the "oj" table? i've never seen that before, does it actually run correctly?

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