| fetch the same value with & without using the trunc ?? [message #632568] |
Tue, 03 February 2015 04:51  |
 |
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
|
Senior Member |
|
|
Hi all,
SELECT pointed_date pointed_date,
SUM (Nvl(quantity, 0)) quantity ,
item_id
FROM table_1
WHERE pointed_date >= Add_months (Trunc(SYSDATE), -4)
GROUP BY pointed_date
SELECT Trunc(pointed_date) pointed_date,
SUM (Nvl(quantity, 0)) qty,
item_id
FROM table_1
WHERE pointed_date >= Add_months (Trunc(SYSDATE), -4)
GROUP BY Trunc(pointed_date),item_id
Without Trunc
Date Qty Item_id
---- --- -------
4/28/2014 -37 43694
4/28/2014 2:35 -50 34966
4/28/2014 2:37 -30 34988
4/28/2014 2:57 -50 34966
4/28/2014 2:57 -30 34963
4/28/2014 3:01 -50 34966
4/28/2014 4:31 -3 43694
4/28/2014 5:40 -14 28393
4/28/2014 5:54 -10 28491
4/28/2014 6:12 -12 32686
4/28/2014 6:24 -6 19423
With trunc
4/28/2014 -50 34958
4/28/2014 -250 34965
4/28/2014 -6 32688
4/28/2014 -8 14653
4/28/2014 -30 1354
4/28/2014 -10 36119
Why i am getting different Item_id's for both queries??
I need to fetch same values
Edited by Lalit : edited a typo in topic title, it was truc instead of trunc]
[Updated on: Tue, 03 February 2015 04:59] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: fetch the same value with & without using the truc ?? [message #632570 is a reply to message #632568] |
Tue, 03 February 2015 04:57  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
mist598 wrote on Tue, 03 February 2015 16:21
GROUP BY pointed_date
GROUP BY Trunc(pointed_date),item_id
Different queries, different results.
Quote:I need to fetch same values
What does this mean? How could you expect same results for two completely different operations.
|
|
|
|
|
|
|
|
| Re: fetch the same value with & without using the truc ?? [message #632573 is a reply to message #632571] |
Tue, 03 February 2015 05:07 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
mist598 wrote on Tue, 03 February 2015 16:30Quote:What does this mean? How could you expect same results for two completely different operations.
But dates are same right?, why it is giving d/f Item_id's , how to get as same as 1st Query ??
Can you prove the two dates "SYSDATE and TRUNC(SYSDATE)" are same. If you group by trunc(date), you lose the time portion, thus all the rows with same date values are grouped.
[Updated on: Tue, 03 February 2015 05:08] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: fetch the same value with & without using the trunc ?? [message #632578 is a reply to message #632577] |
Tue, 03 February 2015 06:08 |
|
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
|
Senior Member |
|
|
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
SELECT pointed_date pointed_date,
SUM (Nvl(quantity , 0)) quantity ,
item_id
FROM table_1
WHERE 1=1
and pointed_date >= Add_months (SYSDATE, -4)
GROUP BY pointed_date,item_id
order by item_id desc
output
------
3/28/2014 7:56 -1 43694
4/2/2014 4:40 -9 43694
4/7/2014 4:26 -3 43694
SELECT pointed_date,qty,item_id
from (select Trunc(pointed_date) pointed_date,
SUM (Nvl(quantity ,0)) qty,
item_id
FROM table_1
WHERE 1=1
and pointed_date >= Add_months (SYSDATE, -4)
GROUP BY Trunc(pointed_date),inventory_item_id)
order by item_id desc
output
------
3/28/2014 -37 43694
4/2/2014 -33 43694
4/7/2014 -27 43694
Both output's
3/28/2014 7:56 -1 43694 3/28/2014 -37 43694
4/2/2014 4:40 -9 43694 4/2/2014 -33 43694
4/7/2014 4:26 -3 43694 4/7/2014 -27 43694
Why the SUM's are different??
|
|
|
|
|
|
|
|
|
|
| Re: fetch the same value with & without using the trunc ?? [message #632582 is a reply to message #632578] |
Tue, 03 February 2015 06:21 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
mist598 wrote on Tue, 03 February 2015 17:38
Why the SUM's are different??
Michel Cadot wrote on Tue, 03 February 2015 17:27
Prove I am wrong by posting a COMPLETE TEST CASE we can reproduce as we already have asked you many times
Michel Cadot wrote on Tue, 03 February 2015 17:40
Why you didn't post a test case?
More?
Littlefoot wrote on Thu, 21 August 2014 12:56 If you could provide test case and explain the problem, someone might assist better than I can.
pablolee wrote on Thu, 21 August 2014 14:12provide a test case as has been asked of you innumerable times in the past
Lalit Kumar B wrote on Fri, 22 August 2014 19:51@mist598,
Invalid test cases repeatedly, that too after 900+ posts?
Bookmark this link TEST CASE
|
|
|
|
| Re: fetch the same value with & without using the trunc ?? [message #632583 is a reply to message #632582] |
Tue, 03 February 2015 06:28 |
|
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
|
Senior Member |
|
|
create table table_1(pointed_date date,quantity number,item_id number)
insert into table_1 values(to_date('3/28/2014 7:56','mm/dd/yyyy hh:ss'), -1,43694)
insert into table_1 values(to_date('4/2/2014 4:40','mm/dd/yyyy hh:ss'), -9,43694)
insert into table_1 values(to_date('4/7/2014 4:26','mm/dd/yyyy hh:ss'), -3,43694)
--drop table table_1
SELECT pointed_date pointed_date,
SUM (Nvl(quantity , 0)) quantity ,
item_id
FROM table_1
WHERE 1=1
--and pointed_date >= Add_months (SYSDATE, -4)
GROUP BY pointed_date,item_id
order by item_id desc
SELECT pointed_date,qty,item_id
from (select Trunc(pointed_date) pointed_date,
SUM (Nvl(quantity ,0)) qty,
item_id
FROM table_1
WHERE 1=1
--and pointed_date >= Add_months (SYSDATE, -4)
GROUP BY Trunc(pointed_date),item_id)
order by item_id desc
Both are giving me the same result. I don't know why my original query d/f
Thanks every body.. 
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
