Skip to end of metadata
Go to start of metadata


SQL Conversion

This chapter describes how to convert SQL statements from SQL Server to ATLIBASE.

1. JOIN

ALTIBASE provides only ANSI standard JOIN operation.

SQL Server

ALTIBASE

REMARK

INNER JOIN

INNER JOIN

 

LEFT OUTER JOIN

LEFT OUTER JOIN

 

RIGHT OUTER JOIN

RIGHT OUTER JOIN

 

FULL OUTER JOIN

FULL OUTER JOIN

 

CROSS JOIN

N/A

 

2. Execution Query

The query terminating character of ALTIBASE is “;” to be. Therefore, if the user wants to execute multiple queries at once, the user must separate each query with the terminator ";".

SQL Server

ALTIBASE

REMARK

GO

;
Commit;

Default value: Autocommit

3. Temporary Table

SQL Server

ALTIBASE

REMARK

CREATE TABLE #TempProcess

CREATE TABLE TEMP1 (…) TABLESPACE volatile_tablespace_name

4. Control Statement

 

SQL Server

ALTIBASE

IF

If (Condition)
Else if (Condition)
Else
End

If condition then
Elseif condition then
Else
End

While

While (condition)
BEING
END
BREAK =>while statement escape

While condition loop
End loop
Exit when condition => Repeat loop escape when condition is satisfied

5. Identity attribute

Identity attribute does not exist in ALTIBASE. Therefore, if necessary, create and use a SEQUENCE

6. Procedure Conversion

ALTIBASE PROCEDURE creation and execution statements are different from SQL Server.
Check how to change PROCEDURE with an example.

1. Parameter Declaration

Remove the'@' sign from the SQL Server parameter declaration.

In, out, and in out are specified according to the nature of the parameter.

In the case of a function, one value is returned after execution. The data type following RETURN must be specified.

SQL Server

CREATE Procedure dbo.sp1
@nTop INT – Ignore
, @nGroupCode INT = -2
, @nObjectCode INT = -2
, @nRCLS INT=1 – 1:Set , 0:Unset
, @nLCRS INT=1 – 1:Set , 0:Unset
, @nLCLS INT=1 – 1:Set , 0:Unset
, @sStartDate VARCHAR(19) = '' – Ignore
, @sEndDate VARCHAR(19) = '' – Ignore
, @nSort INT = 0 – 0: number of detections, 1: number of attempts 2: DataSize
, @sSignatureName VARCHAR(100) = ''
As
…

ALTIBASE

create or replace procedure sp1
(
i_nTop IN INT – Ignore
, i_nGroupCode IN INT := -2
, i_nObjectCode IN INT := -2
, i_nRCLS IN INT:=1 – 1:Set , 0:Unset
, i_nLCRS IN INT:=1 – 1:Set , 0:Unset
, i_nLCLS IN INT:=1 – 1:Set , 0:Unset
, i_sStartDate IN VARCHAR(19) := '' – Ignore
, i_sEndDate IN VARCHAR(19) := '' – Ignore
, i_nSort IN INT := 0 – 0: number of detections, 1: number of attempts 2: DataSize
, i_sSignatureName IN VARCHAR(100) := ''
)
as …

2. Variable Declaration

Altibase's variable declaration is defined between AS and BEGIN.
Remove the'@' sign from the variable declaration in SQL Server.
When declaring a variable, the separator ‘,’ is replaced with ‘;’.
Even in the last variable declaration, it is always declared the last with';'.

SQL Server

create or replace procedure sp1
(
……
)
as
DECLARE @sTotalQry VARCHAR(3000)
, @sQry VARCHAR(3000)
, @sFilterQry VARCHAR(1000)
, @dStartDate DATETIME
, @dEndDate DATETIME
, @sSort VARCHAR(20)
, @sSortSub VARCHAR(90)
, @sTop VARCHAR(10)
, @sTable VARCHAR(50)
, @nSec INT
, @sBaseDate VARCHAR(23)

ALTIBASE

create or replace procedure sp1
(
……
)
as
i_sTotalQry VARCHAR(3000);
i_sQry VARCHAR(3000);
i_sFilterQry VARCHAR(1000);
i_dStartDate DATE;
i_dEndDate DATE;
i_sSort VARCHAR(20);
i_sSortSub VARCHAR(90);
i_sTop VARCHAR(10);
i_sTable VARCHAR(50);
i_nSec INT;
i_sBaseDate VARCHAR(23);

3. ASSIGNMENT

Use SET when assigning values to variables in SQL Server. Here, the SET and ‘@’ characters are removed.
Then, ‘=’ is replaced with ‘:= ’.
The separator ‘,’ is replaced with ‘;’, and the last is always declared with ‘;’.
The variable can be assigned by specifying SET as above.

SQL Server

SET @sTop = '1000'
SET @sBaseDate = CONVERT(VARCHAR, DATEADD(dd, -1, GETDATE()), 121)

ALTIBASE

