Home » SQL & PL/SQL » SQL & PL/SQL » incorrect output from my sql command (11g)
incorrect output from my sql command [message #611502] Thu, 03 April 2014 03:19 Go to next message
kekanap
Messages: 18
Registered: January 2008
Location: Centurion
Junior Member

Hallo Team,

I would like my output to return rows that do not start with 0 or D but i am getting the opposite please have a look at my sql statement below and the output.

input
SELECT to_char(calldate,'yyyy-mm-dd'), TO_CHAR (TRUNC (SYSDATE) + NUMTODSINTERVAL (calltime, 'second'),'hh24:mi:ss') CALLTIME, ani "ANUM", DESTINATION "BNUM",destination_prefix, DURATION,profile_code,stlmntcharge,totalacctcharge,round((stlmntcharge/duration)*60,2) PR
--SELECT servicetype,COUNT(*), ROUND(SUM(duration)/60,2), SUM(stlmntcharge), SUM(totalacctcharge)
FROM voipcdr 
WHERE (calldate >= TO_DATE('20131001','YYYYMMDD') AND calldate <= TO_DATE('20131031','YYYYMMDD'))
AND (remtrunkid IN (SELECT unique trunkid  FROM trunks  WHERE description LIKE '%Telkom%' AND gw_range_id = '61' AND trunkid like '9%')
OR (mind_utils.CONVERT_INT_TO_IP(remip) in ('196.35.130.52','196.35.130.53')))
and servicetype = 'TRANSIT'
AND usercode <> 'ZZZZZZ'
and direction = 'I'
and (destination_prefix <> '0%' or destination_prefix <> 'D%');`
group by servicetype;


Output:
TO_CHAR(CALLDATE,'YYYY-MM-DD')","CALLTIME","ANUM","BNUM","DESTINATION_PREFIX","DURATION","PROFILE_CODE","STLMNTCHARGE","TOTALACCTCHARGE","PR"
"2013-10-03","10:39:28","0414843291","0446202111","044",114,"PROFILE7",0.228,0.456,0.12
"2013-10-03","10:39:32","0413926900","0413738882","041",99,"PROFILE7",0.198,0.2805,0.12
"2013-10-03","10:39:57","0115320000","0183971500","018",17,"PROFILE7",0.034,0.068,0.12
"2013-10-03","10:39:48","0113920000","0118223177","011",114,"PROFILE7",0.228,0.323,0.12
"2013-10-03","10:39:52","0217004140","0214614823","021",56,"PROFILE7",0.112,0.1587,0.12
"2013-10-03","10:39:57","0115187000","0338975276","033",34,"PROFILE7",0.068,0.136,0.12
"2013-10-03","10:40:11","","0128031333","012",358,"PROFILE7",0.716,1.0143,0.12
"2013-10-03","10:22:11","0115499600","0313093031","031",214,"PROFILE7",0.428,0.6063,0.12
"2013-10-03","10:33:03","0118062000","0114731628","011",15,"PROFILE7",0.03,0.0425,0.12
"2013-10-03","10:32:49","0357512590","0315602633","031",101,"PROFILE7",0.202,0.2862,0.12
"2013-10-03","10:33:00","0215058000","0218843110","021",45,"PROFILE7",0.09,0.1275,0.12
"2013-10-03","10:40:26","","0319163112","031",14,"PROFILE7",0.028,0.0397,0.12
"2013-10-03","10:40:12","0116840000","0112148300","011",297,"PROFILE7",0.594,0.8415,0.12
"2013-10-03","10:40:15","0413601509","0466241099","046",64,"PROFILE7",0.128,0.256,0.12
"2013-10-03","10:45:35","0128109373","0128043500","012",51,"PROFILE7",0.102,0.1445,0.12
"2013-10-03","10:45:35","0873588210","0588636705","058",10,"PROFILE7",0.02,0.04,0.12
"2013-10-03","10:45:42","0310031400","0314622431","031",17,"PROFILE7",0.034,0.0482,0.12
"2013-10-03","10:45:40","0879411198","0217031414","021",41,"PROFILE7",0.082,0.1162,0.12
"2013-10-03","10:45:46","0116611000","0448870189","044",38,"PROFILE7",0.076,0.152,0.12
"2013-10-03","10:30:22","0123052500","0127342992","012",6,"PROFILE7",0.012,0.017,0.12
"2013-10-03","10:30:16","0123052521","0127342825","012",183,"PROFILE7",0.366,0.5185,0.12
"2013-10-03","10:30:20","0212000607","0117235216","011",138,"PROFILE7",0.276,0.391,0.12
"2013-10-03","10:30:20","0879405032","0317146000","031",26,"PROFILE7",0.052,0.0737,0.12
"2013-10-03","10:35:13","0117267700","0422961071","042",643,"PROFILE7",1.286,2.572,0.12
"2013-10-03","10:36:09","0119533750","0117905100","011",873,"PROFILE7",1.746,2.4735,0.12
"2013-10-03","10:36:54","0105939285","0125433843","012",435,"PROFILE7",0.87,1.2325,0.12
"2013-10-03","10:37:03","0310031400","0117970000","011",242,"PROFILE7",0.484,0.6857,0.12
"2013-10-03","10:37:52","0123911022","0123483538","012",720,"PROFILE7",1.44,2.04,0.12
"2013-10-03","10:38:05","0113961113","0119301731","011",124,"PROFILE7",0.248,0.3513,0.12
"2013-10-03","10:40:48","0283121954","0283140186","028",42,"PROFILE7",0.084,0.168,0.12
"2013-10-03","10:40:55","0343125845","0343127346","034",92,"PROFILE7",0.184,0.368,0.12
"2013-10-03","10:40:05","0113122678","0419941800","041",67,"PROFILE7",0.134,0.1898,0.12
"2013-10-03","10:40:02","0116080607","0118691151","011",47,"PROFILE7",0.094,0.1332,0.12
"2013-10-03","10:40:06","0537121031","0537121351","053",148,"PROFILE7",0.296,0.592,0.12
"2013-10-03","10:40:44","0115778600","0110225135","011",19,"PROFILE7",0.0697,0.0538,0.22
"2013-10-03","10:32:13","0437222531","0414510689","041",83,"PROFILE7",0.166,0.2352,0.12
"2013-10-03","10:32:20","0312060470","0312076451","031",12,"PROFILE7",0.024,0.034,0.12
"2013-10-03","10:32:35","0113122678","0126571630","012",57,"PROFILE7",0.114,0.1615,0.12
"2013-10-03","10:32:50","0215062600","0414844834","041",125,"PROFILE7",0.25,0.3542,0.12
"2013-10-03","10:39:07","0137416400","0137523069","013",292,"PROFILE7",0.584,1.168,0.12
"2013-10-03","10:39:11","0164223804","0164551436","016",81,"PROFILE7",0.162,0.324,0.12
"2013-10-03","10:40:47","0872307515","0163630017","016",28,"PROFILE7",0.056,0.112,0.12
"2013-10-03","10:45:49","0310031400","0314674472","031",24,"PROFILE7",0.048,0.068,0.12
"2013-10-03","10:45:50","0538322266","0538410178","053",81,"PROFILE7",0.162,0.324,0.12
"2013-10-03","10:45:43","0116080607","0123461904","012",18,"PROFILE7",0.036,0.051,0.12
"2013-10-03","10:45:48","0113830631","0113943071","011",44,"PROFILE7",0.088,0.1247,0.12
"2013-10-03","10:45:47","","0118571593","011",58,"PROFILE7",0.116,0.1643,0.12
Re: incorrect output from my sql command [message #611507 is a reply to message #611502] Thu, 03 April 2014 03:24 Go to previous messageGo to next message
Michel Cadot
Messages: 59994
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

"return rows that do not start with 0 or D"

This sentence is not clear and this is why you can't write the query.
Do you mean
- return rows that do not start with 0 or do not start with D
- return rows that do not start with 0 and do not start with D

You wrote the first one which will of course returns all rows that are not null.

[Updated on: Thu, 03 April 2014 03:25]

Report message to a moderator

Re: incorrect output from my sql command [message #611526 is a reply to message #611507] Thu, 03 April 2014 06:12 Go to previous messageGo to next message
kekanap
Messages: 18
Registered: January 2008
Location: Centurion
Junior Member

i mean

- return rows that do not start with 0 and do not start with D
Re: incorrect output from my sql command [message #611529 is a reply to message #611526] Thu, 03 April 2014 06:43 Go to previous messageGo to next message
cookiemonster
Messages: 11285
Registered: September 2008
Location: Rainy Manchester
Senior Member
So change your query accordingly.
Re: incorrect output from my sql command [message #611531 is a reply to message #611502] Thu, 03 April 2014 06:51 Go to previous messageGo to next message
EdStevens
Messages: 349
Registered: September 2013
Senior Member
kekanap wrote on Thu, 03 April 2014 03:19
Hallo Team,

I would like my output to return rows that do not start with 0 or D but i am getting the opposite please have a look at my sql statement below and the output.

input
SELECT . . . TO_CHAR (TRUNC (SYSDATE) + NUMTODSINTERVAL (calltime, 'second'),'hh24:mi:ss') CALLTIME, . . . 



As an aside from your immediate question, take a look at the snippet of your code I've quoted above. You are converting sysdate to a string (to_char)and then trying to apply a time function to that string. If that is giving you what you want, it is sheer dumb luck that Oracle is correctly guessing for an implied type conversion. Think that one through a bit more about exactly what you want to apply the to_char function to.

[Updated on: Thu, 03 April 2014 06:54]

Report message to a moderator

Re: incorrect output from my sql command [message #611603 is a reply to message #611531] Fri, 04 April 2014 06:31 Go to previous messageGo to next message
gauravgautam135
Messages: 31
Registered: December 2013
Member
Hi,

I have not analyzed your query conversion as highlighted by EdStevens. But I used your data and try to exclude all those records which are having '0' & 'D' in DESTINATION_PREFIX.

Please see below code:
SQL> WITH t as
  2  (SELECT '2013-10-03' c1,'10:39:28' c2,'0414843291' c3,'0446202111' c4,'044' DESTINATION_PREFIX,
114 c5,'PROFILE7' c6,0.228 c7,0.456 c8,0.12 c9 FROM DUAL
  3  UNION ALL
  4  SELECT '2013-10-03','10:39:32','0413926900','0413738882','141',99,'PROFILE7',0.198,0.2805,0.12 
FROM DUAL
  5  UNION ALL
  6  SELECT '2013-10-03','10:39:57','0115320000','0183971500','D18',17,'PROFILE7',0.034,0.068,0.12 F
ROM DUAL
  7  UNION ALL
  8  SELECT '2013-10-03','10:39:48','0113920000','0118223177','111',114,'PROFILE7',0.228,0.323,0.12 
FROM DUAL
  9  UNION ALL
 10  SELECT '2013-10-03','10:39:52','0217004140','0214614823','021',56,'PROFILE7',0.112,0.1587,0.12 
FROM DUAL
 11  UNION ALL
 12  SELECT '2013-10-03','10:39:57','0115187000','0338975276','033',34,'PROFILE7',0.068,0.136,0.12 F
ROM DUAL)
 13  SELECT * FROM t
 14   WHERE NOT (DESTINATION_PREFIX LIKE '0%' OR DESTINATION_PREFIX LIKE 'D%');

C1         C2       C3         C4         DES         C5 C6               C7         C8         C9
---------- -------- ---------- ---------- --- ---------- -------- ---------- ---------- ----------
2013-10-03 10:39:32 0413926900 0413738882 141         99 PROFILE7       .198      .2805        .12
2013-10-03 10:39:48 0113920000 0118223177 111        114 PROFILE7       .228       .323        .12


This excluded 044, D18, 021 & 033 destination_prefix records.
Re: incorrect output from my sql command [message #611619 is a reply to message #611603] Fri, 04 April 2014 10:01 Go to previous message
Michel Cadot
Messages: 59994
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Good, you succeed to put in SQL what has been said.

Previous Topic: finding missing parent from oracle hierarchical query
Next Topic: Two row into one row conversition
Goto Forum:
  


Current Time: Sat Dec 20 02:26:39 CST 2014

Total time taken to generate the page: 0.05291 seconds