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.

You and I – Scorpions

I lose control because of you, babe
I lose control when you look at me like this
There’s something in your eyes
That is saying tonight
I’m not a child anymore
Life has opened the door
To a new exciting life
I lose control whem I’m close to you, babe
I lose control don’t look at me like this
There’s something in your eyes
Is this love at first sight?

Like a flower that grows
Life just wants you to know
All the secrets of life
It’s all written down in your lifelines
It’s written down inside your heart
You and I just have a dream
To find our love a place, where we can hide away
You and I, we’re just made
To love each other now, forever and a day, yeah
I lose control because of you, babe
I lose control don’t look at me like this
There’s something in your eyes
That is saying tonight
I’m so curious for more
Just like never before
In my innocent life
It’s all written down in your lifelines, yeah
It’s written down inside your heart
You and I just have a dream
To find our love a place, where we can hide away
You and I, we’re just made
To love each other now, forever and a day, yeah
The time stands still
When the days of innocence
Are falling for the night
I love you, girl, I always will
I swear, I’m there for you
Till the day I die
You and I just have a dream
To find our love a place, where we can hide away
You and I, we’re just made
To love each other now, forever and a day, yeah