subquery returning any result.

Dear Experts,

Why the below query not returning any row.?

         SELECT 1
    FROM (SELECT 1 col1, (SELECT COUNT(1) FROM t1) col2 FROM dual)
    WHERE col1 - col2 > 0

CREATE TABLE T1
   (      TABLE_NAME" VARCHAR2(30),
      TABLESPACE_NAME VARCHAR2(30)
   );
sakthikumarAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
You need NVL() on col2 [ or you could use COALESCE() ]

     SELECT 1
    FROM (SELECT 1 col1,  NVL( SELECT COUNT(1) FROM t1) ,0) col2 FROM dual)
    WHERE col1 - col2 > 0

But as soon as there is more than 1 row in t1 your where clause will prohibit any result.

e.g.
 number of rows in t1 = 10

1 - 10 = -9 which is not > 0
0
awking00Commented:
"where col1 - col2 > 0"
Since you made col1 = 1, then 1 - anything other than 0 or a negative number (which you can't get using count) will never be > 0 unless your count is 0. Can you provide a little more description of what your'e trying to do?
0
sakthikumarAuthor Commented:
This happens in 11g, if you change the optimizer to rule, then you will get a result.

I am not sure this is a bug in oracle or I dont see any valid reason for this?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PortletPaulfreelancerCommented:
    CREATE TABLE T1
        ("ID" INT)
    ;
    
    
    
    CREATE TABLE T1A
        ("ID" int)
    ;
    
    INSERT ALL 
        INTO T1A ("ID")
             VALUES (1)
    SELECT * FROM dual
    ;

Open in new window


    SELECT COUNT(1) FROM t1
    

**[Results][2]**:
    | COUNT(1) |
    |----------|
    |        0 |
**Query 2**:

    
    
    SELECT 1
        FROM (SELECT 1 col1, (SELECT COUNT(1) FROM t1) col2 FROM dual)
        WHERE col1 - col2 > 0
    

**[Results][3]**:
    | 1 |
    |---|
    | 1 |

Open in new window


    SELECT COUNT(1) FROM t1a
    

**[Results][4]**:
    | COUNT(1) |
    |----------|
    |        1 |
**Query 4**:

    
    
    SELECT 1
        FROM (SELECT 1 col1, (SELECT COUNT(1) FROM t1a) col2 FROM dual)
        WHERE col1 - col2 > 0
    

**[Results][5]**:

Open in new window


no row is returned IF col1 - col2 >0

1 (col1) - 1 (col2) = 0
0 is NOT greater than 0

hence no row is returned.

You need to CHANGE the WHERE condition
0
sakthikumarAuthor Commented:
I am using the below oracle version

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for HPUX: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

which version you are using. I am not getting any result for below. also no need to insert any row.

SELECT 1
  FROM (SELECT 1 col1, (SELECT COUNT(1) FROM t1) col2 FROM dual)
 WHERE col1 - col2 > 0
0
PortletPaulfreelancerCommented:
Your WHERE CLAUSE IS WRONG

------------------------------
col1 = 1

col2 = count(*) = 1

1 - 1 > 0 = FALSE = NO RESULT
---------------------------------
col1 = 1

col2 = count(*) = 999

1 - 999 > 0 = FALSE = NO RESULT
----------------------

try
WHERE COL2 >= 0

or

WHERE col1 = 1
0
sakthikumarAuthor Commented:
Hi portlet paul,


You did not understand my question.

create a table like below and do not insert any row in it.


CREATE TABLE T1
   (      TABLE_NAME" VARCHAR2(30),
      TABLESPACE_NAME VARCHAR2(30)
   );

execute the below query.

if no results are coming

then change the optimizer rule based.

execute the same query again.

just post your result here.
0
sakthikumarAuthor Commented:
Execute the query in my original post.
0
PortletPaulfreelancerCommented:
**Oracle 11g R2 Schema Setup**:

  CREATE TABLE T1
   (      TABLE_NAME VARCHAR2(30),
      TABLESPACE_NAME VARCHAR2(30)
   );
   
   
**Query 1**:
SELECT COUNT(1) FROM t1

**[Results]**:
| COUNT(1) |
|----------|
|        0 |

**Query 2**:
SELECT 1
FROM (SELECT 1 col1, (SELECT COUNT(1) FROM t1) col2 FROM dual)

**[Results]**:
| 1 |
|---|
| 1 |


**Query 3**:
SELECT 1
FROM (SELECT 1 col1, (SELECT COUNT(1) FROM t1) col2 FROM dual)
WHERE col1 - col2 > 0


**[Results]**:
| 1 |
|---|
| 1 |

see the above operating at: http://sqlfiddle.com/#!4/7c4a3/1 
   
If there is no row in the table the query returns a single row; but only then.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.