William Jiang

JavaScript,PHP,Node,Perl,LAMP Web Developer – http://williamjxj.com; https://github.com/williamjxj?tab=repositories

sybase.sql

-- William Jiang's Demo for T-SQL (MS-SQL Server and Syase ASE 11)
-- 1. PROC proc_insert_PICKUP
if exists (
	select * from sysobjects
	where name='proc_insert_PICKUP' and type='P'
	)
begin
	drop procedure proc_insert_PICKUP
end
go

create proc proc_insert_PICKUP (
	@shipment_details_id char(20),
	@shipment_status_id	char(20),
	@location_address_id char(20),
	@time_stamp			datetime,
	@comment			varchar(255),
	@city_name		char(30),
	@country_code	char(4),
	@code			char(4)
)
as

if @shipment_details_id is null
or @shipment_status_id is null
or @location_address_id is null
or @time_stamp is null
or @comment is null
or @city_name is null
or @country_code is null
or @code is null
begin
	print "Usage: proc_insert_PICKUP need 8 input parameters."
	return
end

declare @err int, @rows int

begin transaction

	insert into SHIPMENT_DETAILS (
		SHIPMENT_DETAILS_ID,
		SHIPMENT_STATUS_ID,
		SHIPMENT_STATUS_CODE,
		STATUS_REASON_CODE,
		LOCATION_ADDRESS_ID,
		TIME_STAMP,
		COMMENT,
		DATE_CREATE
	)
	values (
		@shipment_details_id,
		@shipment_status_id,
		@code,
		'WTK',
		@location_address_id,
		@time_stamp,
		@comment,
		getdate()
		)

	select @err = @@error, @rows = @@rowcount
	if @err != 0
	begin
		-- rollback changes to begin tran.
		rollback transaction

		print "Failure: shipment_details_id=%1!, shipment_status_id=%2!, location_address_id=%3!, time_stamp=%4!, comment=%5!",
		@shipment_details_id, @shipment_status_id, @location_address_id, @time_stamp, @comment

		return
	end

	-- no rows found to insert.
	if @rows = 0
	begin
		-- rollback to end the transaction.
		rollback transaction
		print "0 rows is influenced in Shd."
		return
	end

	-- cascade insert to Adr.
	insert into ADDRESS (ADDRESS_ID, CITY_NAME, COUNTRY_CODE)
	values ( 
		@location_address_id, 
		@city_name, 
		@country_code
		)

	select @err = @@error, @rows = @@rowcount

	if @err != 0
	begin
		rollback transaction
		print "location_address_id=%1!, city_name=%2!, country_code=%3!", @location_address_id, @city_name, @country_code
		return
	end

	if @rows != 1
	begin
		rollback transaction
		print "not 1 row was influenced in Adr."
		return
	end

	if @code = '08'
	begin
		update SHIPMENT_STATUS
		set 
			SHIPMENT_STATUS = "Pick Up",
			ACTUAL_PICKUPDATE = @time_stamp,
			DATE_UPDATE = getdate()
		where SHIPMENT_STATUS_ID = @shipment_status_id
	end
	else if @code = '10'
	begin
		update SHIPMENT_STATUS
		set 
			SHIPMENT_STATUS = 'In Transit',
			ACTUAL_UPLIFTDATE = @time_stamp,
			DATE_UPDATE = getdate()
		where SHIPMENT_STATUS_ID = @shipment_status_id
	end
	else
	begin
		update SHIPMENT_STATUS
		set 
			SHIPMENT_STATUS = 'Accepted',
			DATE_UPDATE = getdate()
		where SHIPMENT_STATUS_ID = @shipment_status_id
	end

	select @err = @@error, @rows = @@rowcount

	if @err != 0
	begin
		rollback transaction
		print "shipment_status_id=%1!", @shipment_status_id
		return
	end

	if @rows != 1
	begin
		rollback transaction
		print "Not 1 row was influenced in Shs."
		return
	end

commit transaction
return

go


-- 2. Create PROC proc_insert_POD
/*
 * Used by SHIPMENT_DETAILS, SHIPMENT_ADDRESS,
 * SHIPMENT_UNCLASSIFIED, SHIPMENT_STATUS for POD.
 */
