ok I have 4 columns returned in my query which can either have a value of 'FF' or null.

I want to create a fifth column with a calculation which basically states

none are populated
1 is populated
2 are populated
3 are populated
4 are populated

I was thinking something like case mixed with count, but not sure how to set it up.

and another decode for "is" or "are"....

I thought I'd leave something for the OP to do

Many, many ways of doing it...

SQL> ed
Wrote file afiedt.buf
 
  1  with t as (select 'FF' as col1, null as col2, null as col3, null as col4 from dual union all
  2             select 'FF', 'FF', 'FF', null from dual union all
  3             select 'FF', 'FF', null, 'FF' from dual union all
  4             select 'FF', null, 'FF', null from dual)
  5  -- END OF TEST DATA
  6  select col1, col2, col3, col4, length(col1||col2||col3||col4)/2 as populated
  7* from t
SQL> / 
 
CO CO CO CO  POPULATED
-- -- -- -- ----------
FF                   1
FF FF FF             3
FF FF    FF          3
FF    FF             2
 
SQL>

the simple decode returns only zeros

We can also use the fact, that FF is one valid value for column

SQL> with t as (select 'FF' as col1, null as col2, null as col3, null as col4 from dual union all
2 select 'FF', 'FF', 'FF', null from dual union all
3 select 'FF', 'FF', null, 'FF' from dual union all
4 select 'FF', null, 'FF', null from dual)
5 select length(col1||col2||col3||col4) / 2
6 from t
7 /

LENGTH(COL1||COL2||COL3||COL4)

---

1
3
3
2

SQL>

you are correct....when I strip out the extras it works fine.

the problem is that I need some additional columns returned and the four columns I previously mentioned were decoded from other results.

query is as such now(without the calculation requested):

select
vdh.vin as VIN,
mcs.vehicle.veh_manuf_year@OCP5 as YEAR,
mcs.veh_make.veh_make_desc@OCP5 as MAKE,
vcbc.veh_model.veh_model_desc@OCP5 as MODEL,
vdh.created_timestamp as "TIMESTAMP",
max(to_char(decode(c.COMMAND_ID,'LT_FT_TR_PR',TRIM (TO_CHAR
(cvp.command_result, 'XXXXXXXXXXXXXXX')), ' '))) LF_TIRE_PRESSURE,
max(to_char(decode(c.COMMAND_ID,'RT_FT_TR_PR',TRIM (TO_CHAR
(cvp.command_result, 'XXXXXXXXXXXXXXX')), ' '))) RF_TIRE_PRESSURE,
max(to_char(decode(c.COMMAND_ID,'LT_RR_TR_PR',TRIM (TO_CHAR
(cvp.command_result, 'XXXXXXXXXXXXXXX')), ' '))) LR_TIRE_PRESSURE,
max(to_char(decode(c.COMMAND_ID,'RT_RR_TR_PR',TRIM (TO_CHAR
(cvp.command_result, 'XXXXXXXXXXXXXXX')), ' '))) RR_TIRE_PRESSURE,
max(to_char(decode(c.COMMAND_ID,'HIGH__PRESSURE_LIMIT',TRIM (TO_CHAR
(cvp.command_result, 'XXXXXXXXXXXXXXX')), ' '))) HI_LIMIT,
max(to_char(decode(c.COMMAND_ID,'LOW__PRESSURE_LIMIT',TRIM (TO_CHAR
(cvp.command_result, 'XXXXXXXXXXXXXXX')), ' '))) LO_LIMIT,
c.STATUS as STATUS,
dh.DTC_CODE as DTC_CODE,
dh.EXPLANATION_TEXT as DTC_EXPLANATION,

with t as (select 'FF' as LF_TIRE_PRESSURE, null as RF_TIRE_PRESSURE,
null as LR_TIRE_PRESSURE, null as RR_TIRE_PRESSURE from dual union all
select 'FF', 'FF', 'FF', null from dual union all
select 'FF', 'FF', null, 'FF' from dual union all
select 'FF', null, 'FF', null from dual) select t.*,
decode(LF_TIRE_PRESSURE,'FF',1,0) + decode(RF_TIRE_PRESSURE,'FF',1,0) +
decode(LR_TIRE_PRESSURE,'FF',1,0)+decode(RR_TIRE_PRESSURE,'FF',1,0) cnt
from t

from vdu.c2pt_data_history c, veh_diag_history vdh,
vdu.connected_veh_param cvp,
module_history mh, dtc_history dh,
mcs.vehicle@OCP5, mcs.veh_make@OCP5, vcbc.veh_model@OCP5,
vcbc.veh_manufacturer@OCP5

where ((c.VEH_DIAG_HISTORY_SAK = vdh.VEH_DIAG_HISTORY_SAK)
and (vdh.CASE_SAK = cvp.CASE_SAK)
and (dh.MODULE_HISTORY_SAK = mh.MODULE_HISTORY_SAK)
and (vdh.VEH_DIAG_HISTORY_SAK = mh.MODULE_HISTORY_SAK)
and (cvp.VEHICLE_SAK = vehicle.vehicle_sak@OCP5)
and (veh_make.veh_manuf@OCP5 = veh_model.veh_manuf@OCP5)
AND (veh_make.veh_make@OCP5 = veh_model.veh_make@OCP5)
AND (veh_manufacturer.veh_manuf@OCP5 = veh_model.veh_manuf@OCP5)
AND (veh_model.veh_manuf@OCP5 = vehicle.veh_manuf@OCP5)
AND (veh_model.veh_make@OCP5 = vehicle.veh_make@OCP5)
AND (veh_model.veh_model@OCP5 = vehicle.veh_model@OCP5)
AND (veh_model.veh_manuf_year@OCP5 = vehicle.veh_manuf_year@OCP5))

and c.CREATED_TIMESTAMP >= to_date ('10/20/2007 12:00:00 AM','MM/DD/YYYY
HH:MI:SS AM')
and c.CREATED_TIMESTAMP < to_date ('10/21/2007 12:00:00 AM','MM/DD/YYYY
HH:MI:SS AM')
group by vdh.vin, mcs.vehicle.veh_manuf_year@OCP5,
mcs.veh_make.veh_make_desc@OCP5,
vcbc.veh_model.veh_model_desc@OCP5, vdh.created_timestamp, c.STATUS,
dh.DTC_CODE,
dh.EXPLANATION_TEXT
order by vdh.created_timestamp

If you're not looking for easy solutions you can use collections here:

SQL> select col1, col2, col3, col4,
  2         cardinality(cast(multiset(select decode(level, 1, col1, 2, col2, 3, col3, 4, col4) from dual
  3              connect by level <= 4) as test)
  4         multiset except all
  5         cast(multiset(select null from dual connect by level<=4) as test)) coll from t
  6  / 
 
COL1       COL2       COL3       COL4             COLL
---------- ---------- ---------- ---------- ----------
FF         NULL       NULL       NULL                1
FF         FF         FF         NULL                3
FF         FF         NULL       FF                  3
FF         NULL       FF         NULL                2

detailed description in my blog:
http://volder-notes.blogspot.com/2007/10/using-collections-with-10g-features.html