Sql – Ignoring a column when condition doesnt match in select query – SQL-Oracle

oraclesql

My requirement is to display a Column in select query when a condition satisfies and not to display the column when condition is not matched.

For eg : In this simple table below

Table: XYZ

Name    ID  Fairness
------  --  --------
harish  3   White
ravi    5   brown 
arun    2   black 
rahul   5   white

Query:

select name,
       case id when 5 then " I Like to learn more languages" end as Remarks,
       Fairness
from xyz
where id=2

My requirement is in the above query "Remarks" column should not be displayed in output, but my output is

Actual Output:

Name  Remarks  Fairness
----  -------  --------
arun  null     black

Expected Output:

Name  Fairness
----  --------
arun  black

i.e, I need remarks column to be displayed only if the id is 5 in where clause.

Please provide me help to ignore "Remarks" when the condition is not satisfied or met.

Best Solution

Perhaps you want the SQL COALESCE function?

select coalesce(myMaybeNullColumn, '') from foo

will give blanks instead of nulls.

Related Solutions

Sql – How to Join to first row

SELECT   Orders.OrderNumber, LineItems.Quantity, LineItems.Description
FROM     Orders
JOIN     LineItems
ON       LineItems.LineItemGUID =
         (
         SELECT  TOP 1 LineItemGUID 
         FROM    LineItems
         WHERE   OrderID = Orders.OrderID
         )

In SQL Server 2005 and above, you could just replace INNER JOIN with CROSS APPLY:

SELECT  Orders.OrderNumber, LineItems2.Quantity, LineItems2.Description
FROM    Orders
CROSS APPLY
        (
        SELECT  TOP 1 LineItems.Quantity, LineItems.Description
        FROM    LineItems
        WHERE   LineItems.OrderID = Orders.OrderID
        ) LineItems2

Please note that TOP 1 without ORDER BY is not deterministic: this query you will get you one line item per order, but it is not defined which one will it be.

Multiple invocations of the query can give you different line items for the same order, even if the underlying did not change.

If you want deterministic order, you should add an ORDER BY clause to the innermost query.

Example sqlfiddle

Sql – Selecting top n elements of a group in Oracle

Is this what you are after?

My test set-up:

SQL> alter session set nls_date_format = 'DD-Mon-YYYY HH24:Mi:SS';

Session altered.

SQL> drop table so_test;

Table dropped.

SQL> create table so_test  (
  2    n varchar2(32)
  3  , v varchar2(32)
  4  , t date );

Table created.

SQL> 
SQL> insert into so_test values ( 'X' , 'Test1', to_date('01-Jan-2011 12:00:00','DD-Mon-YYYY HH24:Mi:SS') );

1 row created.

SQL> insert into so_test values ( 'X' , 'Test2', to_date('01-Jan-2011 13:00:00','DD-Mon-YYYY HH24:Mi:SS') );

1 row created.

SQL> insert into so_test values ( 'X' , 'Test3', to_date('01-Jan-2011 14:00:00','DD-Mon-YYYY HH24:Mi:SS') );

1 row created.

SQL> insert into so_test values ( 'Y' , 'Test5', to_date('02-Jan-2011 12:00:00','DD-Mon-YYYY HH24:Mi:SS') );

1 row created.

SQL> insert into so_test values ( 'Y' , 'Test6', to_date('03-Jan-2011 12:00:00','DD-Mon-YYYY HH24:Mi:SS') );

1 row created.

SQL> insert into so_test values ( 'Y' , 'Test7', to_date('04-Jan-2011 12:00:00','DD-Mon-YYYY HH24:Mi:SS') );

1 row created.

SQL>

Here is the query:

SQL> select n,v,t from (
  2  select n, v , t , rank() over ( partition by n order by t desc) r
  3  from so_test
  4  ) where r <= 2;

N                V                T
-------------------------------- -------------------------------- --------------------
X                Test3                01-Jan-2011 14:00:00
X                Test2                01-Jan-2011 13:00:00
Y                Test7                04-Jan-2011 12:00:00
Y                Test6                03-Jan-2011 12:00:00

SQL>

Best Solution

Related Solutions

Sql – How to Join to first row

Sql – Selecting top n elements of a group in Oracle

Related Question