if exists (
	select * from sysobjects
	where name='proc_insert_POD' and type='P'
	)
begin
	drop procedure proc_insert_POD
end
go

create proc proc_insert_POD (
	@shipment_details_id 	char(20),
	@shipment_status_id	char(20),
	@time_stamp		datetime,
	@comment		varchar(255),
	@location_address_id 	char(20),
	@city_name		char(30),
	@country_code		char(4),
	@shipment_unclassified_id char(20),
	@except_col_9		varchar(50),
	@except_col_10		varchar(50),
	@signature_of_delivery char(30)
)
as

if @shipment_details_id			is null
or @shipment_status_id			is null
or @time_stamp 				is null
or @comment				is null
or @location_address_id			is null
or @city_name 				is null
or @country_code 			is null
or @shipment_unclassified_id		is null
or @except_col_9			is null
or @except_col_10			is null
or @signature_of_delivery	is null
begin
	print "proc_insert_POD needs 11 parameters."
	return
end

declare @err int, @rows int

begin transaction

	-- Shd
	insert SHIPMENT_DETAILS (
		SHIPMENT_DETAILS_ID,
		SHIPMENT_STATUS_ID,
		SHIPMENT_STATUS_CODE,
		STATUS_REASON_CODE,
		TIME_STAMP,
		COMMENT,
		LOCATION_ADDRESS_ID,
		SHIPMENT_UNCLASSIFIED_ID
		)
	values(
		@shipment_details_id,
		@shipment_status_id,
		'20',
		'WTK',
		@time_stamp,
		@comment,
		@location_address_id,
		@shipment_unclassified_id
		)

	select @err = @@error, @rows = @@rowcount

	if @err != 0
	begin
		rollback transaction
		
		print "Failure: shipment_details_id=%1!, shipment_status_id=%2!, location_address_id=%3!, time_stamp=%4!, comment=%5!, shipment_unclassified_id=%6!", @shipment_details_id, @shipment_status_id, @location_address_id, @time_stamp, @comment, @shipment_unclassified_id

		return
	end

	if @rows = 0
	begin
		rollback transaction

		print "0 row was influenced in Shd."

		return
	end

	-- Adr
	insert into ADDRESS (
		ADDRESS_ID, 
		CITY_NAME, 
		COUNTRY_CODE
		)
	values (
		@location_address_id, 
		@city_name, 
		@country_code
		)

	select @err = @@error, @rows = @@rowcount

	if @err != 0
	begin
		rollback transaction

		print "location_address_id=%1!, city_name=%2!, country_code=%3!", @location_address_id, @city_name, @country_code

		return
	end

	if @rows != 1
	begin
		rollback transaction
		print "not 1 row was inserted in Adr."
		return
	end

	-- Shu
	insert into SHIPMENT_UNCLASSIFIED(
		SHIPMENT_UNCLASSIFIED_ID,
		REMARK_COL_9,
		EXCEPT_COL_9,
		REMARK_COL_10,
		EXCEPT_COL_10
		)
	values(
		@shipment_unclassified_id,
		'DELIVERY_LOC_DESC',
		@except_col_9,
		'STATUS_EXCEPTION',
		@except_col_10
		)

	select @err = @@error, @rows = @@rowcount

	if @err != 0
	begin
		rollback transaction

		print "shipment_unclassified_id=%1!, except_col_9=%2!, except_col_10=%3!", @shipment_unclassified_id, @except_col_9, @except_col_10

		return
	end

	if @rows = 0
	begin
		rollback transaction
		print "0 row was inserted in Shu."
		return
	end

	update SHIPMENT_STATUS
	set 
		SIGNATURE_OF_DELIVERY = @signature_of_delivery,
		DATE_UPDATE = getdate(),		-- william adds
		SHIPMENT_STATUS = "Delivered",
		POD_DATE = @time_stamp
	where SHIPMENT_STATUS_ID = @shipment_status_id

	select @err = @@error, @rows = @@rowcount
	if @err != 0
	begin
		rollback transaction
		print "signaure=%1!, shipmentid=%2!", @signature_of_delivery, @shipment_status_id
		return
	end

	if @rows != 1
    	begin
		rollback transaction
		print "signaure=%1!, shipmentid=%2!", @signature_of_delivery, 
			@shipment_status_id
	end

