Total Pageviews

2015/07/31

[Oracle to SQL Server Migration] TO_CHAR - Convert Datetime to String

In Oracle
SELECT to_char(MAX(PRT_DATE),'yyyymmdd')
FROM DBM000F1
WHERE FUNC_ID='DBM030R'
  AND RPT_ID='DBM030P'

 But SQL Server does not have to_char function, so it need to translate as bellowing:
SELECT CONVERT(VARCHAR(8), MAX(PRT_DATE), 112)
FROM DBM000F1
WHERE FUNC_ID='DBM030R'
  AND RPT_ID='DBM030P'

 Reference
[1] http://www.sqlines.com/oracle-to-sql-server/to_char_datetime
[2] https://msdn.microsoft.com/en-us/library/ms187928.aspx
Posted by Albert Kuo at 4:29 PM 0 comments
Labels:

[Oracle to SQL Server Migration] An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Problem
Here is my original SQL statement in Oracle:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
SELECT B.MASTER_AGE,
       ORG_TYPE,
       B.AGE,
       SUM(DEBT_AMT) B_AMT,
       SUBSTR(B.AGE_NAME,1,3) AGE_NAME
FROM DBM032FA A,
     DBM030FA B
WHERE A.STA_DATE = B.STA_DATE
  AND TO_CHAR(B.STA_DATE, 'yyyy') = :qryYear + 1911
  AND A.AGE = B.AGE
  AND DEBT_TYPE = 'B'
GROUP BY ORG_TYPE,
         B.MASTER_AGE,
         B.AGE,
         B.AGE_NAME
ORDER BY org_type

Owing to I need to change database to Microsoft SQL Server. Therefore, the foregoing SQL statement will be translated as following: 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
SELECT B.MASTER_AGE,
       ORG_TYPE,
       B.AGE,
       SUM(DEBT_AMT) B_AMT,
       SUBSTRING(B.AGE_NAME,1,3) AGE_NAME
FROM DBM032FA A,
     DBM030FA B
WHERE A.STA_DATE = B.STA_DATE
  AND CONVERT(VARCHAR(4), MAX(B.STA_DATE), 112) = :qryYear + 1911
  AND A.AGE = B.AGE
  AND DEBT_TYPE = 'B'
GROUP BY ORG_TYPE,
         B.MASTER_AGE,
         B.AGE,
         B.AGE_NAME
ORDER BY org_type

But as I executed this SQL statement in SQL Sever, it throw this exception unexpectedly:
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.(除非彙總置於 HAVING 子句或選取清單所包含的子查詢中，且彙總的資料行為外部參考，否則不得在 WHERE 子句中出現。)

 Solution
Move CONVERT(VARCHAR(4), MAX(B.STA_DATE), 112) = :qryYear + 1911 from where clause to having clause:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
SELECT B.MASTER_AGE,
       ORG_TYPE,
       B.AGE,
       SUM(DEBT_AMT) B_AMT,
       SUBSTRING(B.AGE_NAME,1,3) AGE_NAME
FROM DBM032FA A,
     DBM030FA B
WHERE A.STA_DATE = B.STA_DATE
  AND A.AGE = B.AGE
  AND DEBT_TYPE = 'B'
GROUP BY ORG_TYPE,
         B.MASTER_AGE,
         B.AGE,
         B.AGE_NAME
HAVING CONVERT(VARCHAR(4), MAX(B.STA_DATE), 112) = :qryYear+ 1911
ORDER BY org_type


Reference
[1] http://blog.xuite.net/a88370.a88370/cjoushua/73695699-SQL+%E5%AD%B8%E7%BF%92%E7%AD%86%E8%A8%98-Having
[2] https://technet.microsoft.com/en-us//library/cc645611(v=sql.105).aspx
Posted by Albert Kuo at 2:08 PM 0 comments
Labels:

2015/07/29

oracle.net.ns.NetException: Got minus one from a read call

Problem
My web application environment is :

  • Database: Oracle
  • Application Server: JBoss

