Re: [phpBT-dev] Could someone help fix the Oracle-SQL stuff?

[Jirka P. <fi...@us...>]

> First of all, I think there is a comma too much after the second 'sum'.
> If I fix that, it gives me the following:
>
> DB Error: no such field
> SELECT sum(decode( s.status_id, BUG_UNCONFIRMED, 1, BUG_PROMOTED, 1,
> BUG_ASSIGNED, 1, BUG_REOPENED, 1, 0 )), sum(decode( s.status_id,
> BUG_UNCONFIRMED, 0, BUG_PROMOTED, 0, BUG_ASSIGNED, 0, BUG_REOPENED, 0, =
1 ))
> from phpbt_bug b, phpbt_status s where b.status_id =3D s.status_id (+) =
and
> b.assigned_to =3D 1 [nativecode=3DORA-00904: invalid column name ]
>
> If I pretty print that in SQL*Plus Worksheet, I get:
> SELECT
> sum(decode(s.status_id,
>   BUG_UNCONFIRMED, 1,
>   BUG_PROMOTED,    1,
>   BUG_ASSIGNED,    1,
>   BUG_REOPENED,    1,
>   0 )),
> sum(decode(s.status_id,
>   BUG_UNCONFIRMED, 0,
>   BUG_PROMOTED,    0,
>   BUG_ASSIGNED,    0,
>   BUG_REOPENED,    0,
>   1 ))
> from phpbt_bug b, phpbt_status s where b.status_id =3D s.status_id (+) =
and
> b.assigned_to =3D 1;
>
> Where the error is:
>   BUG_REOPENED,    0,
>   *
> FEHLER in Zeile 12:

This means error on line 12? I hope so.

> ORA-00904: Ung=FCltiger Spaltenname (invalid column name)

ORACLE doesn't know BUG_REOPENED column.

> Any hints on how to fix it?

What DECODE does is that it takes at least three parameters: DECODE (hays=
tack,=20
needle_1, value_1, ... , needle_n, value_n, value_when_null),
where needle_X is value, which must be found in the haystack column and w=
hen=20
it is, DECODE will replace this value with value_X. Other parameters are =
self=20
explanatory.

phpbt_status.status_id is a number, so I actually don't know why there is=
 a=20
DECODE function used, replacing constants with numbers.

I hope you will be able to repair this, because I have no time to look on=
 it.

Have a nice day!
Jirka Pech