home *** CD-ROM | disk | FTP | other *** search
/ NetNews Usenet Archive 1992 #20 / NN_1992_20.iso / spool / comp / database / oracle / 1561 < prev    next >
Encoding:
Internet Message Format  |  1992-09-11  |  3.7 KB
  1. Path: sparky!uunet!wupost!usc!rpi!bu.edu!dartvax!kip-sn-49.dartmouth.edu!user
  2. From: carl.pedersen@dartmouth.edu (L. Carl Pedersen)
  3. Newsgroups: comp.databases.oracle
  4. Subject: Weird GROUP BY/TO_CHAR(date) Interaction?
  5. Message-ID: <carl.pedersen-110992122420@kip-sn-49.dartmouth.edu>
  6. Date: 11 Sep 92 16:28:59 GMT
  7. Sender: news@dartvax.dartmouth.edu (The News Manager)
  8. Followup-To: comp.databases.oracle
  9. Organization: Dartmouth College
  10. Lines: 96
  11.  
  12. Can anyone explain the result of the middle select, below?
  13.  
  14. SQL> select dtgrd, count(*) from lmasv where dtgrd >= '01-JAN-91' group by
  15. dtgrd;
  16.  
  17. DTGRD       COUNT(*)
  18. --------- ----------
  19. 01-FEB-91          4
  20. 01-MAR-91         47
  21. 01-APR-91          1
  22. 01-MAY-91          7
  23. 01-JUN-91        541
  24. 01-AUG-91         42
  25. 01-DEC-91         37
  26. 01-MAR-92         51
  27. 01-MAY-92          3
  28. 01-JUN-92        590
  29. 01-JUN-93          1
  30.  
  31. 11 rows selected.
  32.  
  33. SQL> select to_char(dtgrd), count(*) from lmasv where dtgrd >= '01-JAN-91'
  34. group by dtgrd;
  35.  
  36. TO_CHAR(DTGRD)                                                             
  37.   COUNT(*)
  38. ---------------------------------------------------------------------------
  39. ----------
  40. 01-JUN-83                                                                  
  41.          4
  42. 01-JUN-83                                                                  
  43.         47
  44. 01-JUN-83                                                                  
  45.          1
  46. 01-JUN-83                                                                  
  47.          7
  48. 01-JUN-83                                                                  
  49.        541
  50. 01-JUN-83                                                                  
  51.         42
  52. 01-JUN-83                                                                  
  53.         37
  54. 01-JUN-83                                                                  
  55.         51
  56. 01-JUN-83                                                                  
  57.          3
  58. 01-JUN-83                                                                  
  59.        590
  60. 01-JUN-83                                                                  
  61.          1
  62.  
  63. 11 rows selected.
  64.  
  65. SQL> select to_char(dtgrd), count(*) from lmasv where dtgrd >= '01-JAN-91'
  66. group by to_char(dtgrd);
  67.  
  68. TO_CHAR(DTGRD)                                                             
  69.   COUNT(*)
  70. ---------------------------------------------------------------------------
  71. ----------
  72. 01-APR-91                                                                  
  73.          1
  74. 01-AUG-91                                                                  
  75.         42
  76. 01-DEC-91                                                                  
  77.         37
  78. 01-FEB-91                                                                  
  79.          4
  80. 01-JUN-91                                                                  
  81.        541
  82. 01-JUN-92                                                                  
  83.        590
  84. 01-JUN-93                                                                  
  85.          1
  86. 01-MAR-91                                                                  
  87.         47
  88. 01-MAR-92                                                                  
  89.         51
  90. 01-MAY-91                                                                  
  91.          7
  92. 01-MAY-92                                                                  
  93.          3
  94.  
  95. 11 rows selected.
  96.  
  97. SQL>
  98.  
  99. Apologies for lines that are wrapped funny.  The question is, where the
  100. heck did it get the values for the first column in the second query?? 
  101. Aside from the order change, shouldn't the 2nd & 3rd queries have given the
  102. same result?  (None of these dates have times.)
  103.  
  104. I don't seem to have any COLUMN or BREAK commands that are affecting this,
  105. but even if I did, what could cause this?
  106.  
  107. Is this a bug?  In the kernel?  SQL*Plus?
  108.