As I startup JBoss, it show this error message unexpectedly:
1
2
3
4
5
6
 Caused by: oracle.net.ns.NetException: Got minus one from a read call
  at oracle.net.ns.Packet.receive(Packet.java:311) ~[oracle.jdbc-11.2.0.4-java-6.jar:11.2.0.3.0]
  at oracle.net.ns.NSProtocol.connect(NSProtocol.java:300) ~[oracle.jdbc-11.2.0.4-java-6.jar:11.2.0.3.0]
  at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1140) ~[oracle.jdbc-11.2.0.4-java-6.jar:11.2.0.3.0]
  at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:340) ~[oracle.jdbc-11.2.0.4-java-6.jar:11.2.0.3.0]
  ... 124 common frames omitted


Resolution
This exception result from the number of process exceed the maximum value.

 Therefore, you need to alter the number of process in oracle :
ALTER SYSTEM
SET processes=<number> SCOPE=spfile;

 Remember to retsart Oracle.

 Reference
[1] http://oraclepath.blogspot.tw/2013/10/javasqlsqlexception-got-minus-one-from.html

Posted by Albert Kuo at 11:48 AM 0 comments
Labels: ,

2015/07/27

[Oracle to SQL Server Migration] How to convert Oracle User-Defined Function to SQL Server

Here is my oracle user-defined function:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
create or replace FUNCTION F_DBM_FIND_CODE_NM (PI_KIND_CODE IN VARCHAR2, PI_CODE_NO   IN NUMBER)
   RETURN VARCHAR2
IS
   CODE_NAME NVARCHAR2(50);
   V_KIND_CODE VARCHAR2(5) :=  PI_KIND_CODE;
   V_CODE_NO    NUMBER (10) := PI_CODE_NO;
   V_SQLSTR VARCHAR2 (200) := '';
BEGIN
   V_SQLSTR := 'SELECT CODE_NAME FROM DBM002FB WHERE KIND_CODE = :1 AND CODE_NO = :2';
   EXECUTE IMMEDIATE V_SQLSTR INTO CODE_NAME USING V_KIND_CODE, V_CODE_NO;  
   
   RETURN CODE_NAME;
END F_DBM_FIND_CODE_NM;


But the syntax has big difference in Microsoft SQL Server:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
--Transact-SQL Scalar Function Syntax
CREATE FUNCTION [ schema_name. ] function_name( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type 
    [ = default ] [ READONLY ] } 
    [ ,...n ]
  ]
)
RETURNS return_data_type
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN 
        function_body 
        RETURN scalar_expression
    END
[ ; ]

Hence, the oracle user-defined function will be translated as bellows:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
CREATE FUNCTION AP_NTA.F_DBM_FIND_CODE_NM 
    (
      @PI_KIND_CODE VARCHAR(5),
      @PI_CODE_NO NUMERIC(10,0)
    )
RETURNS NVARCHAR(50)
BEGIN
RETURN 
(
    SELECT CODE_NAME FROM DBM002FB WHERE KIND_CODE = @PI_KIND_CODE AND CODE_NO = @PI_CODE_NO
)
END
;

Test function in Microsoft SQL Server:


select AP_NTA.F_DBM_FIND_CODE_NM('IK', 47) as codeName;






















Reference

[1] http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5009.htm

[2] https://msdn.microsoft.com/en-us/library/ms186755.aspx








Posted by
Albert Kuo


at
6:22 PM


0
comments



















Labels:
,













          

        

          

        

2015/07/26


          

        







2015/07 Kyoto










茶山站

DSC07956




清水寺

DSC08000




DSC08016




恵文社一乗寺店

DSC08111




京都文化博物館

DSC08154




DSC08178




南禪寺

DSC08199




鴨川納涼床

DSC08285




DSC08264




貴船神社

DSC08312




貴船流水麵線

DSC08351




鞍馬站

DSC08375


DSC08380












Posted by
Albert Kuo


at
4:12 PM


0
comments



















Labels:



















2015/07 Osaka










心齋橋筋

DSC07814




DSC07817




Harukas 300 - 展望台

DSC07844




DSC07845




Universal Studio Japan

DSC07879




DSC07881




DSC07908




DSC07916












Posted by
Albert Kuo


at
4:03 PM


0
comments



















Labels:













          

        

          

        

2015/07/17


          

        







[MS SQL Server] How to insert an explicit value into datetime column










In Oracle, it would be wrote as bellows:






1
2
3
4
5
6
7
8


INSERT INTO AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE)
VALUES (to_date('01-1月 -14','DD-MON-RR'),
        '2',
        '397000000A',
        '高雄市政府',
        'A00000000A',
        'TEST',
        to_date('20-3月 -15','DD-MON-RR'));












In Microsoft SQL Server, it should be modified as following:






1
2
3
4
5
6
7
8


INSERT INTO AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE)
VALUES (CAST('2014-01-01' AS datetime),
        '2',
        '397000000A',
        '高雄市政府',
        'A00000000A',
        'TEST',
        CAST('2015-03-20' AS datetime));












Reference

[1] http://stackoverflow.com/questions/10262426/sql-server-cannot-insert-an-explicit-value-into-a-timestamp-column











Posted by
Albert Kuo


at
9:50 AM


0
comments



















Labels:



















[Oracle to SQL Server Migration] Data Type Mapping for Oracle and MS SQL Server














 


  

Oracle data type


  

SQL Server data type


  

Alternatives


 



  

BFILE


  

VARBINARY(MAX)


  

Yes


 



  

BLOB


  

VARBINARY(MAX)


  

Yes


 



  

CHAR([1-2000])


  

CHAR([1-2000])


  

Yes


 



  

CLOB


  

VARCHAR(MAX)


  

Yes


 



  

DATE


  

DATETIME


  

Yes


 



  

FLOAT


  

FLOAT


  

No


 



  

FLOAT([1-53])


  

FLOAT([1-53])


  

No


 



  

FLOAT([54-126])


  

FLOAT


  

No


 



  

INT


  

NUMERIC(38)


  

Yes


 



  

INTERVAL


  

DATETIME


  

Yes


 



  

LONG


  

VARCHAR(MAX)


  

Yes


 



  

LONG RAW


  

IMAGE


  

Yes


 



  

NCHAR([1-1000])


  

NCHAR([1-1000])


  

No


 



  

NCLOB


  

NVARCHAR(MAX)


  

Yes


 



  

NUMBER


  

FLOAT


  

Yes


 



  

NUMBER([1-38])


  

NUMERIC([1-38])


  

No


 



  

NUMBER([0-38],[1-38])


  

NUMERIC([0-38],[1-38])


  

Yes


 



  

NVARCHAR2([1-2000])


  

NVARCHAR([1-2000])


  

No


 



  

RAW([1-2000])


  

VARBINARY([1-2000])


  

No


 



  

REAL


  

FLOAT


  

No


 



  

ROWID


  

CHAR(18)


  

No


 



  

TIMESTAMP


  

DATETIME


  

Yes


 



  

TIMESTAMP(0-7)


  

DATETIME


  

Yes


 



  

TIMESTAMP(8-9)


  

DATETIME


  

Yes


 



  

TIMESTAMP(0-7) WITH TIME ZONE


  

VARCHAR(37)


  

Yes


 



  

TIMESTAMP(8-9) WITH TIME ZONE


  

VARCHAR(37)


  

No


 



  

TIMESTAMP(0-7) WITH LOCAL TIME ZONE


  

VARCHAR(37)


  

Yes


 



  

TIMESTAMP(8-9) WITH LOCAL TIME ZONE


  

VARCHAR(37)


  

No


 



  

UROWID


  

CHAR(18)


  

No


 



  

VARCHAR2([1-4000])


  

VARCHAR([1-4000])


  

Yes


 





The DDL script is for Oracle database:






 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48


