An if statement within an sql statement

[tjbarr]

if ($lfpr ==1 and $j < ($count_yr-1))
{
$strSQL=$strSQL."(".$table2.".".$pre2.$yr_array [$j]."/".$table.".".$pre.$yr_array[$j]. ") as " .$newpre.$yr_array[$j].",";
}

If the demominator of this equation is != 0 then I want to execute the above sql, if it is = 0 then I want the equation to =0.

I keep getting postgres errors.

[tjbarr]

I need to put the if into the sql

[Sxooter]

The easiest way is probably a union.

select i1/i2 from table where i2<>0
union
select '0' from table where i2=0;

[Sxooter]

A couple of points:

You'll need to apply that technique to your query yourself, I couldn't tell quite what your query would look like with just your php/where clause partial, so I just posted a simple example.

You might need a UNION ALL if you need all rows and have duplicates, since UNION drops duplicates by default.

Next time you might wanna post your schema and the generated SQL query that is causing the problem, it's easier to work with.

No biggie on those, just FYI.

[chriskl]

The IF statement is crappy non-standard MySQL syntax. For PostgreSQL, you need to use the CASE statement:

eg:

CASE WHEN cond1 THEN
value1
ELSE WHEN cond2 THEN
value2
ELSE
value3

[tjbarr]

Thanks for the Case info. This SQL worked

if ((($gcp ==1) and $j==($count_yr-1)) or(($gcp_1dg == 1) and $j == ($count_yr-1)) or (($gcp_2dg == 1) and $j == ($count_yr-1)))
{
$strSQL=$strSQL."((CASE WHEN " .$table3.".".$pre.$yr_array[$j]."=0 THEN 0 ELSE (".$table.".".$pre.$yr_array[$j]."/".$table3.".".$pre.$yr_array[$j].")END)* "
.$table2.".".$pre2.$yr_array[$j].") as " .$newpre.$yr_array[$j];
}