¿À¶óŬ Äõ¸®¿¡¼­ DATE ŸÀÔÀÌ CHAR ŸÀÔº¸´Ù ºü¸¥°¡¿ä?

¹ÎºÀ±â   
   Á¶È¸ 4975   Ãßõ 9    

´ÙÀ½°ú °°Àº µÎ°³ÀÇ Äõ¸®´Â °á°ú´Â ¶È°°À¸¸ç
date ŸÀÔÀ» ½è´À³Ä char ŸÀÔÀ» ½è´À³Ä¸é ´Ù¸¨´Ï´Ù.

(1)
        SELECT u.OBJECT_ID, u.DATE_DAY, u.DATE_TIME
                   ,u.TOTAL_VALUE
                   ,u.TOTAL_NUM
                   ,u.MIN_VALUE
                   ,INST1.fnpempa9001(u.OBJECT_ID, u.DATE_DAY, u.DATE_TIME, u.MIN_VALUE) MIN_TIME
                   ,u.MAX_VALUE
                   ,INST1.fnpempa9002(u.OBJECT_ID, u.DATE_DAY, u.DATE_TIME, u.MAX_VALUE) MAX_TIME
                   ,INST1.fnpempa9003(u.OBJECT_ID, u.DATE_DAY, u.DATE_TIME) LAST_VALUE
                   ,INST1.fnpempa9004(u.OBJECT_ID, u.DATE_DAY, u.DATE_TIME) LAST_TIME
        FROM (SELECT OBJECT_ID, DATE_DAY, DATE_TIME
                                   ,SUM(VALUE) TOTAL_VALUE
                                   ,COUNT(VALUE) TOTAL_NUM
                                   ,MIN(VALUE) MIN_VALUE
                                   ,MAX(VALUE) MAX_VALUE
                        FROM (
                                 SELECT OBJECT_ID,VALUE_HISTORY_ID, VALUE, DATE_DAY, DATE_TIME
                                 FROM (SELECT a.OBJECT_ID, a.VALUE_HISTORY_ID, a.VALUE
                                                                ,TO_CHAR(a.TIMESTAMP,'YYYYMMDD') DATE_DAY
                                                                ,TO_CHAR(a.TIMESTAMP,'HH24') DATE_TIME
                                           FROM INST1.TSPEMPA60 a, INST1.TSPEMPA50 b
                                           WHERE a.OBJECT_ID = b.OBJECT_ID
                                                             --AND b.NODE_NAME = 'atmsdb1' /*Input - ¼­¹ö¸í*/
                                          )
                                 WHERE 1=1
                                            AND DATE_DAY = '20070227' /*Input - ³¯Â¥*/                                
                                )
                        GROUP BY OBJECT_ID, DATE_DAY, DATE_TIME
                ) u

(2)
        SELECT u.OBJECT_ID, u.DATE_DAY, u.DATE_TIME
                   ,u.TOTAL_VALUE
                   ,u.TOTAL_NUM
                   ,u.MIN_VALUE
                   ,INST1.fnpempa9001(u.OBJECT_ID, u.DATE_DAY, u.DATE_TIME, u.MIN_VALUE) MIN_TIME
                   ,u.MAX_VALUE
                   ,INST1.fnpempa9002(u.OBJECT_ID, u.DATE_DAY, u.DATE_TIME, u.MAX_VALUE) MAX_TIME
                   ,INST1.fnpempa9003(u.OBJECT_ID, u.DATE_DAY, u.DATE_TIME) LAST_VALUE
                   ,INST1.fnpempa9004(u.OBJECT_ID, u.DATE_DAY, u.DATE_TIME) LAST_TIME
        FROM (SELECT a.OBJECT_ID, DATE_DAY, DATE_TIME
                                   ,SUM(VALUE) TOTAL_VALUE
                                   ,COUNT(VALUE) TOTAL_NUM
                                   ,MIN(VALUE) MIN_VALUE
                                   ,MAX(VALUE) MAX_VALUE
                        FROM INST1.TSPEMPA60_T a, INST1.TSPEMPA50 b
                        WHERE a.OBJECT_ID = b.OBJECT_ID
                                             AND b.NODE_NAME = 'atmsdb1' /*Input - ¼­¹ö¸í*/
                                         AND DATE_DAY = '20070227' /*Input - ³¯Â¥*/
                        GROUP BY a.OBJECT_ID, DATE_DAY, DATE_TIME
                ) u


µ¥ÀÌÅÍ´Â ¿ø½Ã Å×À̺íÀÇ µ¥ÀÌÅÍ °Ç¼ö°¡ õ¸¸°Ç À̳»À̸ç
¼Óµµ´Â (1)¹ø Äõ¸®°¡ (2)¹ø º¸´Ù 30¹è Á¤µµ ºü¸¨´Ï´Ù.
À¯ÀÏÇÑ Â÷ÀÌ´Â TIMESTAMP¶ó°í µÇ¾î ÀÖ´Â Ä÷³ÀÌ DATE ŸÀÔÀ¸·Î Á¤ÀǵǾî Àִµ¥
ÀÌ ºÎºÐÀÌ DATE_DAY¿Í µ¿ÀÏÇÏ°Ô Å¸ÀÔ¸¸ ´Ù¸¥°ÍÀ̰ŵç¿ä.

¿ø·¡ DATE ŸÀÔÀÌ ºü¸¥°ÇÁö... ÀÌÁ¤µµ·Î ºü¸¥°ÇÁö ¾Ë°í ½Í½À´Ï´Ù.
¾Æ´Ô ¼Óµµ Â÷ÀÌ°¡ ³ª´Âµ¥ ´Ù¸¥ ¿äÀÎÀÌ ÀÖ¾ú´ø °ÇÁö...

À妽º´Â (1)°æ¿ì¿¡ OBJECT_ID ¿¡ °É·Á ÀÖÀ¸¸ç
(2)°æ¿ì¿¡ OBJECT_ID ±×¸®°í NODE_NAME, DATE_DAY¿¡ °É·Á ÀÖ´Â »óÅÂÀÔ´Ï´Ù.
ªÀº±Û Àϼö·Ï ½ÅÁßÇÏ°Ô.


QnA
Á¦¸ñPage 4525/5693
2014-05   5021281   Á¤ÀºÁØ1
2015-12   1556614   ¹é¸Þ°¡
2005-01   6687   À¯È£ÁØ
2005-05   5650   ¼Õ»ó¿í
2003-03   10694   Á¤Àç¿õ
2003-12   9025   ±è±¤Áß
2006-04   5601   ¹é¸íö
2006-06   5063   À±¿µ¹è
2008-01   6032   ¹æÈ¿¹®
2003-04   11142   Á¤Çö±¸
2005-12   4890   À±È£¿ë
2004-07   7318   ±èÇüÇÊ
2002-06   15614   ¹Ú°æÈ£
2002-07   14378   ±è¿ì¼º
2003-05   11136   ±èÀÏȯ
2003-09   11931   Á¤ÁÖȯ
2004-12   6632   ¹é¼ºÁÖ
2005-05   6171   À̽ÂÇö
2004-09   6550   ¿À±â·æ
2005-07   5573   À±È£¿ë
2006-01   4847   È«»óÈÆ
2004-09   7373   ¿¹º´±â