commit transaction
go


-- 3. Create PROC proc_update_insert_Adr_by_Shs
/*
 * Used for SHIPMENT_STATUS and ADDRESS table.
 */
if exists (
	select * from sysobjects
	where name='proc_update_insert_Adr_by_Shs' and type='P'
	)
begin
	drop procedure proc_update_insert_Adr_by_Shs
end
go

create proc proc_update_insert_Adr_by_Shs (
	@shipment_status_id char(20),
	@city		char(30),
	@country	char(4),
	@address_id 	char(20)
)
as

if @shipment_status_id is null
or @city is null
or @country is null
/*
 * Here @address_id could be null
 */
begin
	print "proc_update_insert_Adr_by_Shs needs 4 parameters."
	return
end

declare @err int, @row int, @consigneeid char(20)

begin tran

	select @consigneeid = CONSIGNEE_ADDRESS_ID 
	from   SHIPMENT_STATUS
	where  SHIPMENT_STATUS_ID = @shipment_status_id

	if @consigneeid is null
	begin
		update SHIPMENT_STATUS
		set CONSIGNEE_ADDRESS_ID = @address_id
		where SHIPMENT_STATUS_ID = @shipment_status_id

		select @err = @@error, @row = @@rowcount
		if @err != 0
		begin
			rollback tran
			print "consignee_address_id=%1!, shipment_status_id=%2!", @address_id, @shipment_status_id
			return
		end

		if @row = 0
		begin
			rollback tran
			print "no suitable record"
			return
		end

		insert ADDRESS (ADDRESS_ID, CITY_NAME, COUNTRY_CODE)
		values (@address_id, @city, @country)

		select @err = @@error, @row = @@rowcount
		if @err != 0
		begin
			rollback tran
			print "address_id=%1!, city=%2!, country=%3!", @address_id, @city, @country
			return
		end

		if @row != 1
		begin
			rollback tran
			print "no record insert"
			return
		end
	end
	else
	begin
/*
 * Shs.CONSIGNEE_ADDRESS_ID exists, but Adr.ADDRESS_ID not exists.
 * Does this case exists ???
 */
		if not exists (select * from ADDRESS where ADDRESS_ID = @consigneeid)
		begin
			insert ADDRESS (ADDRESS_ID, CITY_NAME, COUNTRY_CODE) 
			values (@consigneeid, @city, @country)

			select @err = @@error, @row = @@rowcount
			if @err != 0
			begin
				rollback tran
				print "consigneeid=%1!, city=%2!, country=%3!", @consigneeid, @city, @country
				return
			end

			if @row != 1
			begin
				rollback tran
				print "no record insert"
				return
			end

		end	
		else
		begin
			update ADDRESS
			set 
				CITY_NAME = @city,
				COUNTRY_CODE = @country
			where ADDRESS_ID = @consigneeid

			select @err = @@error, @row = @@rowcount
			if @err != 0
			begin
				rollback tran

				print "city='%1!', country='%2!'", @city, @country
				
				return
			end

			if @row = 0
			begin
				rollback tran
				print "no record update"
				return
			end
		end
	end

commit tran
go

/**
 * William Jiang's Demos of T-SQL: Store Procedure, Cursor, View, etc. This is a batch process of T-SQL operations.
-- alter table by adding additional fields.
--
alter table SHIPMENT_STATUS
add PIECE_COUNT char(20) null
add FLIGHT_NO char(20) null

alter table ORDERS
add PO char(20) null

-- add  record in ACCOUNT table.
--
insert into ACCOUNT(
	ACCOUNT_ID, COMPANY_NAME, KEY_REF_1, KEY_REF_2,
	SHORT_CODE, STATUS, SUBSYSTEM_CODE, DATE_CREATE)
values(
	'S1012298766234810100', 'Table1','Table1','Table2',                     
	'TIP','ACTIVE','GOE',getdate())
*/


