Re: How to use Logic in View Statment?
As an update to my earlier question, is it possible to have logic in select statements including the join? ie, select IF(CHAR_LENGTH(broker)0,broker,vendor) as company from table 1 IF(CHAR_LENGTH(broker)0,INNER JOIN tblBroker,INNER JOIN tblVendor) Thanks. On 11/28/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Scott Klarenbach [EMAIL PROTECTED] wrote on 11/28/2005 01:58:22 PM: I'd like to do the following in my view select fieldOne, fieldTwo, if(fieldThree.length0) then fieldThree as Company else fieldFour as Company from table; I realize this syntax isn't correct and length doesn't exists, but is this possible? I've seen it done in SQLServer, but can't remember the syntax. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] LENGTH does exist (as a function): http://dev.mysql.com/doc/refman/5.0/en/string-functions.html and here is how to use the IF() function: http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html I believe this is the equivalent statement you wanted: SELECT fieldOne, fieldTwo, IF(CHAR_LENGTH(fieldThree)0,fieldThree,fieldFour) as Company FROM table; Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to use Logic in View Statment?
Yes, but not quite as you imagined doing it. SELECT tbl1.*, coalesce(broker, vendor) as company from table1 tbl1 LEFT JOIN tblBroker b on tbl1.broker_id = b.id LEFT JOIN tblVendor v ON tbl1.vendor_id = v.id; The left joins indicates that the tables on right side of the join contain optional information. For rows that do not match the ON conditions of each JOIN, every column in the unmatched table will have a NULL value in it. The COALESCE() function returns the first non-null value from the list of values. Give us more details about what you are trying to do and I am certain you can get a more precise answer. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Scott Klarenbach [EMAIL PROTECTED] wrote on 12/05/2005 02:43:58 PM: As an update to my earlier question, is it possible to have logic in select statements including the join? ie, select IF(CHAR_LENGTH(broker)0,broker,vendor) as company from table 1 IF(CHAR_LENGTH(broker)0,INNER JOIN tblBroker,INNER JOIN tblVendor) Thanks. On 11/28/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Scott Klarenbach [EMAIL PROTECTED] wrote on 11/28/2005 01:58:22 PM: I'd like to do the following in my view select fieldOne, fieldTwo, if(fieldThree.length0) then fieldThree as Company else fieldFour as Company from table; I realize this syntax isn't correct and length doesn't exists, but is this possible? I've seen it done in SQLServer, but can't remember the syntax. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] LENGTH does exist (as a function): http://dev.mysql.com/doc/refman/5.0/en/string-functions.html and here is how to use the IF() function: http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html I believe this is the equivalent statement you wanted: SELECT fieldOne, fieldTwo, IF(CHAR_LENGTH(fieldThree)0,fieldThree,fieldFour) as Company FROM table; Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to use Logic in View Statment?
I'd like to do the following in my view select fieldOne, fieldTwo, if(fieldThree.length0) then fieldThree as Company else fieldFour as Company from table; I realize this syntax isn't correct and length doesn't exists, but is this possible? I've seen it done in SQLServer, but can't remember the syntax. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to use Logic in View Statment?
Scott Klarenbach [EMAIL PROTECTED] wrote on 11/28/2005 01:58:22 PM: I'd like to do the following in my view select fieldOne, fieldTwo, if(fieldThree.length0) then fieldThree as Company else fieldFour as Company from table; I realize this syntax isn't correct and length doesn't exists, but is this possible? I've seen it done in SQLServer, but can't remember the syntax. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] LENGTH does exist (as a function): http://dev.mysql.com/doc/refman/5.0/en/string-functions.html and here is how to use the IF() function: http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html I believe this is the equivalent statement you wanted: SELECT fieldOne, fieldTwo, IF(CHAR_LENGTH(fieldThree)0,fieldThree,fieldFour) as Company FROM table; Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: How to use Logic in View Statment?
Scott select fieldOne, fieldTwo, if(fieldThree.length0) then fieldThree as Company else fieldFour as Company from table; SELECT fieldOne, fieldTwo, IF( LENGTH( fieldthree ) 0, fieldthree, fieldFour ) AS Company FROM table; PB - Scott Klarenbach wrote: I'd like to do the following in my view select fieldOne, fieldTwo, if(fieldThree.length0) then fieldThree as Company else fieldFour as Company from table; I realize this syntax isn't correct and length doesn't exists, but is this possible? I've seen it done in SQLServer, but can't remember the syntax. -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.8/184 - Release Date: 11/27/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to use Logic in View Statment?
I have a similar question. How do you test if the field in question isn't NULL or '' (empty string)? Is the LENGTH a good test or is there a better way? [EMAIL PROTECTED] wrote: LENGTH does exist (as a function): http://dev.mysql.com/doc/refman/5.0/en/string-functions.html and here is how to use the IF() function: http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html I believe this is the equivalent statement you wanted: SELECT fieldOne, fieldTwo, IF(CHAR_LENGTH(fieldThree)0,fieldThree,fieldFour) as Company FROM table; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to use Logic in View Statment?
I have a similar question. How do you test if the field in question isn't NULL or '' (empty string)? Is the LENGTH a good test or is there a better way? WHERE myfield IS NOT NULL -- Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to use Logic in View Statment?
Cory @ SkyVantage [EMAIL PROTECTED] wrote on 11/28/2005 02:38:50 PM: I have a similar question. How do you test if the field in question isn't NULL or '' (empty string)? Is the LENGTH a good test or is there a better way? [EMAIL PROTECTED] wrote: LENGTH does exist (as a function): http://dev.mysql.com/doc/refman/5.0/en/string-functions.html and here is how to use the IF() function: http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html I believe this is the equivalent statement you wanted: SELECT fieldOne, fieldTwo, IF(CHAR_LENGTH(fieldThree)0,fieldThree,fieldFour) as Company FROM table; There are MANY ways to work with or detect NULL values: ISNULL() COALESCE() IS NULL IS NOT NULL NOT .. IS NULL ='' = NULL IFNULL() NULLIF() http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html What exactly are you trying to accomplish with the test? There may be several ways of getting at what you want. Shawn Green Database Administrator Unimin Corporation - Spruce Pine The ISNULL function returns either a 0 or 1 depening on if the expression inside the parentheses is NULL or not
RE: How to use Logic in View Statment?
Hi , IMHO the corect way is to check if IS NULL SELECT fieldOne, fieldTwo, IF(fieldThree IS NULL,fieldFour,fieldThree) as Company FROM table; Depening on the column definition(if is byte,multi-byte...etc.), LENGTH() function can have various behaviour and is not quite sure that you will get always the same result. Regards _ G.NET SOFTWARE COMPANY SYSTEM INTEGRATOR - AUTOMATION SOFTWARE DEVELOPER http://www.gonetsoftware.com Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, 28 November, 2005 10:09 PM To: Cory @ SkyVantage Cc: My SQL Subject: Re: How to use Logic in View Statment? Cory @ SkyVantage [EMAIL PROTECTED] wrote on 11/28/2005 02:38:50 PM: I have a similar question. How do you test if the field in question isn't NULL or '' (empty string)? Is the LENGTH a good test or is there a better way? [EMAIL PROTECTED] wrote: LENGTH does exist (as a function): http://dev.mysql.com/doc/refman/5.0/en/string-functions.html and here is how to use the IF() function: http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html I believe this is the equivalent statement you wanted: SELECT fieldOne, fieldTwo, IF(CHAR_LENGTH(fieldThree)0,fieldThree,fieldFour) as Company FROM table; There are MANY ways to work with or detect NULL values: ISNULL() COALESCE() IS NULL IS NOT NULL NOT .. IS NULL ='' = NULL IFNULL() NULLIF() http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html What exactly are you trying to accomplish with the test? There may be several ways of getting at what you want. Shawn Green Database Administrator Unimin Corporation - Spruce Pine The ISNULL function returns either a 0 or 1 depening on if the expression inside the parentheses is NULL or not -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
