Cara Create Tabel Dinamis sybase dari Hasil Select

Salah satu fasilitas yang tersedia dalam database Sybase yaitu fungsi untuk mengcreate tabel dinamis. Create Tabel dinamis disini yaitu tabel tersebut dapat kita create dari hasil query. Fungsi query yang dapat digunakan yaitu Select Into

Syntax create tabel tersebut yaitu :

SELECT [*,Kolom1,kolom2....]
    INTO [nama tabel baru ]
   FROM [nama tabel1,nama tabel2... ]
 WHERE [kondisi]

Contoh :

SELECT USERID,USERNAME,BRANCHID
    INTO USERBRANCH001
 FROM T_USER
WHERE BRANCHID = '001'

Trik menggunakan Function TimeStampDiff DB2

Function TimeStampDiff DB2 berfungsi untuk menghitung jumlah selisih interval satuan waktu (detik,menit,jam,hari,minggu,bulan dan tahun) antara dua tanggal yang berbeda.

Format atau Syntax function TimeStampDiff yaitu :

>>-TIMESTAMPDIFF--(--expression--,--expression--)--------------><

TIMESTAMPDIFF({parameter interval satuan waktu}, {parameter TIMESTAMP1 } –

{parameter TIMESTAMP2 } )

Hati-hati penggunaan fungsi RTRIM dan LTRIM dalam Sybase

Dalam database Sybase kita kenal adanya functioan LTRIM dan RTRIM. Function tersebut berfungsi untuk menghilangkah karakter spasi/kosong yang ada pada suatu text.

Syntax scriptnya yaitu :
LTRIM(Text) , berfungsi untuk menghilangkan karakter kosong dari sebelah kiri text
RTRIM(Text), berfungsi untuk menghilangkan karakter kosong dari sebelah kanan text

Function SQL Mencari tanggal dari tanggal tertentu dengan fungsi dateadd pada Sybase

Function dateadd adalah sebuah function yang disediakan oleh Sybase untuk mencari suatu tanggal dari parameter tanggal tertentu, bedasarkan penambahan atau pengurangan parameter hari, bulan dan tahun.

dateadd

 

Description

Returns the date produced by adding a given number of years, quarters, hours, 
or other date parts to the specified date.

 

Syntax

dateadd(date_part, integer, date expression)

 

Parameters

date_part

is a date part or abbreviation. 
For a list of the date parts and abbreviations recognized by Adaptive Server, see “Date parts”.

numeric

is an integer expression.

date expression 

is an expression of type datetime, smalldatetime, date, time, 
or a character string in a datetime format.

 

Examples

Example 1

Displays the new publication dates when the publication dates of all the books 
in the titles table slip by 21 days:

select newpubdate = dateadd(day, 21, pubdate) from titles 

 

Example 2

Add one day to a date:

declare @a dateselect @a = "apr 12, 9999"select dateadd(dd, 1, @a)

--------------------------
Apr 13 9999

Example 3

Add five minutes to a time:

select dateadd(mi, 5, convert(time, "14:20:00"))

--------------------------
2:25PM

Example 4

Add one day to a time and the time remains the same:

declare @a timeselect @a = "14:20:00"select dateadd(dd, 1, @a)
--------------------------
2:20PM

 

Example 5

Although there are limits for each date_part, as with datetime values, 
higher values can be added resulting in the values rolling over to the next significant field:

--Add 24 hours to a datetime

select dateadd(hh, 24, "4/1/1979")
-------------------------- 
Apr  2 1979 12:00AM  

--Add 24 hours to a date
select dateadd(hh, 24, "4/1/1979")
------------------------- 
Apr  2 1979 

 

Usage

·dateadd, a date function, adds an interval to a specified date. 
For more information about date functions, see “Date functions”.

·dateadd takes three arguments: the date part, a number, and a date. 
The result is a datetime value equal to the date plus the number of date parts.

·If the date argument is a smalldatetime value, the result is also a smalldatetime. 
You can use dateadd to add seconds or milliseconds to a smalldatetime, 
but it is meaningful only if the result date returned by dateadd 
changes by at least one minute.