/**
 *
 * Firstly I must validate the input parameters are correct:
 * ACCOUNT = 'Table1'	-- Act::KEY_REF_1
 * ENTITY  = 'Table2'	-- Act::KEY_REF_2
 *
 */
if exists (select name from sysobjects where name='proc_tracking_validation' and type='P')
begin
	drop proc proc_tracking_validation
end
go

create proc proc_tracking_validation(@account char(30), @entity	char(30))
as
if @account is null or @entity is null
begin
	print "Usage: proc_tracking_validation need account and entity as input."
	return
end

/*
-- select ACCOUNT_ID according to user's input.
-- notice: maybe multi ACCOUNT_ID, such as INTEL.
select	ACCOUNT_ID, SHORT_CODE		-- is SHORT_CODE useful
from		ACCOUNT
where	KEY_REF_1 = 'Table1'		--ACCOUNT
and		KEY_REF_2 = 'Table2'	--ENTITY
and		SUBSYSTEM_CODE = 'GOE'
*/

declare @row int
select ACCOUNT_ID from ACCOUNT where KEY_REF_1=@account and KEY_REF_2=@entity
select @row=@@rowcount
if @row = 1
	return 1
else
	return 0
go


/*
 * PO No		-- Ord.PO
 * DN No		-- Ord.YOUR_REF
 * Tracking No	-- Shs.SHIPMENT_REF
 * Master AWB	-- Shs.MASTER_AWB
 * Status		-- 
 * Piece Count	-- Shs.PIECE_COUNT
 * Flight No	-- Shs.FLIGHT_NO
 * Despathed Date	-- Shs.SHIP_DATE
 *
 */

--1 Summary
--
if exists(select name from sysobjects where name='vw_tracking_summary' and type='V')
begin
	drop view vw_tracking_summary
end
go

create view vw_tracking_summary
as
select	distinct o.PO, o.YOUR_REF as DN, s.SHIPMENT_REF as CRN, 
		s.MASTER_AWB as MAWB, s.PIECE_COUNT, s.FLIGHT_NO, 
		s.SHIPMENT_STATUS
from	ORDERS o, SHIPMENT_STATUS s
where	o.ACCOUNT_ID = s.ACCOUNT_ID
and		o.YOUR_REF	= s.SHIPPER_REF
and		o.OUR_REF	= s.REMARKS

go

--2 Detail
--
/*
 * DN No	-- Shs.SHIPPER_REF, -- Ord.YOUR_REF
 * PO No	-- Shs.PO
 * Tracking No -- Shs.SHIPMENT_REF
 *
 * Pod Information
 * ---------------
 * Delivered To -- Adr.
 * Delivery Location --
 * Delivery Date/Delivery Time -- Shd.TIME_STAMP
 * Signed For By -- Shs.SIGNATURE_OF_DELIVERY
 *
 *
 * PickUp Information
 * ------------------
 * Location		--
 * Date & Time	-- Shd.TIME_STAMP
 * Remarks		-- Shd.COMMENT
 *
 */

-- POD
--
if exists (select name from sysobjects where name='vw_tracking_pod' and type='V')
begin
	drop view vw_tracking_pod
end
go

--In Transit
--
if exists (select name from sysobjects where name='vw_tracking_inTransit' and type='V')
begin
	drop view vw_tracking_inTransit
end
go

create view vw_tracking_inTransit
as
select	a.CITY_NAME, a.COUNTRY_CODE, d.TIME_STAMP, s.SHIPMENT_REF, d.COMMENT
from	SHIPMENT_STATUS s, SHIPMENT_DETAILS d, ADDRESS a
where	s.SHIPMENT_STATUS_ID = d.SHIPMENT_STATUS_ID
and		d.LOCATION_ADDRESS_ID = a.ADDRESS_ID
and		d.SHIPMENT_STATUS_CODE = '10'
and		s.STATUS = 'ACTIVE'
--An ORDER BY clause is not allowed in a view. 
--order by	s.TIME_STAMP desc -- to find out max TIME_STAMP since database is confuse.
go



