Re: query needed very urg.

From: Michael O'Shea <michael.oshea_at_tessella.com>
Date: 14 Dec 2006 01:09:10 -0800
Message-ID: <1166087350.122386.216800@n67g2000cwd.googlegroups.com>

peter wrote:

> Hi all,
> Please tell me query for following conditions:
>
> Conditions are:
>
> 1) a=12, b=3, n=7
> 2) a=12, b=3, n=NULL
> 3) a=12, b=NULL, n=NULL
>
> if 1 is true, I don't need any record of 2 or 3. But if it is false
> then
> I need all record of 2, but if 1 and 2 both are wrong then only I need
> 3.

Hi Peter, please find inline code for creating test data and the SQL query you require from my understanding of your post. Note that the example I have created executes on 10.1.0.4 - you omitted the target Oracle version from your post.

Regards
Mike

TESSELLA Michael.OShea_at_tessella.com

__/__/__/  Tessella Support Services plc
__/__/__/  3 Vineyard Chambers, ABINGDON, OX14 3PX, England
__/__/__/  Tel: (44)(0)1235-555511  Fax: (44)(0)1235-553301

www.tessella.com Registered in England No. 1466429

SQL>
SQL>
SQL> CREATE TABLE tblTest(a NUMBER, b NUMBER, n NUMBER, whateverElse

VARCHAR2(15)); Table created.

SQL> INSERT INTO tblTest(a,b,n,whateverElse ) 2 VALUES(12,3,7,'condition 1');

1 row created.

SQL> INSERT INTO tblTest(a,b,n,whateverElse ) 2 VALUES(12,3,NULL,'condition 2.1');

1 row created.

SQL> INSERT INTO tblTest(a,b,n,whateverElse ) 2 VALUES(12,3,NULL,'condition 2.2');

1 row created.

SQL> INSERT INTO tblTest(a,b,n,whateverElse ) 2 VALUES(12,NULL,NULL,'condition 3');

1 row created.

SQL>
SQL> SELECT a,b,n,whateverElse
2 FROM
3 (

  4      SELECT t.*,
  5             DENSE_RANK() OVER (PARTITION BY a ORDER BY a,b,n) rn
  6       FROM tblTest t
  7        WHERE a=12
  8         AND NVL(b,3)=3
  9         AND NVL(n,7)=7

10 ) WHERE rn=1;

A B N WHATEVERELSE ---------- ---------- ---------- ---------------

12 3 7 condition 1

SQL> SELECT *
2 FROM V$VERSION; BANNER

Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Prod PL/SQL Release 10.1.0.4.0 - Production
CORE 10.1.0.4.0 Production
TNS for 32-bit Windows: Version 10.1.0.4.0 - Production NLSRTL Version 10.1.0.4.0 - Production

SQL>
SQL>
SQL>

Received on Thu Dec 14 2006 - 03:09:10 CST