Try a function called coaleace It will return the non null value. But i dont have the exact syntax with me nor the manual.
Hope it helps Tommy -----Original Message----- From: Arul [mailto:[EMAIL PROTECTED]] Sent: Wed, 05 Jun 2002 13:26 To: MySQL Subject: Decode to Case Hi All I am currently in the process of converting our database from Oracle to MySql. I Use 3.23.50 Max on Win 2k Well..here's a query where we use decode function in oracle...How do i change it to MYSQL The Query is ---------------------------------------------------------------------------- -- SELECT C.Company_Name, DECODE(U.CompanyID, Null, U.USERID, (SELECT DISTINCT A.UserID FROM Users AWHERE A.User_TypeID = 2))as UserID,U.COMPANYID, U.PASSWORD FROM Users U, Company C WHERE U.CompanyID = C.CompanyID (+) ; ---------------------------------------------------------------------------- --- Well...as per the query , if the company id in the user table is null , we need to select the UserID from the usertable and if the company id has any values , we need to select the distinct of the users in the company table I Thought of using CASE...so it went like this Select C.Company_Name , If(U.CompanyID is null , U,UserID,11) as UserID , U.CompanyID , U.Password >From users left join company on u.companyid = c.company id... This by default assigns userid as 11 when companyid is null....But how do we get the value from the user table instead of 11.... Any ideas for replacing subqueries inside decode function...MySql doesnt support sub queries ...so how do i write queries inside a CASE condition Regards, -Arul --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
