Need a unique gift idea?
A Pastebin account makes a great Christmas gift
SHARE
TWEET

Untitled

a guest Aug 10th, 2018 62 Never
Upgrade to PRO!
ENDING IN00days00hours00mins00secs
 
  1. Oracle UNPIVOT and SYSDATE giving weird results
  2. WITH
  3. query AS
  4. (
  5.     SELECT    SYSDATE AS SomeDate,
  6.               'One' AS One,
  7.               'Two' AS Two,
  8.               'Three' AS Three,
  9.               'Four' AS Four,
  10.               'Five' AS Five
  11.         FROM dual
  12. ),
  13. up_query AS
  14. (
  15.     SELECT *
  16.     FROM query
  17.     UNPIVOT
  18.     (
  19.      NUM FOR DUMMY
  20.      IN
  21.      (
  22.       One AS 'One',
  23.       Two AS 'Two',
  24.       Three AS 'Three',
  25.       Four AS 'Four',
  26.       Five AS 'Five'
  27.      )
  28.     )
  29. )
  30. SELECT SYSDATE, b.*
  31.   FROM up_query  b;
  32.    
  33. SYSDATE   SOMEDATE       DUMMY  NUM
  34. 09-DEC-11 09-DEC-07      One    One
  35. 09-DEC-11 09-DEC-07      Two    Two
  36. 09-DEC-11 09-DEC-07      Three  Three
  37. 09-DEC-11 09-DEC-07      Four   Four
  38. 09-DEC-11 09-DEC-07      Five   Five
  39.    
  40. $ sqlplus / as sysdba
  41.  
  42. SQL*Plus: Release 11.2.0.3.0 Production on Sat Dec 10 01:20:32 2011
  43.  
  44. Copyright (c) 1982, 2011, Oracle.  All rights reserved.
  45.  
  46.  
  47. Connected to:
  48. Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
  49. With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
  50. Data Mining and Real Application Testing options
  51.  
  52. SQL> WITH
  53.   2  query AS
  54.   3  (
  55.   4      SELECT    SYSDATE AS SomeDate,
  56.   5                'One' AS One,
  57.   6                'Two' AS Two,
  58.   7                'Three' AS Three,
  59.   8                'Four' AS Four,
  60.   9                'Five' AS Five
  61.  10          FROM dual
  62.  11  ),
  63.  12  up_query AS
  64.  13  (
  65.  14      SELECT *
  66.  15      FROM query
  67.  16      UNPIVOT
  68.  17      (
  69.  18       NUM FOR DUMMY
  70.  19       IN
  71.  20       (
  72.  21        One AS 'One',
  73.  22        Two AS 'Two',
  74.  23        Three AS 'Three',
  75.  24        Four AS 'Four',
  76.  25        Five AS 'Five'
  77.  26       )
  78.  27      )
  79. )
  80.  28   29  SELECT SYSDATE, b.*
  81.  30    FROM up_query  b;
  82.  
  83. SYSDATE   SOMEDATE  DUMMY NUM
  84. --------- --------- ----- -----
  85. 10-DEC-11 10-DEC-11 One   One
  86. 10-DEC-11 10-DEC-11 Two   Two
  87. 10-DEC-11 10-DEC-11 Three Three
  88. 10-DEC-11 10-DEC-11 Four  Four
  89. 10-DEC-11 10-DEC-11 Five  Five
  90.    
  91. WITH
  92. query AS
  93. (
  94.     SELECT     TO_CHAR(SYSDATE, 'RRRRMMDD') AS SomeDate,
  95.               'One' AS One,
  96.               'Two' AS Two,
  97.               'Three' AS Three,
  98.               'Four' AS Four,
  99.               'Five' AS Five
  100.         FROM dual
  101. ),
  102. up_query AS
  103. (
  104.     SELECT *
  105.     FROM query
  106.     UNPIVOT
  107.     (
  108.      NUM FOR DUMMY
  109.      IN
  110.      (
  111.       One AS 'One',
  112.       Two AS 'Two',
  113.       Three AS 'Three',
  114.       Four AS 'Four',
  115.       Five AS 'Five'
  116.      )
  117.     )
  118. )
  119. SELECT SYSDATE, TO_DATE(SomeDate, 'RRRRMMDD') AS ActualSomeDate, b.*,
  120.   FROM up_query  b;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top