--PICKUP
--
if exists (select name from sysobjects where name='vw_tracking_pickup' and type='V')
begin
	drop view vw_tracking_pickup
end
go

create view vw_tracking_pickup
as
select	a.CITY_NAME, a.COUNTRY_CODE, d.TIME_STAMP, s.SHIPMENT_REF, 
		d.COMMENT, d.SHIPMENT_DETAILS_ID
from		SHIPMENT_STATUS s, SHIPMENT_DETAILS d, ADDRESS a
where	s.SHIPMENT_STATUS_ID = d.SHIPMENT_STATUS_ID
and		d.LOCATION_ADDRESS_ID = a.ADDRESS_ID
and		d.SHIPMENT_STATUS_CODE = '08'
and		s.STATUS = 'ACTIVE'
go


--PICKPOD
--
if exists (select name from sysobjects where name='vw_tracking_pickpod' and type='V')
begin
	drop view vw_tracking_pickpod
end
go

create view vw_tracking_pickpod
as
select	a.CITY_NAME, a.COUNTRY_CODE, d.TIME_STAMP, s.SHIPMENT_REF, d.COMMENT
from	SHIPMENT_STATUS s, SHIPMENT_DETAILS d, ADDRESS a
where	s.SHIPMENT_STATUS_ID = d.SHIPMENT_STATUS_ID
and		d.LOCATION_ADDRESS_ID = a.ADDRESS_ID
and		d.SHIPMENT_STATUS_CODE = '20'
and		s.STATUS = 'ACTIVE'

go


/**
 * update SHIPMENT_STATUS.FLIGHT_NO from ECIV
 */
if exists (select name from sysobjects where name='proc_update_Flight_No' and type='P')
begin
	drop	procedure proc_update_Flight_No
end
go

create procedure proc_update_Flight_No(
	@master_awb	varchar(40), @short_code char(5), @flight_no	char(20))
as
if @master_awb is null or @short_code is null or @flight_no is null
begin
	print "proc_update_Flight_No need 3 parameters."
	return
end

declare @err int, @row int, @account_id char(20)
select @account_id = ACCOUNT_ID from ACCOUNT where SHORT_CODE=@short_code

select @row=@@rowcount
if @row = 0
	return

begin transaction
	update SHIPMENT_STATUS
	set	FLIGHT_NO = @flight_no
	where ACCOUNT_ID = @account_id
	and	MASTER_AWB = @master_awb

select @err = @@error, @row=@@rowcount
if @err != 0
begin
	rollback transaction
	print "update SHIPMENT_STATUS:FLIGHT_NO failed"
	return
end

if @row = 0
begin
          rollback tran
          print "no SHIPMENT_STATUS:FLIGHT_NO record update"
          return
end

commit transaction
go

/*
 * delete SHIPMENT_STATUS duplicate records.
 */
if exists (
            select name from sysobjects
            where name='proc_delete_duplicate_CRN' and type='P'
        )
begin
    drop procedure proc_delete_duplicate_CRN
end
go

create procedure proc_delete_duplicate_CRN ( @CRN char(40), @SHORT_CODE char(5) )
as

if @CRN is null or @SHORT_CODE is null
begin
    print "Usage: proc_delete_duplicate_CRN need Carton Number parameter"
    return 0
end

declare @err int, @row int
declare @shipment_status_id char(20), @consignee_address_id char(20)

-- 1. get SHIPMENT_STATUS_ID
select	SHIPMENT_STATUS_ID=@shipment_status_id, 
		CONSIGNEE_ADDRESS_ID=@consignee_address_id
from	SHIPMENT_STATUS, ACCOUNT
where	SHIPMENT_REF = @CRN
and		SHIPMENT_STATUS.ACCOUNT_ID = ACCOUNT.ACCOUNT_ID
and		ACCOUNT.SHORT_CODE = @SHORT_CODE

select @row = @@rowcount
if @row < 1
begin
	print "No this <%1!> in the SHIPMENT_STATUS table.", @CRN
	return 0
end