i_sTop := '1000' ;
i_sBaseDate := to_char(DATEADD (SYSDATE, -1, 'DAY'), 'YYYY-MM-DD HH:MI:SS') ;
OR
Set i_sTop = '1000' ;
Set i_sBaseDate = to_char(DATEADD (SYSDATE, -1, 'DAY'), 'YYYY-MM-DD HH:MI:SS') ;

4. Control_flow – IF Statement

At the end of ALTIBASE sentences, always indicate the end with';'.
At the beginning of the IF statement, the first is marked as IF (condition) THEN.
At the end of the IF statement, END IF indicates the end of the IF statement.

SQL Server

IF (@nRCLS = 1) AND (@nLCRS = 1) AND (@nLCLS = 1) SET @sFilterQry = ''
ELSE IF (@nRCLS = 0) AND (@nLCRS = 0) AND (@nLCLS = 0) SET @sFilterQry = ''

ALTIBASE

IF (i_nRCLS = 1) AND (i_nLCRS = 1) AND (i_nLCLS = 1) THEN
i_sFilterQry := '' ;
ELSIF (i_nRCLS = 0) AND (i_nLCRS = 0) AND (i_nLCLS = 0) THEN
i_sFilterQry := '' ;
END IF;

5. SELECT Statement

At the end of ALTIBASE sentences, always indicate the end with';'.
To send the resultset of select to the client in ALTIBASE, REF CURSOR must be written in the DB object first. And ref cursor should be written as an argument to the argument of sp. In addition, when executing the query, execute it with the command called Open.
Remove the'@' character from SQL Server.

SQL Server

……
SET @sQry = 'select * from test_tbl'
EXEC(@sQry)
……

ALTIBASE

CREATE TYPESET MY_TYPE
AS
TYPE MY_CUR IS REF CURSOR;
END;
/
create or replace procedure spTMSGetEventSignatureRankVariation
(
……
, P1 OUT MY_TYPE.MY_CUR
)
As
i_sQry := 'select * from test_tbl' ;
OPEN P1 FOR i_sQry;
END;

 

6. Exception Handler

At the end of ALTIBASE sentences, always indicate the end with';'.

ALTIBASE's Exception Handler is to handle when a corresponding exception occurs.

Exception handling is always BEGIN… . END; It must be processed within the syntax.

Describes the name of a system-defined EXCEPTION or user-defined EXCEPTION, processes the statement when it encounters a corresponding exception, and finally handles the OTHERS routine if the current exception cannot be handled.

The statement for checking whether the number of applied records is 0 can be checked with the SQL%ROWCOUNT constant.
System-defined exceptions are attached at the end of the file.

SQL Server

IF @@ERROR <> 0 BEGIN
IF @@ROWCOUNT = 0 BEGIN
ROLLBACK
END
END

ALTIBASE

BEGIN
……
EXCEPTION WHEN NO_DATA_FOUND THEN
rollback;
END;

OR
IF SQL%ROWCOUNT = 0 then
ROLLBACK
END if;

 

7. SP Call

At the end of ALTIBASE sentences, always indicate the end with';'.
The SQL Server's SP call is called with exec sp_name and sp arguments are specified without parentheses, but SP_name is executed in ALTIBASE and sp arguments are specified in parentheses.
Remove the'@' character from SQL Server.

SQL Server

IF @ID IS NOT NULL BEGIN
EXEC SP_Product_Reservation Inquiry_Check Member Category,Date2

ALTIBASE

IF i_ID IS NOT NULL THEN
SP_Product_Reservation Inquiry_Member Check (i_Social Security Number,i_Division,i_Date1,i_Date2);

8. Functions

At the end of ALTIBASE sentences, always indicate the end with';'.
The usage may be different for each function, and the function name may be different.
Remove the'@' character from Mssql.

SQL Server

1. LEFT, RIGHT Functions

  • LEFT(@Social Security Number,6)
  • RIGHT(@Social Security Number,7)
    2. ISNULL(V1, 0)
    3. CASE Alliance WHEN '0' THEN B. Foreign Visitor + B. Package ELSE ISNULL (D. Alliance Visitor, 0) END
    4. Convert
    CONVERT(VARCHAR(10),,120) <= B. Departure date TO_CHAR(CASE2(i_departure date1=NULL,SYSDATE),'YYYY-MM-DD');
    5. LEN
    6. RTRIM(LTRIM())
    7. DATEADD(mi, -20, GETDATE())
    8. CEILING
    9. Other operators % (a % b )
    10. charIndex(‘aaa’, ‘aaabbbcccddd’)
    11. String connection + : (‘alti’ + ‘base’)

ALTIBASE

1. Replace with SubStr

  • SUBSTR(Social Security Number,1,6)
  • SUBSTR(Socail Security Number,8,7)
    2. NVL(V1, 0)
    3. CASE2(Alliance = '0', B. Foreign guest || B. Package, D. Combined guest=NULL,0,)
    4. Convert
  • CONVERT(VARCHAR(23), dStartDate, 121) => TO_CHAR(dStartDate, 'YYYY-MM-DD HH:MI:SS.FF3');
  • CONVERT(DATETIME, sEndDate) => TO_DATE(sEndDate, 'YYYY-MM-DD HH:MI:SS');
    5. LENGTH
    6. TRIM()
    7. DATEADD(sysdate, -20, 'MINITUE');
    8. CEIL
    9. MOD(a, b)
    10. INSTR(‘aaabbbcccddd’, ‘aaa’)
    11. || use (‘alti’ || ’base’)

