CODEWITHSUNDEEP
sqloracle-database
jero
jero

Reputation: 543

SQL Selecting MIN value from row data with null values

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.

Upvotes: 0

Views: 15377

Answers (4)

Justin Cave
Justin Cave

Reputation: 231661

It sounds like you want something like

SELECT least( (case when col1 is null or col1 = 0 then 999999999 else col1 end),
              (case when col2 is null or col2 = 0 then 999999999 else col2 end),
              (case when col3 is null or col3 = 0 then 999999999 else col3 end) )
  FROM <<table name>>

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 that least function is 999999999 that you return 0 or NULL 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.

Upvotes: 2

Cody
Cody

Reputation: 2482

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

Upvotes: 0

mikeY
mikeY

Reputation: 519

Oracle NVL Usage:

nvl(check_expression, replacement_value)

So nvl(col2,0) ought to take of nulls that mess with your math.

So try:

CASE
       WHEN nvl(col1,0) < nvl(col2,0) AND nvl(col1,0) < nvl(col3,0)
       THEN Col - nvl(col1,0)
       WHEN  nvl(col2,0) < nvl(col1,0) AND nvl(col2,0) < nvl(col3,0)
       THEN Col - nvl(col2,0)
       ELSE Col - nvl(col3,0)
END ColR

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.

Upvotes: 1

Dhruvenkumar Shah
Dhruvenkumar Shah

Reputation: 528

// 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.

Upvotes: 1

Related Questions