Home » SQL & PL/SQL » SQL & PL/SQL » Help with a select statement (Oracle v10)
Help with a select statement [message #582344] Wed, 17 April 2013 05:50 Go to next message
clancypc
Messages: 33
Registered: December 2006
Member
I have a select statement below:
SELECT rpad(bnumber,30) as "B Number", count(bnumber) as "Call Count"
FROM   day_tables
WHERE  cdrstart BETWEEN to_date('14-Apr-2013')
AND    to_date('15-Apr-2013')
AND    bnumber like '087%'
AND    anumber not like '44%'
GROUP BY bnumber having count(bnumber) >= 20
ORDER BY count(bnumber) DESC;


I have found out when I run the statement that the line 'AND anumber not like '44%' also excludes records where the anumbers have no value, ie are null. I need to include these records. I have tried adding in the condition 'OR anumber is null' but that is still running now so it has been running for a couple of hours so that is not an acceptable solution.
Does anyone have a better suggestion?
Thanks

Re: Help with a select statement [message #582348 is a reply to message #582344] Wed, 17 April 2013 06:40 Go to previous messageGo to next message
cookiemonster
Messages: 10903
Registered: September 2008
Location: Rainy Manchester
Senior Member
For performance issues please read How to tune SQL or Identify Performance Problem and Bottleneck and supply the requested information
Re: Help with a select statement [message #582350 is a reply to message #582348] Wed, 17 April 2013 06:56 Go to previous messageGo to next message
clancypc
Messages: 33
Registered: December 2006
Member
But its not performance tuning help I am looking for. I cant think how to word the query to exclude records where the anumber begin with 44 but include records where the anumber is null.
Re: Help with a select statement [message #582351 is a reply to message #582350] Wed, 17 April 2013 07:04 Go to previous messageGo to next message
pablolee
Messages: 2612
Registered: May 2007
Location: Scotland
Senior Member
NVL(anumber,'1') not like '44%'
Re: Help with a select statement [message #582352 is a reply to message #582348] Wed, 17 April 2013 07:06 Go to previous messageGo to next message
sss111ind
Messages: 473
Registered: April 2012
Location: India
Senior Member

select * from emp where nvl(comm,4) not like '3%';--4 never like 3

select * from emp where comm not like'3%' or comm is null;--it's also working
Re: Help with a select statement [message #582354 is a reply to message #582352] Wed, 17 April 2013 07:17 Go to previous messageGo to next message
clancypc
Messages: 33
Registered: December 2006
Member
nvl(anumber,'1') not like '44%' works perfectly and returned in 167 seconds.
The other option anumber is null did eventually finish with a "cannot extend tablespace TEMP ..." error message so definitely not the way to go.
Thanks for that
Re: Help with a select statement [message #582358 is a reply to message #582354] Wed, 17 April 2013 07:26 Go to previous messageGo to next message
Michel Cadot
Messages: 58847
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
WHERE  cdrstart BETWEEN to_date('14-Apr-2013')
AND    to_date('15-Apr-2013')


This is wrong:
SQL> select * from dual
  2  where sysdate BETWEEN to_date('14-Apr-2013')
  3  AND    to_date('15-Apr-2013')
  4  /
AND    to_date('15-Apr-2013')
               *
ERROR at line 3:
ORA-01858: a non-numeric character was found where a numeric was expected

TO_DATE must always contain a format.

Regards
Michel
Re: Help with a select statement [message #582361 is a reply to message #582350] Wed, 17 April 2013 07:48 Go to previous messageGo to next message
cookiemonster
Messages: 10903
Registered: September 2008
Location: Rainy Manchester
Senior Member
clancypc wrote on Wed, 17 April 2013 12:56
But its not performance tuning help I am looking for. I cant think how to word the query to exclude records where the anumber begin with 44 but include records where the anumber is null.


How you said you did it in the first place should be fine, assuming you rememebered to add the necessary brackets, nvl also works, as you found out (and other functions as well) but there's no reason to think that would be any faster than an is null check.
The fact that it was a lot faster makes me think you missed some brackets when you added the is null check

EDIT: added some missing words

[Updated on: Wed, 17 April 2013 07:48]

Report message to a moderator

Re: Help with a select statement [message #582362 is a reply to message #582352] Wed, 17 April 2013 07:52 Go to previous message
joy_division
Messages: 4503
Registered: February 2005
Location: East Coast USA
Senior Member
sss111ind wrote on Wed, 17 April 2013 08:06
select * from emp where nvl(comm,4) not like '3%';--4 never like 3


This is poor coding.
The second parameter of NVL here is a number, yet you are comparing it to a string. So, either you are using NVL with two different datatypes, or you are comparing it's result with a different datatype.
Previous Topic: Anonymous PLSQL - need output with 0.
Next Topic: Formatting Output into multiple columns
Goto Forum:
  


Current Time: Wed Aug 20 17:44:19 CDT 2014

Total time taken to generate the page: 0.13934 seconds