-- put all modification in a single transaction
begin transaction

    -- 2. firstly delete relative table against SHIPMENT_STATUS
    delete from SHIPMENT_DETAILS
    where SHIPMENT_STATUS_ID = @shipment_status_id

    select @err = @@error
    if @err != 0
    begin
        rollback transaction
        print "Failure this <%1!> while delete relative record in SHIPMENT_DETAILS", @CRN
        return 0
    end

    delete from ADDRESS
    where	ADDRESS_ID = @consignee_address_id

    select @err = @@error
    if @err != 0
    begin
        rollback transaction
        print "Failure this <%1!> while delete relative record in ADDRESS", @CRN
        return 0
    end

    -- 3. secondly, delete SHIPMENT_STATUS itself
    delete from SHIPMENT_STATUS
    where	SHIPMENT_REF = @CRN

    select @err = @@error
    if @err != 0
    begin
        rollback transaction
        print "Failure this <%1!> while delete record in SHIPMENT_STATUS", @CRN
        return 0
    end

commit transaction
return 1
go


if exists (select name from sysobjects where name='vw_pickup' and type='V')
begin
	drop view vw_pickup
end
go

create view vw_pickup
as
select TIME_STAMP, SHIPMENT_STATUS_ID from SHIPMENT_DETAILS
where SHIPMENT_STATUS_CODE = '08'
go

if exists (select name from sysobjects where name='vw_intrans' and type='V')
begin
	drop view vw_intrans
end
go

if exists( select name from sysobjects where name='proc_delete_all_CRN'  and type='P')
begin
	drop proc proc_delete_all_CRN
end
go

create proc proc_delete_all_CRN
as
begin
declare delete_cursor cursor
for
select	SHIPMENT_REF 
from		SHIPMENT_STATUS 
where	SHIPMENT_REF not in (select ORITEM_8 from Test..ORDETAIL) 
for read only

declare @crn char(40)

open delete_cursor

fetch delete_cursor into @crn

while @@sqlstatus != 2
begin
	if(@@sqlstatus = 1)
	begin
		print "Error"
		return
	end

	--print "%1!", @crn
	--select @crn
	execute proc_delete_duplicate_CRN  @crn,"TIP"

	fetch delete_cursor into @crn
end

close delete_cursor
deallocate cursor delete_cursor
end
go


use ETT
go

set nocount on

if exists( select name from tempdb..sysobjects where name="shs")
begin
	drop table tempdb..shs
end
go

select SHIPMENT_STATUS_ID as SID, SHIPMENT_REF as CRN into tempdb..shs
from SHIPMENT_STATUS
go

create index shs_index on tempdb..shs(SID)
go

declare query_cursor cursor
for
select SID, CRN from tempdb..shs
for read only
go

declare @crn char(40), @sid char(20)
declare @total_row int

open query_cursor

fetch query_cursor into @sid, @crn

while @@sqlstatus != 2
begin
	if(@@sqlstatus = 1)
	begin
		print "Error"
		return
	end
	
	declare @row int

	select @row = count(*)  from SHIPMENT_DETAILS 	where SHIPMENT_STATUS_ID = @sid
	if (@row > 12)
	begin
		print "sid %1! (crn %2!) has more than 12 records!(%3!)", @sid, @crn, @row
	end

	select @row=count(*) from SHIPMENT_DETAILS 
	where SHIPMENT_STATUS_CODE = '20'
	and	SHIPMENT_STATUS_ID = @sid
	if(@row > 1)
	begin
		print "sid %1! (crn %2!) has more than 1 POD records!(%3!)", @sid, @crn, @row
	end

	fetch query_cursor into @sid, @crn
	select @total_row = @@rowcount
end

print "Total %1! records have been queryed!", @total_row

close query_cursor
deallocate cursor query_cursor

go

drop table tempdb..shs
go

One response to “sybase.sql

  1. powercashadvance.com 10/27/2011 at 8:44 am

    Most I can comment on is, I’m not sure what to express! Except obviously, for the fantastic tips which are shared with this blog. I will think of a trillion fun methods to read the content articles on this site. I do think I will ultimately take a step employing your tips on those issues I could not have been able to deal with alone. You were so clever to allow me to be one of those to learn from your valuable information. Please see how great I appreciate it.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: