My table in Oracle is like this
Col Col1 Col2 Col3 ColR
-- ---- ---- ---- ----
30 73 40 null -10
60 32 null 20 40
90 80 null null 10
80 45 81 30 50
I can also set 0 instead of null in the above column. I need to find the min value from Col1,Col2,Col3 ignoring null or 0 and populate the ColR by subtracting from Col.
EDIT: i wrote a CASE statement which doesn't work due to the null values inside my table.
SELECT col,col1,col2,col3,
CASE
WHEN Col1 < Col2 AND Col1 < Col3
THEN Col - Col1
WHEN Col2 < Col1 AND Col2 < Col3
THEN Col - Col2
ELSE Col - Col3
END ColR
FROM
(SELECT col,col1,
CASE
WHEN col22 IS NULL
THEN NULL( i can also SET TO 0 but it will mess WITH my other calculation TO find MIN)
ELSE ROUND( 100* ( (col22) / (col44)))
END col2 ,
CASE
WHEN col33 IS NULL
THEN NULL
ELSE ROUND( 100* ( (col33) / (col44)))
END col3
FROM TABLE
)
I have just included the case statement inside my select query. all the the column values all populated from another query.
If you want to ignore nulls in a column, you could wrap them with the NVL function. This replaces null values in a column with the value specified, for which you could use some large number. For example:
NVL(col1,99999)
Oracle Database SQL Reference - NVL: http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions105.htm
// IF YOU NEED MINIMAL FROM COL1 or COL (ANY COLUMN)
SELECT MIN (COL1) FROM (SELECT * FROM TABLE WHERE COL1 IS NOT NULL)
Can you please elaborate I am not able to help you with this small set of info actually.
Oracle NVL Usage:
So nvl(col2,0) ought to take of nulls that mess with your math.
So try:
EDIT: Taking X-Zero's point which I missed. I think if you replace the NULLS with 9999999 instead of 0, the logic will work, although that may be too specific to this sample data and not a real world solution.
It sounds like you want something like
where 999999999 is some numeric value that is large enough that it will always be larger than any other valid value. If it is possible that all three columns will have
NULL
or 0 values, then you'd probably want to add an additional check that if the result of thatleast
function is 999999999 that you return 0 orNULL
or whatever else makes sense.@X-Zero was kind enough to put together a working SQL Fiddle example of this construct. Note that his example is filtering out the rows where all three columns have either
NULL
or 0 values.