CREATE TABLE "AP_NTA"."DBM030FA" 
   ( "STA_DATE" DATE, 
 "ORG_TYPE" VARCHAR2(1 BYTE), 
 "AGE" VARCHAR2(12 BYTE), 
 "AGE_NAME" NVARCHAR2(30), 
 "MASTER_AGE" VARCHAR2(12 BYTE), 
 "USER_ID" VARCHAR2(20 BYTE), 
 "UPDATE_DATE" DATE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DBM_DAT" ;
REM INSERTING into AP_NTA.DBM030FA
SET DEFINE OFF;
Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (to_date('01-1月 -14','DD-MON-RR'),'2','397000000A','高雄市政府','A00000000A','TEST',to_date('20-3月 -15','DD-MON-RR'));
Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (to_date('01-1月 -14','DD-MON-RR'),'1','A00000000A','行政院',null,'TEST',to_date('20-3月 -15','DD-MON-RR'));
Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (to_date('01-1月 -14','DD-MON-RR'),'2','382000000A','新北市政府','A00000000A','TEST',to_date('16-4月 -15','DD-MON-RR'));
Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (to_date('01-1月 -14','DD-MON-RR'),'3','376580000A','新竹市政府','A01000000A ','TEST',to_date('16-4月 -15','DD-MON-RR'));
Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (to_date('01-1月 -14','DD-MON-RR'),'1','A01000000A ','內政部','A00000000A','TEST',to_date('16-4月 -15','DD-MON-RR'));
Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (to_date('01-1月 -14','DD-MON-RR'),'4','376446400A ','竹北市公所','376440000A','TEST',to_date('16-4月 -15','DD-MON-RR'));
Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (to_date('01-1月 -14','DD-MON-RR'),'3','376440000A','新竹縣政府','A01000000A ','TEST',to_date('16-4月 -15','DD-MON-RR'));
--------------------------------------------------------
--  DDL for Index SYS_C0072659
--------------------------------------------------------

  CREATE UNIQUE INDEX "AP_NTA"."SYS_C0072659" ON "AP_NTA"."DBM030FA" ("STA_DATE", "ORG_TYPE", "AGE") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DBM_DAT" ;
--------------------------------------------------------
--  Constraints for Table DBM030FA
--------------------------------------------------------

  ALTER TABLE "AP_NTA"."DBM030FA" ADD PRIMARY KEY ("STA_DATE", "ORG_TYPE", "AGE")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DBM_DAT"  ENABLE;
  ALTER TABLE "AP_NTA"."DBM030FA" MODIFY ("AGE_NAME" NOT NULL ENABLE);
  ALTER TABLE "AP_NTA"."DBM030FA" MODIFY ("AGE" NOT NULL ENABLE);
  ALTER TABLE "AP_NTA"."DBM030FA" MODIFY ("ORG_TYPE" NOT NULL ENABLE);
  ALTER TABLE "AP_NTA"."DBM030FA" MODIFY ("STA_DATE" NOT NULL ENABLE);












The following DDL script had been translated for Microsoft SQL Sever database:






 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19


  CREATE TABLE "AP_NTA"."DBM030FA" 
   ( "STA_DATE" DATETIME, 
 "ORG_TYPE" VARCHAR(1), 
 "AGE" VARCHAR(12), 
 "AGE_NAME" NVARCHAR(30), 
 "MASTER_AGE" VARCHAR(12), 
 "USER_ID" VARCHAR(20), 
 "UPDATE_DATE" DATETIME,
 CONSTRAINT DBM030FA_PK PRIMARY KEY (STA_DATE, ORG_TYPE, AGE)
   ) ;
   

Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (CAST('2014-01-01' AS datetime),'2','397000000A','高雄市政府','A00000000A','TEST',CAST('2015-07-17' AS datetime));
Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (CAST('2014-01-01' AS datetime),'1','A00000000A','行政院',null,'TEST',CAST('2014-07-17' AS datetime));
Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (CAST('2014-01-01' AS datetime),'2','382000000A','新北市政府','A00000000A','TEST',CAST('2014-07-17' AS datetime));
Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (CAST('2014-01-01' AS datetime),'3','376580000A','新竹市政府','A01000000A ','TEST',CAST('2014-07-17' AS datetime));
Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (CAST('2014-01-01' AS datetime),'1','A01000000A ','內政部','A00000000A','TEST',CAST('2014-07-17' AS datetime));
Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (CAST('2014-01-01' AS datetime),'4','376446400A ','竹北市公所','376440000A','TEST',CAST('2014-07-17' AS datetime));
Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (CAST('2014-01-01' AS datetime),'3','376440000A','新竹縣政府','A01000000A ','TEST',CAST('2014-07-17' AS datetime));








Reference

[1] https://msdn.microsoft.com/en-us/library/ms151817.aspx








Posted by
Albert Kuo


at
9:36 AM


0
comments



















Labels:
,













        

      









Subscribe to:
Posts (Atom)