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'

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.

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.

Trik mencetak ke printer yang menggunakan Port LPT to USB di Windows 7

Trik ini dilakukan untuk mengatasi permasalahan pencetakan dari sebuah printer yang menggunakan Port LPT ( Paralel ) to USB. Saat ini hampir semua komputer PC tidak menyediakan port LPT untuk koneksi ke printer,  yang tersedia biasanya hanya port USB.

Dengan demikian untuk jenis-jenis printer tertentu seperti printer dot matrix(Epson dan sejenisnya) , printer passbook , printer pencetak struk yang menggunakan port LPT tidak dapat berfungsi.

Biasanya untuk dapat terkoneksi kita memerlukan kabel konektor atau kabel LPT to USB. Namun hal itu belum dapat mengatasi permasalahan terhadap program atau aplikasi yang hanya dapat membaca Port LPT ( Paralel Port ). Untuk itu kita dapat melakukan trik-trik dibawah ini.

a.       Lakukan Setting Microsoft Loopback Adapter, dengan langkah-langkah sebagai berikut :

1.          Pilih Control Panel

2.          Kilk 2x Device Manager

3.          Pilih Add legacy hardware pada menu Action

4.          Klik tombol Next

5.          Pilih opsi Install the hardware that I manually select from a list

6.          Klikt tombol Next

7.          Pilih Network Adapters, kemudian klik tombol Next

8.          Pilih Microsoft pada List Manufacturer, kemudian Pilih Microsoft Loopback Adapter

9.          Klik tombol Next

10.      Tunggu sampai tampil window berikut ini, kemudian klik tombol Finish

b.      Lakukan Setting Network

1.          Pilih Network and Sharing Center pada menu Control Panel

2.          Klik Local Area Conection 2

3.          Klik tombol Properties, pilih Internet Protocol Version 4 (TCP/IPv4) , kemudian klik tombol Properties

4.          Pilih Use the following IP Address, isi IP  Address dan Subnet mask, kemudian klik tombol OK

5.          Klik tombol Close sampai selesai

c.       Lakukan Setting Printer ( jika printer belum terinstall )

1.          Pilih Devices and Printers dari menu utama Window

2.          Klik Add printer, kemudian pilih Add a Local printer

3.          Pilih Use an existing port, pilih Port USB yang sesuai, kemudian klik tombol Next

4.          Pilih jenis printer yang sesuai, kemudian klik tombol Next

5.          Klik tombol Next

6.          Klik tombol Finish

7.          Lakukan Sharing Printer dengan mengklik 2x pada icon printer yang diinginkan, atau mengklik kanan mouse, kemudian pilih Printer Properties

8.          Pilih tab Sharing, kemudian klik tombol Change Sharing Options

9.          Pilih Share this printer, kemudian ubah Share name menjadi printlpt atau nama lain yang gampang diingat

10.      Klik tombol OK atau Apply

d.      Jalankan perintah NET USE di command prompt (cmd)

1.          Ketik cmd pada menu Start Window

2.          Ketik perintah  NET USE LPT1: \\nama komputer\nama printer /PERSISTENT:YES

Contoh : NET USE LPT1: \\192.168.1.1\Printlpt /PERSISTENT:YES


Jika artikel ini bermanfaat untuk anda , jangan lupa beri komentar :-)

Promosi :

Anda atau Perusahaan anda tertarik belajar PowerBuilder atau Sybase??
Hubungi segera 085319158373...
Dapatkan penawaran menarik...