9. Etc

At the end of ALTIBASE sentences, always indicate the end with';'.
Remove the'@' character from SQL Server.

SQL Server

1. UPDATE STATISTICS index_name
2. with (nolock)
3. Use of keywords such as order and level

ALTIBASE

1. Unnecessary
2. Delete
3. Prohibit the use of keywords such as order and level (Altibase reserved words)

When using the DB Link in ALTIBASE, REMOTE_TABLE(dblink name, Query) for SELECT;

Use REMOTE_EXECUTE_IMMEDIATE(dblink name, Query) for DML.

11. JOIN UPDATE

There are two methods to execute Join Update in ALTIBASE.

1. Use JOIN UPDATE

  • There must be a primary key or unique key in each table.

2. Use the update of Merge Join

  • Merge into A using ( SELECT … )
    WHEN matched then
    Update …

7. Using Cursor

Check how to convert SQL Server cursor to ALTIBASE with the example.

SQL Server

declare security_cursor cursor for
select fldID from tblDept where fldParentID=@fldID
open security_cursor
fetch next from security_cursor into @fldID
while @@fetch_status = 0
begin
exec sr_GetSubDeptID_Str @fldID, @DeptID OUTPUT
declare @sql as varchar(8000)
begin
exec(@sql)
end
fetch next from security_cursor into @fldID
end
close security_cursor
deallocate security_cursor

ALTIBASE

This is a procedure to find the total number of employees and salaries by the department.

CREATE OR REPLACE PROCEDURE ForCursor_Test
AS
BEGIN
DECLARE CURSOR dept_sum IS
SELECT b.dname, COUNT(a.empno) cnt, SUM(a.sal) salary
FROM emp a, dept b
WHERE a.deptno = b.deptno
GROUP BY b.dname;
– Execute Cursor in the FOR statement.

BEGIN
FOR emp_list IN dept_sum LOOP

println('Department name: 'emp_list.dname);
println ('Number of employees: '|| emp_list.cnt);
println('Sum of salary: 'emp_list.salary);

END LOOP;
END;
END;
/

 

EXCEPTION CODE

Exception Name

Error Code
(integer)

Error Code
(hexadecimal)

Error Section

"CURSOR_ALREADY_OPEN"

201062

31166

qpERR_ABORT_QSX_CURSOR_ALREADY_OPEN

"DUP_VAL_ON_INDEX"

201063

31167

qpERR_ABORT_QSX_DUP_VAL_ON_INDEX

"INVALID_CURSOR"

201064

31168

qpERR_ABORT_QSX_INVALID_CURSOR

"INVALID_NUMBER"

201065

31169

qpERR_ABORT_QSX_INVALID_NUMBER

"NO_DATA_FOUND"

201066

3116A

qpERR_ABORT_QSX_NO_DATA_FOUND

"PROGRAM_ERROR"

201067

3116B

qpERR_ABORT_QSX_PROGRAM_ERROR

"STORAGE_ERROR"

201068

3116C

qpERR_ABORT_QSX_STORAGE_ERROR

"TIMEOUT_ON_RESOURCE"

201069

3116D

qpERR_ABORT_QSX_TIMEOUT_ON_RESOURCE

"TOO_MANY_ROWS"

201070

3116E

qpERR_ABORT_QSX_TOO_MANY_ROWS

"VALUE_ERROR"

201071

3116F

qpERR_ABORT_QSX_VALUE_ERROR

"ZERO_DIVIDE"

201072

31170

qpERR_ABORT_QSX_ZERO_DIVIDE

"INVALID_PATH"

201237

31215

qpERR_ABORT_QSX_FILE_INVALID_PATH

"INVALID_MODE"

201235

31213

qpERR_ABORT_QSX_INVALID_FILEOPEN_MODE

"INVALID_FILEHANDLE"

201238

31216

qpERR_ABORT_QSX_FILE_INVALID_FILEHANDLE

"INVALID_OPERATION"

201239

31217

qpERR_ABORT_QSX_FILE_INVALID_OPERATION

"READ_ERROR"

201242

3121A

qpERR_ABORT_QSX_FILE_READ_ERROR

"WRITE_ERROR"

201243

3121B

qpERR_ABORT_QSX_FILE_WRITE_ERROR

"ACCESS_DENIED"

201236

31214

qpERR_ABORT_QSX_DIRECTORY_ACCESS_DENIED

"DELETE_FAILED"

201240

31218

qpERR_ABORT_QSX_FILE_DELETE_FAILED

"RENAME_FAILED"

201241

31219

qpERR_ABORT_QSX_FILE_RENAME_FAILED

  • No labels