asked on

SELECT and COUNT(*) in the same SQL

I have a SQL as follows:

select nvl(Col1, 'SomeVal')
from Tb1
where Col2 like '%A%'

If there is a Col2 value like A in a row and Col1 is NULL this returns 'SomeVal'. However, if there is no row with Col2 value like A, I would still like to return the same value of 'SomeVal'. I have to do this in one SELECT so other solutions such as stored procedure and anonymous blocks are not options. Any ideas?

slightwv (䄆 Netminder)

Not sure I understand. Are you wanting one row of 'SomeVal' if there are not any rows like '%A%'?

What if there is one 'A' and null with 50 not nulls with 'A'?

Given the following test case what are your expected results:

--run1
create table tab1(col1 char(1), col2 char(1));
insert into tab1 values('a','A');
insert into tab1 values(null,'A');
insert into tab1 values('b','B');
insert into tab1 values(null,'B');
commit;

--run2
delete from tab1;
insert into tab1 values('a','A');
insert into tab1 values(null,'A');
commit;

--run3
delete from tab1;
insert into tab1 values('b','B');
insert into tab1 values(null,'B');
commit;

nQuote

ASKER

I am not sure I understand your question but here is what I am trying to mimic in one SELECT:

declare vMyVar varchar2(7);

begin
select nvl(Col1, 'SomeVal') into vMyVar
from Tb1
where Col2 like '%A%';

exception
when no_data_found then
vMyVar := SomeVal;
end;

Does this answer your question?

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slightwv (䄆 Netminder)

>>Does this answer your question?

Not really. What if tb1 has two rows where Col2 like '%A%'. One where col1 is null and one where it isn't?

Sean Stuber

if, you are looking for multiple rows of col1 to be returned, but if there are none to be found, then return a single made up row with "SomeVal" , then try this...

SELECT col1
FROM (SELECT 1 n, col1
FROM tbl1
WHERE col2 LIKE '%A%'
UNION ALL
SELECT 2 n, 'SomeVal' FROM DUAL
ORDER BY n)
WHERE (ROWNUM = 1 AND n = 2) OR n = 1

nQuote

ASKER

sdstuber, I think that did it. I'll check further and get back to you.

chandra sekhar

Hi,

Below query useful for single record or multiple records.

SELECT NVL(COL1,1) FROM TEST_3 WHERE COL2=ALL(SELECT col2 from test_3 where col2='aA');

Sean Stuber

that doesn't return anything if test_3 has no rows for that col2 condition.

Plus, it's woefully inefficient to force a double query on the same table for an implicit condition.

WHERE COL2=ALL(SELECT col2 from test_3 where col2='aA');

Open in new window

is a really inefficient way to do this

WHERE COL2='A'

Open in new window

Juan Carlos

Select nvl(col1,'someval')
From tbl
Where col2 like '%A%' or
(Not col2 like '%A'%' And col1 is null)