Hi Tommy My Requirement is not to select a non null value ...I need to select the companyid and if the companyid is null i need to select the userid from user table and if the companyid is not null , there may be many users for the company and i need to select the user i need (where user_type = 2)
-Arul ----- Original Message ----- From: "Tommy Claasens - Q Data KZN" <[EMAIL PROTECTED]> To: "Arul" <[EMAIL PROTECTED]>; "MySQL" <[EMAIL PROTECTED]> Sent: Wednesday, June 05, 2002 5:54 PM Subject: RE: Decode to Case > 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 > --------------------------------------------------------------------- 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
