Thread: no IF - am I missing something ?
Hello all. In the tail end of converting an app from MySQL to psql. I have this code : <snip> IF(days_complete <= -120, job_price,0) AS Days_120, IF(days_complete BETWEEN -119 AND -90, job_price,0) AS Days_90, IF(days_complete BETWEEN -89 AND -60, job_price,0) AS Days_60, IF(days_complete BETWEEN -59 AND -30, job_price,0) AS Days_30, IF(days_complete BETWEEN -29 AND 0, job_price,0) AS current <snip> It builds an aged debt report, and there are similar versions that SUM(IF ..) to give me debt by customer etc. All the questions I've seen about IF end up with people saying "use CASE" and I'm sure it would work. But do you lot reallyuse 5 lines for each IF ? Doesn't it seem kind of ugly ? Or do you all secretly write a quick IF function ? I'm interested, and I half suspect that I'm missing a more elegant solution. thanks Rich
The number of lines depends merely on where you place your line breaks. IF(days_complete <= 120, job_price, 0)AS Days_120 could be written as: CASE WHEN days_complete <= 120 THEN job_price ELSE 0 END AS Days_120 There might be somewhat less syntactic sugar, but this is not a five line expression and, to me, is more readable than a comma-delimited list where position alone indicates function in the expression. -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Mar 17, 2005, at 1:57 PM, Richard Crawley wrote: > > Hello all. > > In the tail end of converting an app from MySQL to psql. I have this > code : > > > <snip> > IF(days_complete <= -120, job_price,0) AS Days_120, > IF(days_complete BETWEEN -119 AND -90, job_price,0) AS Days_90, > IF(days_complete BETWEEN -89 AND -60, job_price,0) AS Days_60, > IF(days_complete BETWEEN -59 AND -30, job_price,0) AS Days_30, > IF(days_complete BETWEEN -29 AND 0, job_price,0) AS current > <snip> > > > It builds an aged debt report, and there are similar versions that > SUM(IF ..) to give me debt by customer etc. > > All the questions I've seen about IF end up with people saying "use > CASE" and I'm sure it would work. But do you lot really use 5 lines > for each IF ? Doesn't it seem kind of ugly ? Or do you all secretly > write a quick IF function ? > > I'm interested, and I half suspect that I'm missing a more elegant > solution. > > > > thanks > > > Rich > > > ---------------------------(end of > broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings
On Mon, Mar 21, 2005 at 12:35:22AM -0600, Thomas F.O'Connell wrote: > The number of lines depends merely on where you place your line breaks. > > IF(days_complete <= 120, job_price, 0)AS Days_120 > > could be written as: > > CASE WHEN days_complete <= 120 THEN job_price ELSE 0 END AS Days_120 > > There might be somewhat less syntactic sugar, but this is not a five > line expression and, to me, is more readable than a comma-delimited > list where position alone indicates function in the expression. CASE is also standard SQL, whereas IF isn't (unless I've overlooked it in the SQL:2003 draft). -- Michael Fuhr http://www.fuhr.org/~mfuhr/
In article <20050321075049.GA18302@winnie.fuhr.org>, Michael Fuhr <mike@fuhr.org> writes: > On Mon, Mar 21, 2005 at 12:35:22AM -0600, Thomas F.O'Connell wrote: >> The number of lines depends merely on where you place your line breaks. >> >> IF(days_complete <= 120, job_price, 0)AS Days_120 >> >> could be written as: >> >> CASE WHEN days_complete <= 120 THEN job_price ELSE 0 END AS Days_120 >> >> There might be somewhat less syntactic sugar, but this is not a five >> line expression and, to me, is more readable than a comma-delimited >> list where position alone indicates function in the expression. > CASE is also standard SQL, whereas IF isn't (unless I've overlooked > it in the SQL:2003 draft). CASE, despite being standard SQL, is even supported by MySQL ;-)