·Use the datetime datatype only for dates after January 1, 1753.  
datetime values must be enclosed in single or double quotes. 
Use the date datatype for dates from January 1, 0001 to 9999.  
date must be enclosed in single or double quotes.

Use char, nchar, varchar or nvarchar for earlier dates. 
Adaptive Server recognizes a wide variety of date formats. For more information, 
see “User defined datatypes” and “Datatype conversion functions”.

Adaptive Server automatically converts between character and datetime 
values when necessary 
(for example, when you compare a character value to a datetime value). 
Using the date part weekday or dw with dateadd is not logical, 
and produces spurious results. 
Use day or dd instead.

Table 2-7: date_part recognized abbreviations

Date part	Abbreviation	Values
Year	yy	1753-9999 (datetime) 1900-2079 (smalldatetime) 0001-9999 (date)
Quarter	qq	1-4
Month	mm	1-12
Week	wk	1054
Day	dd	1-7
dayofyear	dy	1-366
Weekday	dw	1-7
Hour	hh	0-23
Minute	mi	0-59
Second	ss	0-59
millisecond	ms	0-999

Function SQL mencari selisih Tanggal dalam Sybase

DATEDIFF function [Date and time]

Function

Returns the interval between two dates.

Syntax

DATEDIFF ( date-part, date-expression1, date-expression2 )

Parameters

date-part Specifies the date part in which the interval is to be measured.

For a complete listing of allowed date parts, see “Date parts”.

date-expression1 The starting date for the interval. This value is subtracted from date-expression2 to return the number of date parts between the two arguments.

date-expression2 The ending date for the interval. date-expression1 is subtracted from this value to return the number of date parts between the two arguments.

Examples

The following statement returns 1:

SELECT DATEDIFF( HOUR, '4:00AM', '5:50AM' )
FROM iq_dummy

The following statement returns 102:

SELECT DATEDIFF( MONTH, '1987/05/02', '1995/11/15' )
FROM iq_dummy

The following statement returns 0:

SELECT DATEDIFF( DAY, '00:00', '23:59' ) FROM iq_dummy

The following statement returns 4:

SELECT DATEDIFF( DAY, '1999/07/19 00:00', '1999/07/23
23:59' ) FROM iq_dummy

The following statement returns 0:

SELECT DATEDIFF( MONTH, '1999/07/19', '1999/07/23' )
FROM iq_dummy

The following statement returns 1:

SELECT DATEDIFF( MONTH, '1999/07/19', '1999/08/23' )
FROM iq_dummy

The following statement returns 4:

SELECT DATEDIFF(MCS, '2009-11-03 11:10:42.033185',
'2009-11-03 11:10:42.033189') FROM iq_dummy

The following statement returns 15:

SELECT DATEDIFF(MICROSECOND, '2009-11-10
14:57:52.722001', '2009-11-10 14:57:52.722016')
FROM iq_dummy

The following statement returns 1,500,000:

SELECT DATEDIFF(MCS, '2000/07/07/07 07:07:06.277777',
'2000/07/07/07 07:07:07.777777') FROM iq_dummy

Usage

This function calculates the number of date parts between two specified dates. The result is a signed integer value equal to (date2 - date1), in date parts.
DATEDIFF results are truncated, not rounded, when the result is not an even multiple of the date part.
When you use day as the date part, DATEDIFF returns the number of midnights between the two times specified, including the second date, but not the first. For example, the following statement returns the value 5. Midnight of the first day 2003/08/03 is not included in the result. Midnight of the second day is included, even though the time specified is before midnight.

SELECT DATEDIFF( DAY, '2003/08/03 14:00', '2003/08/08 14:00' ) FROM iq_dummy

When you use month as the date part, DATEDIFF returns the number of first-of-the-months between two dates, including the second date but not the first. For example, both of the following statements return the value 9:

SELECT DATEDIFF( MONTH, '2003/02/01', '2003/11/15' ) FROM iq_dummy;
SELECT DATEDIFF( MONTH, '2003/02/01', '2003/11/01' ) FROM iq_dummy;

The first date 2003/02/01 is a first-of-month, but is not included in the result of either query. The second date 2003/11/01 in the second query is also a first-of-month and is included in the result.
When you use week as the date part, DATEDIFF returns the number of Sundays between the two dates, including the second date but not the first. For example, in the month 2003/08, the dates of the Sundays are 03, 10, 17, 24, and 31. The following query returns the value 4:

SELECT DATEDIFF( week, '2003/08/03', '2003/08/31' ) FROM iq_dummy;

The first Sunday (2003/08/03) is not included in the result.

Merubah Kolom dalam Database DB2

Berikut ini script SQL untuk merubah kolom dalam database DB2 

Syntax : 
ALTER TABLE [nama tabel] ALTER COLUMN [nama kolom] SET DATA TYPE [tipe data] 
Syntax SQL di atas bertujuan untuk merubah tipe data suatu tabel dalam database tertentu. 

Contoh : 
Misalnya kita akan merubah sebuah kolom ALAMAT dalam tabel MASTER.PEGAWAI. Kolom tersebut akan kita ubah ukuran tipe datanya, dari VARCHAR(60) menjadi VARCHAR(120). 

ALTER TABLE MASTER.PEGAWAI ALTER COLUMN ALAMAT SET DATA TYPE VARCHAR(120)

Catatan :
Dalam database DB2, tipe data CHAR tidak bisa dilakukan.
Trik untuk melakukan perubahannya yaitu dengan cara mengcreate ulang tabel bersangkutan dengan tipe data yang baru.

Misalnya kita akan merubah tipe data JNS_PEGAWAI dalam tabel MASTER.PEGAWAI dari tipe data CHAR(1) menjadi VARCHAR(3). Caranya yaitu sebagai berikut :

1. Copy tabel yang akan kita ubah (
   MASTER.PEGAWAI)
2. Buat script create tabel baru dengan tipe data JNS_PEGAWAI VARCHAR(3)

Contoh Query dengan fungsi Exists

Berikut ini beberapa contoh query dengan fungsi Exists SELECT * FROM USERID.BLOKIR_SALDO WHERE EXISTS ( SELECT 1 FROM USERID.SAVE_MASTER B WHERE B.KD_STATUS = '1' AND USERID.BLOKIR_SALDO.KD_CAB = B.KD_CAB AND USERID.BLOKIR_SALDO.NO_REK = B.NO_REK ) select t1.name, t1.id, 'Y' as HasChild from t1 where exists ( select 1 from t2 where t2.id = t1.id) UNION select t1.name, t1.id, 'N' as HasChild from t1 where NOT exists ( select 1 from t2 where t2.id = t1.id)

Using the BCP utility to input and output data to/from Sybase

When performing database maintenance you will occasionally find the need to export data out of your database tables to an operating system for storage, or conversely import data to a table from a file. You may find yourself needing to do these tasks for data backup or for inserting data that comes from a 3rd party export. Sybase makes this process simple by the Bulk Copy Utility (BCP).

Basic BCP Usage

BCP as it’s name suggest is used for 1 purpose the bulk copy of data to and from Sybase Tables. To utilize the utility the basic command format is:

bcp DATABASE..TABLE out C:DBtemp.txt

If you wanted to perform an import you simply switch the out keyword for in.

BCP Option Flags

In addition to the command there are a few options you may also find yourself using.

• Server Flag -S You can specify the Database Server you are accessing by use of the -S flag followed by the Server. (e.g. -S SYBABC )
• User Flag -U If you are not using credentials outlined in your .ini file then you can specify the User you would like to use for authentication using the -U flag followed by the user name. (e.g. -U guest)
• Password Flag -P If no password is supplied then a prompt will occur to input it. You can use the -P flag to specify the password to use to bypass the prompt. (e.g. -P test123)
• Native Format -n To export data in native format which is not readable by looking directly at the file you can use the -n flag. This mode will make it so you don’t have to specify the column format during export.
• Char Format -c To export all data in basic char format then you can use the -c flag. This mode will also make it so you don’t have to specify the column format during export.
• Tab Format -t The default format for delimiting data columns per row is the tab character to specify a different delimiter -t can be used followed by the new delimiter.
• Row Format -r If you want to have a different delimiter then the new line character in your export you can modify it by using the -r flag followed by the new delimiter.

Basic BCP Output Example

bcp DATABASE..TABLE out C:DBtemp.txt -c -S server -U userName -P password

Basic BCP Input Example

bcp DATABASE..TABLE inC:DBtemp.txt -c -S server -U userName -P password

The Bulk Copy Utility (BCP) is a very simple and versatile tool. If you find yourself doing a lot of database management you may want to familiarize yourself with it as it can save you a lot of time, effort, and headaches.

Resources

• Sybase Online Manual – BCP Utility Guide
• MDBitz Project Laboratory
  http://mdbitz.com/2010/04/22/using-the-bcp-utility-to-input-and-output-data-tofrom-sybase/

Syntax BCP Out dengan kondisi tertentu

Perintah BCP dikenal dalam database Sybase berfungsi untuk melakukan copy data dari suatu table. Copy data tabel ini bisa keseluruhan data maupun berdasarkan kondisi query tertentu.
Misalnya saja kita akan melakukan copy data dari data Master CIF dengan kondisi Nasabah yang tahun kelahirannya diatas tahun 1990.
Syntax BCP yang dapat kita jalankan yaitu sebagai berikut :
bcp NamaDatabase.NamaTabel out NamaFile --initstring "select * from NamaDatabase.NamaTabel where kondisi " -c-Usa -Ppassword -SServerName  

Contoh :
bcp DBTEST.MASTER.CIF out CIF1990.TXT --initstring "select * from DBTEST.MASTER.CIF where YEAR(BIRDTHDY) = 1990 " -c-Usa -Ppassword -SServerTest  

Merubah Kolom di database Sybase

Dalam suatu database aplikasi yang kita buat terkadang ada beberapa kolom yang perlu diubah. Perubahan kolom tersebut bisa berupa perubahan tipe data maupun lebar kolom atau ukuran kolom. 
Syntax untuk merubah kolom dalam Sybase yaitu :
ALTER TABLE {NAMATABEL} MODIFY {NAMAKOLOM} {TIPEDATA} {UKURANKOLOM}

Contoh :
ALTER TABLE MASTER.CIF MODIFY nama_ibu VARCHAR(15) VARCHAR(60)

Merubah Nilai Default Tabel database Sybase

Untuk merubah nilai default suatu kolom tabel dalam database sybase tidak dapat menggunakan fungsi ALTER MODIFY, tetapi harus menggunakan fungsi ALTER REPLACE.
Syntaxnya yaitu :
ALTER TABLE {NAMATABEL} REPLACE {NAMAKOLOM} DEFAULT {NILAIDEFAULT}

Contoh :
ALTER TABLE MASTER.CIF REPLACE AVG_TRANS DEFAULT 0

Get multiple result rows from sybase store procedure

Sometime we need get multiple result rows  from some query in store procedure.

For security purposes, I had to change some variable, column
and table names, but here is my stored procedure:

Create Proc dbo.sp_GetMultiple (
@parm_keyField varchar(100) = null,
@parm_dbMyName char(30) = null Output,
@parm_dbState char(2) = null Output,
@parm_dbUniqueId varchar(16) = null Output,
@parm_dbField2 varchar(8) = null Output,
@parm_dbField3 varchar(8) = null Output,
@parm_dbField4 varchar(16) = null Output,
@parm_dbField5 varchar(8) = null Output,
@parm_dbPeriod varchar(8) = null Output
)
As
Begin
/**********************************************************
*Procedure Name: sp_GetMultiple
*********************************************************/
/*** declare variables ***/
declare @er integer
declare @varState varchar(2)
declare @varUniqueId varchar(16)
declare @varField2 varchar(8)
declare @varField3 varchar(8)
declare @varField4 varchar(8)
declare @varField5 varchar(16)
declare @varPeriod varchar(8)
/*** retrieve data ***/
declare resultList cursor for
SELECT LEFT(myName, 30) AS myName, State, column2, column3,
column4, column5, Period, UniqueId
FROM Table1 T1
LEFT OUTER JOIN Table2 T2 ON T1.CatId = T2.CatIdFK
WHERE T2.keyField = @parm_keyField
ORDER BY 1, 2
open resultList
if @@sqlstatus != 0 return
fetch resultList
into @parm_dbMyName, @varState, @varField2, @varField3,
@varField4, @varField5, @varPeriod, @varUniqueId

/* if cursor result set is not empty, then process each
row of information */
while (@@sqlstatus = 0)
begin
/* decrypt the data */
exec dbo.sp_DecryptVar @varState, @parm_dbState
output
exec dbo.sp_DecryptVar @varUniqueId, @parm_dbUniqueId
output
exec dbo.sp_DecryptVar @varField2, @parm_dbField2
output
exec dbo.sp_DecryptVar @varField3, @parm_dbField5
output
exec dbo.sp_DecryptVar @varField4, @parm_dbField3
output
exec dbo.sp_DecryptVar @varField5, @parm_dbField4
output
exec dbo.sp_DecryptVar @varPeriod, @parm_dbPeriod
output
/*** get next record from cursor ***/
fetch resultList into @parm_dbMyName, @varState,
@varField2, @varField3, @varField4, @varField5, @varPeriod,
@varUniqueId
end
close resultList
return
End


than... 
How do you return data to the client ?

And how does the client proces the data ?

If you have multiple selects in a procedure, you get multiple
result sets sent to the client, so the client should process more
than one result set. A way to avoid this is to insert the
data in a temp table first and then select from the temp table.

Mengatasi Error Installasi Database PostgreSQL di Window 7

Pernah mengalami error saat Installasi database PostgreSQL di Window 7 ? dengan pesan error : The 'Secondary Logon' is not running seperti gambar berikut :



Solusinya sebagai berikut :
1. Buka Control Panel
2. Pilih ( klik 2x ) Administrative Tools
3. Pilih ( klik 2x ) Services
4. Pilih 'Secondary Logon'
5. Pilih StartUp Type menjadi Automatic
6. Klik tombol Start 

Selamat mencoba...

Online database sybase

Untuk mengaktifkan database sybase yang baru direstore kita dapat melakukannya dengan cara menjalankan fungsi online database. Adapun penjelasan mengenai fungsi online database sybase dapat dilihat pada penjelasan berikut ini :

Description

Marks a database available for public use after a normal load sequence; if needed, upgrades a loaded database to the current version of Adaptive Server; brings a database online after loading a transaction log dumped with the for standby_access option.

Syntax

online database database_name [for standby_access]

Parameters

database_name

specifies the name of the database to be brought online.

for standby_access

brings the database online on the assumption that the database contains no open transactions.

Examples

Example 1

Makes the pubs2 database available for public use after a load sequence completes:

online database pubs2

Example 2

Brings the database inventory_db online. Used after loading inventory_db with a transaction-log dump obtained through dump tran...with standby_access:

online database inventory_db for standby_access

Usage

• online database brings a database online for general use after a normal database or transaction log load sequence.
  
• When load database is issued, the database’s status is set to “offline.” The offline status is set in the sysdatabases system table and remains set until online database completes.
  
• Do not issue online database until all transaction logs are loaded. The command sequence is:
  
  • load database
    
  • load transaction (there may be more than one load transaction)
    
  • online database
    
• If you execute online database against a currently online database, no processing occurs and no error messages are generated.
  
• You can only use online database...for standby_access with a transaction log that was dumped using dump transaction...with standby_access. If you use online database...for standby_access after loading a transaction log that was dumped without using dump transaction...with standby access, online database generates an error message and fails.
  
• You can use sp_helpdb to find out whether a database is currently online, online for standby access, or offline.
  

Upgrading databases

• online database initiates, if needed, the upgrade of a loaded database and transaction log dumps to make the database compatible with the current version of Adaptive Server. After the upgrade completes, the database is made available for public use. If errors occur during processing, the database remains offline.
  
• online database is required only after a database or transaction log load sequence. It is not required for new installations or upgrades. When you upgrade Adaptive Server to a new version, all databases associated with that server are automatically upgraded.
  
• online database only upgrades version 11.9 or later user databases.
  
• After you upgrade a database with online database, dump the newly upgraded database to create a dump that is consistent with the current version of Adaptive Server. You must dump the upgraded database before you can issue a dump transaction command.
  

The issue of "truncation error occurred"

The issue of "truncation error occurred"

Penggunaan opsi set arithabort numeric_truncation

We can actually ignore the scale truncation errors via 'set arithabort numeric_truncation off' command.
This also gives a better picture as why the errors were thrown and actually shows us that how many scale values we lost when implicit conversions were made ( good debugging )
Also even if the truncation error occurs, the batch sql continues to execute so might be that the job which executed the procedure was not failing but the data where this truncation is happening, was not getting displayed.
Here are some examples and comments:
Case 1 :
1> select (convert(numeric(17,6),10177.244681) * convert(numeric(35,12),947.8281139625) / 100) * convert(numeric(28,13),1.4556537149915)
2>
Truncation error occurred.
Command has been aborted.
1> set arithabort numeric_truncation off
2>
1> select (convert(numeric(17,6),10177.244681) * convert(numeric(35,12),947.8281139625) / 100) * convert(numeric(28,13),1.4556537149915)
2>
--------------------------------------------------------------------------------
140416.41325534436901151728336040
(1 row affected)
1> set arithabort numeric_truncation on ( this is ON by default thus we get errors in scale losses )
2>
1> select (convert(numeric(17,6),10177.244681) * convert(numeric(35,12),947.8281139625) / 100) * convert(numeric(28,18),1.4556537149915)
2>
140416.4132553443690115172833604056875 -- for original setting, we were loosing out 5 scale values, hence when we made the scale as 18, no truncation occurred.
Case 2
1> select (convert(numeric(17,6),10177.244681) * convert(numeric(35,12),947.828113962515) / 100) * convert(numeric(35,20),1.45565371499151111111)
2>
Truncation error occurred.
Command has been aborted.

1> set arithabort numeric_truncation off
2>
1> select (convert(numeric(17,6),10177.244681) * convert(numeric(35,12),947.828113962515) / 100) * convert(numeric(35,20),1.45565371499151111111)
2>
--------------------------------------------------------------------------------
140416.41325534766300175115789757
Lets find out how much it goes..
1> select 10177.244681*(947.828113962515/100)*1.45565371499151111111
2>
---------------------------------------------------------------
140416.4132553476630017511578975709032198096365000000000
Its going beyond 38 which is the max scale we can have on numeric and hence truncating…


Case 3 :
In truncation errors being off :

1> select (convert(numeric(17,6),10177.244681) * convert(numeric(35,12),947.8281139625) / 100) * convert(numeric(35,20),1.4556537149915)
2>
--------------------------------------------------------------------------------
140416.41325534436901151728336040
We know that this multiplication goes to 56875 as per Case 1 so adding 5 more to scale would lead to no scale loss

1> select (convert(numeric(17,6),10177.244681) * convert(numeric(35,12),947.8281139625) / 100) * convert(numeric(35,25),1.4556537149915)
2>
--------------------------------------------------------------------------------
140416.4132553443690115172833604056875

1> set arithabort numeric_truncation on
2>
1> select (convert(numeric(17,6),10177.244681) * convert(numeric(35,12),947.8281139625) / 100) * convert(numeric(35,25),1.4556537149915)
2>
--------------------------------------------------------------------------------
140416.4132553443690115172833604056875
We definetely do not need that length of precesions and can safely ignore the truncation errors..
Hope this helps.