就写个外键

来源：互联网发布：radio html js 编辑：程序博客网时间：2024/06/01 12:24

1. 在不使用聚合函数的时候，group by子句中必须包含所有的列，否则会报错

2. having子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having条件显示特定的组，也可以使用多个分组标准进行分组。

Please try to learn thedifference of the following SQLs

select top 10 * from asset_identwhere asset_id =0x000400632b44161e and id_cd <> 'BDG'

having count(id_number) > 1

select top 10 * from asset_identwhere asset_id =0x000400632b44161e and id_cd <> 'BDG'

group by asset_id,id_cd

having count(id_number) > 1

select top 10 * from asset_identwhere asset_id =0x000400632b44161e and id_cd <> 'BDG'

group byasset_id,id_cd,id_number

having count(id_number) > 1

creat table

alter reference

insert data into tables

inner join

use A table .cd B table .cd A table.cd char(2) B table.cd char(8)

select tmp_as.cd, typ_cds.cd from tmp_as innner join typ_cdson tmp_as.cd = typ_cds.cd

select * from tmp_as inner join typ_cds on tmp_as.cd = typ_cds.cd

select cd, count(*) from tmp_asgroup by cd

select cd,count(*) from tmp_asgroup by cd

order by count(*) desc

create index ,delete index

Bcp data out to files

Grant read permission to tables

create trigger

isql -U fis_dist -P fisreadw -SHGCDB1

1> use dmo_govcorp

2> go

Please find out records countwhich in different asset_info_src_cd in dmo_govcorp..asset table .

The result should be like thefollowing: ( tip: please use count(*), group by and order by )

asset_info_src_cd

----------------------------

RTR 1611637

WM 526630

ISM 441547

MPD 223134

KOS 185046

CDS 132780

BDG 80028

INF 59759

PRS 48576

PRO 45182

MKT 42835

LVA 34700

CSP 27592

RSC 21986

PAL 17757

CIB 12474

STM 9862

OKB 8200

AIA 6268

VLF 4862

IDI 4265

TRS 2725

COM 2294

VPS 2173

DTC 1905

SCR 1853

ERD 1850

AIF 1815

IDV 721

CBK 497

PUB 270

IFR 184

NULL 76

OTH 75

RRS 14

TRD 10

ANR 9

BIA 6

ELC 5

EUR 4

S&P 3

(41 rows affected)

alter table asset_ident addconstraint fk_id foreign key(asset_id) references asset(asset_id)

1> alter table asset_identmodify asset_id binary(8 ) null

2> go

Msg 10304, Level 14, State 1:

Server 'HXGCDB1', Line 1:

Only the owner of object'asset_ident' or a user with 'sa_role' role can run

this command.

1> alter table asset_identmodify asset_id null

2> go

Msg 10304, Level 14, State 1:

Server 'HXGCDB1', Line 1:

Only the owner of object'asset_ident' or a user with 'sa_role' role can run

this command.

eg:

1>selectasset_info_src_cd,count(*) from assetwheredatediff(week,asset_setup_dt,getdate())=0

2> group byasset_info_src_cd

eg:

select asset_info_src_cd,count (asset_info_src_cd) from asset wheredatepart(week,asset_setup_dt)=datepart(week,getdate()) group by asset_info_src_cd

eg: select asset_info_src_cd,count(*) from asset whereasset_setup_dt >'20150706' and asset_setup_dt <=getdate() group byasset_info_src_cd

Documents:

http://wenku.baidu.com/view/c8dbc908f12d2af90242e629.html

http://wenku.baidu.com/view/825558dc6f1aff00bed51e4b.html

运行：

http://wenku.baidu.com/view/37046d5133687e21af45a967.html?re=view

History

cd/home/users/cyu/work/src/OPS/feeds/wm_solva/scripts

mnsdev13:scripts> ls

solva_bucket.pl* solva_feed_loader.sh* solva_load_vendordb.pl* solva_parse.pl* t2.pl

solva_db_cleanup.pl* solva_fetch.pl* solva_map.pl* t.pl*

mnsdev13:scripts> ./t2.pl

ksh: ./t2.pl: cannot execute

mnsdev13:scripts> ls -al t2.pl

-rw-r--r-- 1cyu staff 48 Jul 13 21:16 t2.pl

mnsdev13:scripts> chmod +xt2.pl

mnsdev13:scripts> ls -alt2.pl

-rwxr-xr-x 1cyu

cat t2.pl

#!/usr/local/bin/perl5

print "hello world !\n"

mnsdev13:scripts> ./test.plFASTA FASTA.out

cd/home/users/cyu/work/src/OPS/feeds/wm_solva/scripts

mnsdev13:scripts> vim FASTA

cmnsdev13:scripts> cat t2.pl

#!/usr/local/bin/perl5

print "hello world !\n"

mnsdev13:scripts> vim t2^?^C

mnsdev13:scripts> vim test.pl

cmnsdev13:scripts> ls -altest.pl

-rw-r--r-- 1cyu staff 240 Jul 13 21:23 test.pl

mnsdev13:scripts> perl5 t2.pl

hello world !

mnsdev13:scripts> chmod +xtest.pl

mnsdev13:scripts> ./test.pl

ksh: ./test.pl: not found

mnsdev13:scripts> ls -al

total 247

drwxr-xr-x 3 cyu staff 14 Jul 13 21:23 ./

drwxr-xr-x 7cyu staff 9 Jul 9 01:49../

drwxr-xr-x 6cyu staff 9 Jul 9 01:49.svn/

-rw-r--r-- 1cyu staff 29 Jul 13 21:19 FASTA

-rwxr-xr-x 1cyu staff 24974Jul 9 01:49 solva_bucket.pl*

-rwxr-xr-x 1cyu staff 11415Jul 9 01:49 solva_db_cleanup.pl*

-rwxr-xr-x 1cyu staff 2400 Jul 9 01:49 solva_feed_loader.sh*

-rwxr-xr-x 1cyu staff 16035Jul 9 01:49 solva_fetch.pl*

-rwxr-xr-x 1cyu staff 21572Jul 9 01:49 solva_load_vendordb.pl*

-rwxr-xr-x 1cyu staff 16058Jul 9 01:49 solva_map.pl*

-rwxr-xr-x 1cyu staff 8589 Jul 9 01:49 solva_parse.pl*

-rwxr-xr-x 1cyu staff 43 Jul 13 21:15 t.pl*

-rwxr-xr-x 1cyu staff 48 Jul 13 21:16 t2.pl*

-rwxr-xr-x 1cyu staff 240 Jul 13 21:23 test.pl*

mnsdev13:scripts> ls test.pl

test.pl*

mnsdev13:scripts> vim test.pl

cmnsdev13:scripts> ./test.pl

Usage: perl ./test.pl<input.fa> <out.gc>

mnsdev13:scripts> ./test.plFASTA FASTA.out

Sent: Tuesday, July 14, 2015 10:27 AM

cd/home/users/cyu/work/src/OPS/feeds/wm_solva/scripts

mnsdev13:scripts> ls

solva_bucket.pl* solva_feed_loader.sh* solva_load_vendordb.pl* solva_parse.pl* t2.pl

solva_db_cleanup.pl* solva_fetch.pl* solva_map.pl* t.pl*

mnsdev13:scripts> ./t2.pl

ksh: ./t2.pl: cannot execute

mnsdev13:scripts> ls -alt2.pl

-rw-r--r-- 1cyu staff 48 Jul 13 21:16 t2.pl

mnsdev13:scripts> chmod +xt2.pl

mnsdev13:scripts> ls -alt2.pl

-rwxr-xr-x 1cyu

cat t2.pl

#!/usr/local/bin/perl5

print "hello world !\n"

mnsdev13:scripts> ./test.plFASTA FASTA.out

http://perldoc.perl.org/perlintro.html

7.22.2015

1 1 #!/usr/local/bin/perl5

2 use strict;

3 use warnings;

4 print "Hello world";

5 print "Hello, world";

6 print 'Hello, world';

2. 1 #!/usr/local/bin/perl5

2 use strict;

3 use warnings;

4 print "Hello, $name\n";

wever, only double quotes "interpolate" variables and specialcharacters such as newlines (\n):

1.   print "Hello, $name\n";     # works fine

2.   print 'Hello, $name\n';     # prints $name\n literally

3. 1 #!/usr/local/bin/perl5

      2 use strict;

      3 use warnings;

      4 print 'Hello, $name\n';

4.      1 #!/usr/local/bin/perl5

      2 use strict;

      3 use warnings;

      4 print 42;

5.    1 #!/usr/local/bin/perl5

      2 use strict;

      3 use warnings;

      4  print("Hello, world\n");

6.      1 #!/usr/local/bin/perl5

      2 use strict;

      3 use warnings;

      4  print "Hello, world\n";

7.      1 #!/usr/local/bin/perl5

      2 use strict;

      3 use warnings;

      4 my $animal = "camel";

      5 my $answer = 42;

      6 print $animal;

      7 print "The animal is $animal\n";

      8 print"The square of $answer is ",$answer * $answer, "\n";

8.      1 #!/usr/local/bin/perl5

      2 use strict;

      3 use warnings;

      4 print;

9.

10。

11.

o Whichjobs failed in hasched2 ? i.e. use fail command line

o Usejr to see the fail job of markit_xform_daily

o What’sthe error of market_xform_daily ? i.e. use analog -f market_xform_dailyto find out the error messages .

Which cycle the market_xform_daily belongs to ? i.e. usewhich_box command line to find it out

hasched2% fail

hasched2% jr markit_xform_daily

hasched2% analog -fmarkit_xform_daily

hasched2% which_boxmarkit_xform_daily

· 2> sp__helpf_action_hist

· 3> go

mnsdev13:yoliu> isql -U fis_dist -P fisreadw -S HXGCDB1

1> use dmo_govcorp

1> sp__helptext chk_relationship_hist#to_obj1

2> go

CREATE PROCEDURE dbo.chk_relationship_hist#to_obj1

@oid id_TY,

@rc int OUT,

@msg varchar(512) OUT

-- $Id: chk_relationship_hist#to_obj1.pr 57363 2013-06-06 07:21:47Zwinnie.wang

-- $Rev: 57363 $

BEGIN

DECLARE

@rid id_TY,

@is_dup int,

@is_overflow int,

@dup_cnt int,

@group_cnt int,

@value varchar(100),

@prev_value varchar(100),

@msg_part varchar(512),

@msg_tmp varchar(512)

SELECT @is_dup = 0,@dup_cnt = 0, @group_cnt = 0, @is_overflow = 0

-- Create cursor

DECLARE relation_curCURSOR FOR

SELECTr.relation_id, r.relation_type_cd +bintostr(r.from_obj_oid) +

r.from_obj_type_cd

+bintostr(r.to_obj_oid) + r.to_obj_type_cd

+isnull(convert(varchar(8), r.eff_zdt, 112),'NULL')

+isnull(convert(varchar(8), r.end_zdt, 112),'NULL') as value

FROM relationship_histr, gc_support..relation_type_cfg g

WHEREr.to_obj_oid = @oid

ANDr.relation_type_cd = g.relation_type_cd

ANDr.from_obj_type_cd = g.from_obj_type_cd

AND r.to_obj_type_cd = g.to_obj_type_cd

ANDg.obj_type_cd = 'GCRE'

ANDg.analyst_edit_fl = 'y'

ORDER BY value

OPEN relation_cur

FETCH relation_curINTO @rid, @value

IF (@@sqlstatus = 0)

BEGIN

SELECT @prev_value= @value, @group_cnt = 1

SELECT @msg_part ='0x' + bintostr(@rid)

FETCH relation_curINTO @rid, @value

WHILE (@@sqlstatus = 0 )

BEGIN

IF (@value<> @prev_value)

BEGIN

IF(@group_cnt > 1)

BEGIN

SELECT@is_dup = 1

IF(@msg_tmp IS NULL)

BEGIN

SELECT @msg_tmp = '(' + @msg_part + ')'

END

ELSE

BEGIN

SELECT @msg_tmp = @msg_tmp + ', (' + @msg_part + ')'

END

SELECT@prev_value = @value, @group_cnt = 1

SELECT@msg_part = '0x' + bintostr(@rid)

END

ELSE

BEGIN

IF(@dup_cnt > 7)

BEGIN

SELECT@is_overflow = 1

BREAK

END

IF(@group_cnt = 1)

BEGIN

SELECT@dup_cnt = @dup_cnt + 1

END

SELECT@msg_part = @msg_part + ', 0x' + bintostr(@rid)

SELECT@group_cnt = @group_cnt + 1, @dup_cnt =@dup_cnt + 1

END

FETCHrelation_cur INTO @rid, @value

END

IF (@group_cnt> 1)

BEGIN

SELECT @is_dup= 1

IF (@msg_tmpIS NULL)

BEGIN

SELECT@msg_tmp = '(' + @msg_part + ')'

END

ELSE

BEGIN

SELECT@msg_tmp = @msg_tmp + ', (' + @msg_part + ')'

END

IF (@is_overflow =1)

BEGIN

SELECT@msg_tmp = @msg_tmp + ' and more'

END

CLOSE relation_cur

DEALLOCATErelation_cur

IF (@is_dup = 1)

BEGIN

SELECT @rc = 0

SELECT @msg ="Duplicate Relationships,relation_id: " + @msg_tmp + '.'

END

ELSE

BEGIN

SELECT @rc = 1

SELECT @msg = NULL

END

(return status = 0)

select count(*) fromdmo_muni..asset t1,gcodes..asset_status_cds t2 where t1.asset_status_cd =

t2.asset_status_cd andt2.active_fl = 'y'

System procedures:

• sp_who, sp__lock,sp__helptable, sp__helpcolumn,sp__helpdb, sp__helptext, sp__helpproc ,

sp__helptriggeretc.

creat table

alter reference

insert data into tables

inner join

use A table .cd B table .cd A table.cd char(2) B table.cd char(8)

select tmp_as.cd, typ_cds.cd from tmp_as innner join typ_cdson tmp_as.cd = typ_cds.cd

select * from tmp_as inner join typ_cds on tmp_as.cd = typ_cds.cd

select cd, count(*) from tmp_asgroup by cd

select cd,count(*) from tmp_asgroup by cd

order by count(*) desc

create index ,delete index

Bcp data out to files

Grant read permission to tables

create trigger

isql -U fis_dist -P fisreadw -SHGCDB1

1> use dmo_govcorp

2> go

Please find out records countwhich in different asset_info_src_cd in dmo_govcorp..asset table .

The result should be like thefollowing: ( tip: please use count(*), group by and order by )

asset_info_src_cd

----------------------------

RTR 1611637

WM 526630

ISM 441547

MPD 223134

KOS 185046

CDS 132780

BDG 80028

INF 59759

PRS 48576

PRO 45182

MKT 42835

LVA 34700

CSP 27592

RSC 21986

PAL 17757

CIB 12474

STM 9862

OKB 8200

AIA 6268

VLF 4862

IDI 4265

TRS 2725

COM 2294

VPS 2173

DTC 1905

SCR 1853

ERD 1850

AIF 1815

IDV 721

CBK 497

PUB 270

IFR 184

NULL 76

OTH 75

RRS 14

TRD 10

ANR 9

BIA 6

ELC 5

EUR 4

S&P 3

(41 rows affected)

alter table asset_ident addconstraint fk_id foreign key(asset_id) references asset(asset_id)

1> alter table asset_identmodify asset_id binary(8 ) null

2> go

Msg 10304, Level 14, State 1:

Server 'HXGCDB1', Line 1:

Only the owner of object'asset_ident' or a user with 'sa_role' role can run

this command.

1> alter table asset_identmodify asset_id null

2> go

Msg 10304, Level 14, State 1:

Server 'HXGCDB1', Line 1:

Only the owner of object'asset_ident' or a user with 'sa_role' role can run

this command.

eg:

1>selectasset_info_src_cd,count(*) from assetwheredatediff(week,asset_setup_dt,getdate())=0

2> group byasset_info_src_cd

eg:

select asset_info_src_cd,count (asset_info_src_cd) from asset wheredatepart(week,asset_setup_dt)=datepart(week,getdate()) group by asset_info_src_cd

eg: select asset_info_src_cd,count(*) from asset whereasset_setup_dt >'20150706' and asset_setup_dt <=getdate() group byasset_info_src_cd

Documents:

http://wenku.baidu.com/view/c8dbc908f12d2af90242e629.html

http://wenku.baidu.com/view/825558dc6f1aff00bed51e4b.html

运行：

http://wenku.baidu.com/view/37046d5133687e21af45a967.html?re=view

History

cd/home/users/cyu/work/src/OPS/feeds/wm_solva/scripts

mnsdev13:scripts> ls

solva_bucket.pl* solva_feed_loader.sh* solva_load_vendordb.pl* solva_parse.pl* t2.pl

solva_db_cleanup.pl* solva_fetch.pl* solva_map.pl* t.pl*

mnsdev13:scripts> ./t2.pl

ksh: ./t2.pl: cannot execute

mnsdev13:scripts> ls -al t2.pl

-rw-r--r-- 1cyu staff 48 Jul 13 21:16 t2.pl

mnsdev13:scripts> chmod +xt2.pl

mnsdev13:scripts> ls -alt2.pl

-rwxr-xr-x 1cyu

cat t2.pl

#!/usr/local/bin/perl5

print "hello world !\n"

mnsdev13:scripts> ./test.plFASTA FASTA.out

cd/home/users/cyu/work/src/OPS/feeds/wm_solva/scripts

mnsdev13:scripts> vim FASTA

cmnsdev13:scripts> cat t2.pl

#!/usr/local/bin/perl5

print "hello world !\n"

mnsdev13:scripts> vim t2^?^C

mnsdev13:scripts> vim test.pl

cmnsdev13:scripts> ls -altest.pl

-rw-r--r-- 1cyu staff 240 Jul 13 21:23 test.pl

mnsdev13:scripts> perl5 t2.pl

hello world !

mnsdev13:scripts> chmod +xtest.pl

mnsdev13:scripts> ./test.pl

ksh: ./test.pl: not found

mnsdev13:scripts> ls -al

total 247

drwxr-xr-x 3 cyu staff 14 Jul 13 21:23 ./

drwxr-xr-x 7cyu staff 9 Jul 9 01:49../

drwxr-xr-x 6cyu staff 9 Jul 9 01:49.svn/

-rw-r--r-- 1cyu staff 29 Jul 13 21:19 FASTA

-rwxr-xr-x 1cyu staff 24974Jul 9 01:49 solva_bucket.pl*

-rwxr-xr-x 1cyu staff 11415Jul 9 01:49 solva_db_cleanup.pl*

-rwxr-xr-x 1cyu staff 2400 Jul 9 01:49 solva_feed_loader.sh*

-rwxr-xr-x 1cyu staff 16035Jul 9 01:49 solva_fetch.pl*

-rwxr-xr-x 1cyu staff 21572Jul 9 01:49 solva_load_vendordb.pl*

-rwxr-xr-x 1cyu staff 16058Jul 9 01:49 solva_map.pl*

-rwxr-xr-x 1cyu staff 8589 Jul 9 01:49 solva_parse.pl*

-rwxr-xr-x 1cyu staff 43 Jul 13 21:15 t.pl*

-rwxr-xr-x 1cyu staff 48 Jul 13 21:16 t2.pl*

-rwxr-xr-x 1cyu staff 240 Jul 13 21:23 test.pl*

mnsdev13:scripts> ls test.pl

test.pl*

mnsdev13:scripts> vim test.pl

cmnsdev13:scripts> ./test.pl

Usage: perl ./test.pl<input.fa> <out.gc>

mnsdev13:scripts> ./test.plFASTA FASTA.out

Sent: Tuesday, July 14, 2015 10:27 AM

cd/home/users/cyu/work/src/OPS/feeds/wm_solva/scripts

mnsdev13:scripts> ls

solva_bucket.pl* solva_feed_loader.sh* solva_load_vendordb.pl* solva_parse.pl* t2.pl

solva_db_cleanup.pl* solva_fetch.pl* solva_map.pl* t.pl*

mnsdev13:scripts> ./t2.pl

ksh: ./t2.pl: cannot execute

mnsdev13:scripts> ls -alt2.pl

-rw-r--r-- 1cyu staff 48 Jul 13 21:16 t2.pl

mnsdev13:scripts> chmod +xt2.pl

mnsdev13:scripts> ls -alt2.pl

-rwxr-xr-x 1cyu

cat t2.pl

#!/usr/local/bin/perl5

print "hello world !\n"

mnsdev13:scripts> ./test.plFASTA FASTA.out

http://perldoc.perl.org/perlintro.html

7.22.2015

1 1 #!/usr/local/bin/perl5

2 use strict;

3 use warnings;

4 print "Hello world";

5 print "Hello, world";

6 print 'Hello, world';

2. 1 #!/usr/local/bin/perl5

2 use strict;

3 use warnings;

4 print "Hello, $name\n";

wever, only double quotes "interpolate" variables and specialcharacters such as newlines (\n):

1.   print "Hello, $name\n";     # works fine

2.   print 'Hello, $name\n';     # prints $name\n literally

3. 1 #!/usr/local/bin/perl5

      2 use strict;

      3 use warnings;

      4 print 'Hello, $name\n';

4.      1 #!/usr/local/bin/perl5

      2 use strict;

      3 use warnings;

      4 print 42;

5.    1 #!/usr/local/bin/perl5

      2 use strict;

      3 use warnings;

      4  print("Hello, world\n");

6.      1 #!/usr/local/bin/perl5

      2 use strict;

      3 use warnings;

      4  print "Hello, world\n";

7.      1 #!/usr/local/bin/perl5

      2 use strict;

      3 use warnings;

      4 my $animal = "camel";

      5 my $answer = 42;

      6 print $animal;

      7 print "The animal is $animal\n";

      8 print"The square of $answer is ",$answer * $answer, "\n";

8.      1 #!/usr/local/bin/perl5

      2 use strict;

      3 use warnings;

      4 print;

9.

10。

11.

o Whichjobs failed in hasched2 ? i.e. use fail command line

o Usejr to see the fail job of markit_xform_daily

o What’sthe error of market_xform_daily ? i.e. use analog -f market_xform_dailyto find out the error messages .

Which cycle the market_xform_daily belongs to ? i.e. usewhich_box command line to find it out

hasched2% fail

hasched2% jr markit_xform_daily

hasched2% analog -fmarkit_xform_daily

hasched2% which_boxmarkit_xform_daily

· 2> sp__helpf_action_hist

· 3> go

mnsdev13:yoliu> isql -U fis_dist -P fisreadw -S HXGCDB1

1> use dmo_govcorp

1> sp__helptext chk_relationship_hist#to_obj1

2> go

CREATE PROCEDURE dbo.chk_relationship_hist#to_obj1

@oid id_TY,

@rc int OUT,

@msg varchar(512) OUT

-- $Id: chk_relationship_hist#to_obj1.pr 57363 2013-06-06 07:21:47Zwinnie.wang

-- $Rev: 57363 $

BEGIN

DECLARE

@rid id_TY,

@is_dup int,

@is_overflow int,

@dup_cnt int,

@group_cnt int,

@value varchar(100),

@prev_value varchar(100),

@msg_part varchar(512),

@msg_tmp varchar(512)

SELECT @is_dup = 0,@dup_cnt = 0, @group_cnt = 0, @is_overflow = 0

-- Create cursor

DECLARE relation_curCURSOR FOR

SELECTr.relation_id, r.relation_type_cd +bintostr(r.from_obj_oid) +

r.from_obj_type_cd

+bintostr(r.to_obj_oid) + r.to_obj_type_cd

+isnull(convert(varchar(8), r.eff_zdt, 112),'NULL')

+isnull(convert(varchar(8), r.end_zdt, 112),'NULL') as value

FROM relationship_histr, gc_support..relation_type_cfg g

WHEREr.to_obj_oid = @oid

ANDr.relation_type_cd = g.relation_type_cd

ANDr.from_obj_type_cd = g.from_obj_type_cd

AND r.to_obj_type_cd = g.to_obj_type_cd

ANDg.obj_type_cd = 'GCRE'

ANDg.analyst_edit_fl = 'y'

ORDER BY value

OPEN relation_cur

FETCH relation_curINTO @rid, @value

IF (@@sqlstatus = 0)

BEGIN

SELECT @prev_value= @value, @group_cnt = 1

SELECT @msg_part ='0x' + bintostr(@rid)

FETCH relation_curINTO @rid, @value

WHILE (@@sqlstatus = 0 )

BEGIN

IF (@value<> @prev_value)

BEGIN

IF(@group_cnt > 1)

BEGIN

SELECT@is_dup = 1

IF(@msg_tmp IS NULL)

BEGIN

SELECT @msg_tmp = '(' + @msg_part + ')'

END

ELSE

BEGIN

SELECT @msg_tmp = @msg_tmp + ', (' + @msg_part + ')'

END

SELECT@prev_value = @value, @group_cnt = 1

SELECT@msg_part = '0x' + bintostr(@rid)

END

ELSE

BEGIN

IF(@dup_cnt > 7)

BEGIN

SELECT@is_overflow = 1

BREAK

END

IF(@group_cnt = 1)

BEGIN

SELECT@dup_cnt = @dup_cnt + 1

END

SELECT@msg_part = @msg_part + ', 0x' + bintostr(@rid)

SELECT@group_cnt = @group_cnt + 1, @dup_cnt =@dup_cnt + 1

END

FETCHrelation_cur INTO @rid, @value

END

IF (@group_cnt> 1)

BEGIN

SELECT @is_dup= 1

IF (@msg_tmpIS NULL)

BEGIN

SELECT@msg_tmp = '(' + @msg_part + ')'

END

ELSE

BEGIN

SELECT@msg_tmp = @msg_tmp + ', (' + @msg_part + ')'

END

IF (@is_overflow =1)

BEGIN

SELECT@msg_tmp = @msg_tmp + ' and more'

END

CLOSE relation_cur

DEALLOCATErelation_cur

IF (@is_dup = 1)

BEGIN

SELECT @rc = 0

SELECT @msg ="Duplicate Relationships,relation_id: " + @msg_tmp + '.'

END

ELSE

BEGIN

SELECT @rc = 1

SELECT @msg = NULL

END

(return status = 0)

select count(*) fromdmo_muni..asset t1,gcodes..asset_status_cds t2 where t1.asset_status_cd =

t2.asset_status_cd andt2.active_fl = 'y'

System procedures:

• sp_who, sp__lock,sp__helptable, sp__helpcolumn,sp__helpdb, sp__helptext, sp__helpproc ,

sp__helptriggeretc.

  
                              
 
 
 
 
 
 
 
 
 
 
 
 
 \
 
 
creat table 
 
 
alter reference
 
 
 
 
insert data into tables
 
 
 
inner join   
 use A table .cd    B table .cd     A table.cd char(2)     B table.cd char(8)
select tmp_as.cd, typ_cds.cd from tmp_as innner join typ_cdson tmp_as.cd  =  typ_cds.cd
or
select * from tmp_as inner join typ_cds on tmp_as.cd =  typ_cds.cd
  
 
 
  
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1
select cd, count(*) from tmp_asgroup by cd
 
2
 
select cd,count(*) from tmp_asgroup by cd
order by count(*) desc
 
create   index ,delete  index
 
 
 
Bcp data out to files
 
 
 
Grant read permission to tables
 
 
create  trigger
 
 
isql -U fis_dist -P fisreadw -SHGCDB1
1> use dmo_govcorp
2> go
 
Please find out records countwhich in different asset_info_src_cd in dmo_govcorp..asset table .
The result should be like thefollowing:  ( tip: please use count(*), group by and  order by )
 
asset_info_src_cd       
 ---------------------------- 
 RTR                  1611637 
 WM                    526630 
 ISM                   441547 
 MPD                   223134 
 KOS                   185046 
 CDS                   132780 
 BDG                    80028 
 INF                    59759 
 PRS                    48576
 PRO                    45182 
 MKT                    42835 
 LVA                    34700 
 CSP                    27592 
 RSC                    21986 
 PAL                    17757 
 CIB                    12474 
 STM                     9862
 OKB                     8200 
 AIA                     6268 
 VLF                     4862 
 IDI                     4265 
 TRS                     2725 
 COM                     2294 
 VPS                     2173 
 DTC                     1905 
 SCR                     1853 
 ERD                     1850 
 AIF                     1815 
 IDV                      721 
 CBK                      497 
 PUB                      270 
 IFR                      184 
 NULL                      76 
 OTH                       75 
 RRS                       14 
 TRD                       10 
 ANR                        9 
 BIA                        6 
 ELC                        5 
 EUR                        4 
 S&P                        3 
 
(41 rows affected)
1>    
 
 
alter table asset_ident addconstraint fk_id foreign key(asset_id) references asset(asset_id)
 
 
1> alter table asset_identmodify asset_id binary(8 ) null
2> go
Msg 10304, Level 14, State 1:
Server 'HXGCDB1', Line 1:
Only the owner of object'asset_ident' or a user with 'sa_role' role can run
this command.
1> alter table asset_identmodify asset_id null
2> go
Msg 10304, Level 14, State 1:
Server 'HXGCDB1', Line 1:
Only the owner of object'asset_ident' or a user with 'sa_role' role can run
this command.
 
 
 
eg:
1>selectasset_info_src_cd,count(*) from assetwheredatediff(week,asset_setup_dt,getdate())=0 
2> group byasset_info_src_cd
 
eg:
select asset_info_src_cd,count (asset_info_src_cd)  from  asset wheredatepart(week,asset_setup_dt)=datepart(week,getdate())  group by asset_info_src_cd
 
eg:  select asset_info_src_cd,count(*) from asset whereasset_setup_dt >'20150706' and asset_setup_dt <=getdate() group byasset_info_src_cd
 
 
 
 
Documents:
 
http://wenku.baidu.com/view/c8dbc908f12d2af90242e629.html
 
http://wenku.baidu.com/view/825558dc6f1aff00bed51e4b.html
 
运行：
 
http://wenku.baidu.com/view/37046d5133687e21af45a967.html?re=view
 
 
 
History
 
cd/home/users/cyu/work/src/OPS/feeds/wm_solva/scripts
 
mnsdev13:scripts> ls
solva_bucket.pl*        solva_feed_loader.sh*    solva_load_vendordb.pl* solva_parse.pl*          t2.pl
solva_db_cleanup.pl*    solva_fetch.pl*         solva_map.pl*           t.pl*
mnsdev13:scripts> ./t2.pl
ksh: ./t2.pl: cannot execute
mnsdev13:scripts> ls -al t2.pl
-rw-r--r--   1cyu     staff         48 Jul 13 21:16 t2.pl
mnsdev13:scripts> chmod +xt2.pl
mnsdev13:scripts> ls -alt2.pl
-rwxr-xr-x   1cyu  
 
 
cat t2.pl
 
#!/usr/local/bin/perl5
print "hello world !\n"
 
 
mnsdev13:scripts> ./test.plFASTA FASTA.out
 
 
 
 
 
 
 
 
 
cd/home/users/cyu/work/src/OPS/feeds/wm_solva/scripts
 
 
mnsdev13:scripts> vim FASTA
cmnsdev13:scripts> cat t2.pl
#!/usr/local/bin/perl5
print "hello world !\n"
mnsdev13:scripts> vim t2^?^C
mnsdev13:scripts> vim test.pl
cmnsdev13:scripts> ls -altest.pl
-rw-r--r--   1cyu     staff        240 Jul 13 21:23 test.pl
mnsdev13:scripts> perl5 t2.pl
hello world ! 
mnsdev13:scripts> chmod +xtest.pl
mnsdev13:scripts> ./test.pl
ksh: ./test.pl:  not found
mnsdev13:scripts> ls -al
total 247
drwxr-xr-x   3 cyu     staff         14 Jul 13 21:23 ./
drwxr-xr-x   7cyu     staff          9 Jul  9 01:49../
drwxr-xr-x   6cyu     staff          9 Jul  9 01:49.svn/
-rw-r--r--   1cyu     staff         29 Jul 13 21:19 FASTA
-rwxr-xr-x   1cyu      staff      24974Jul  9 01:49 solva_bucket.pl*
-rwxr-xr-x   1cyu      staff      11415Jul  9 01:49 solva_db_cleanup.pl*
-rwxr-xr-x   1cyu      staff      2400 Jul  9 01:49 solva_feed_loader.sh*
-rwxr-xr-x   1cyu      staff      16035Jul  9 01:49 solva_fetch.pl*
-rwxr-xr-x   1cyu      staff      21572Jul  9 01:49 solva_load_vendordb.pl*
-rwxr-xr-x   1cyu      staff      16058Jul  9 01:49 solva_map.pl*
-rwxr-xr-x   1cyu      staff      8589 Jul  9 01:49 solva_parse.pl*
-rwxr-xr-x   1cyu     staff         43 Jul 13 21:15 t.pl*
-rwxr-xr-x   1cyu     staff         48 Jul 13 21:16 t2.pl*
-rwxr-xr-x   1cyu     staff        240 Jul 13 21:23 test.pl*
 
mnsdev13:scripts> ls test.pl
test.pl*
mnsdev13:scripts> vim test.pl
cmnsdev13:scripts> ./test.pl
Usage: perl ./test.pl<input.fa> <out.gc>
mnsdev13:scripts> ./test.plFASTA FASTA.out
 
Sent: Tuesday, July 14, 2015 10:27 AM
 
cd/home/users/cyu/work/src/OPS/feeds/wm_solva/scripts
 
mnsdev13:scripts> ls
solva_bucket.pl*        solva_feed_loader.sh*    solva_load_vendordb.pl* solva_parse.pl*          t2.pl
solva_db_cleanup.pl*    solva_fetch.pl*         solva_map.pl*           t.pl*
mnsdev13:scripts> ./t2.pl
ksh: ./t2.pl: cannot execute
mnsdev13:scripts> ls -alt2.pl
-rw-r--r--   1cyu      staff        48 Jul 13 21:16 t2.pl
mnsdev13:scripts> chmod +xt2.pl
mnsdev13:scripts> ls -alt2.pl
-rwxr-xr-x   1cyu  
 
 
cat t2.pl
 
#!/usr/local/bin/perl5
print "hello world !\n"
 
 
mnsdev13:scripts> ./test.plFASTA FASTA.out
 
 
 
http://perldoc.perl.org/perlintro.html
7.22.2015
1          1 #!/usr/local/bin/perl5
      2 use strict;
      3 use warnings;
      4 print "Hello  world";
      5 print "Hello, world"; 
      6 print 'Hello, world';
2.    1 #!/usr/local/bin/perl5
      2 use strict;
      3 use warnings;
      4 print "Hello, $name\n";
wever, only double quotes "interpolate" variables and specialcharacters such as newlines (\n):
1.   print "Hello, $name\n";     # works fine
2.   print 'Hello, $name\n';     # prints $name\n literally
3. 1 #!/usr/local/bin/perl5
      2 use strict;
      3 use warnings;
      4 print 'Hello, $name\n';
 
4.      1 #!/usr/local/bin/perl5
      2 use strict;
      3 use warnings;
      4 print 42;
 
5.    1 #!/usr/local/bin/perl5
      2 use strict;
      3 use warnings;
      4  print("Hello, world\n");
 
6.      1 #!/usr/local/bin/perl5
      2 use strict;
      3 use warnings;
      4  print "Hello, world\n";
 
7.      1 #!/usr/local/bin/perl5
      2 use strict;
      3 use warnings;
      4 my $animal = "camel";
      5 my $answer = 42;
      6 print $animal;
      7 print "The animal is $animal\n";
      8 print"The square of $answer is ",$answer * $answer, "\n";
 
8.      1 #!/usr/local/bin/perl5
      2 use strict;
      3 use warnings;
      4 print;
 
9. 
 
10。 
11. 
12 
13   
o   Whichjobs failed in hasched2 ?  i.e. use fail command line
o   Usejr to see the fail job of markit_xform_daily
o   What’sthe error of market_xform_daily ?  i.e. use analog -f market_xform_dailyto find out the error messages .
Which cycle the market_xform_daily belongs to ? i.e. usewhich_box command line to find it out
 
 
hasched2% fail
hasched2% jr markit_xform_daily
hasched2% analog -fmarkit_xform_daily
hasched2% which_boxmarkit_xform_daily
 
o   
·       2> sp__helpf_action_hist
·       3> go
 
 
mnsdev13:yoliu> isql -U fis_dist -P fisreadw -S HXGCDB1
1> use dmo_govcorp
1> sp__helptext chk_relationship_hist#to_obj1
2> go
CREATE PROCEDURE dbo.chk_relationship_hist#to_obj1
   @oid id_TY,
   @rc int OUT,
   @msg varchar(512) OUT
AS
-- $Id: chk_relationship_hist#to_obj1.pr 57363 2013-06-06 07:21:47Zwinnie.wang
$
-- $Rev: 57363 $
BEGIN
    DECLARE
        @rid                id_TY,
        @is_dup             int,
        @is_overflow        int,
        @dup_cnt            int,
        @group_cnt          int,
        @value              varchar(100),
        @prev_value         varchar(100),
        @msg_part           varchar(512),
        @msg_tmp            varchar(512)
    SELECT @is_dup = 0,@dup_cnt = 0, @group_cnt = 0, @is_overflow = 0
    -- Create cursor
    DECLARE relation_curCURSOR FOR
    SELECTr.relation_id,  r.relation_type_cd +bintostr(r.from_obj_oid) +
r.from_obj_type_cd
            +bintostr(r.to_obj_oid) + r.to_obj_type_cd
            +isnull(convert(varchar(8), r.eff_zdt, 112),'NULL')
            +isnull(convert(varchar(8), r.end_zdt, 112),'NULL') as value
    FROM relationship_histr, gc_support..relation_type_cfg g
    WHEREr.to_obj_oid       = @oid
      ANDr.relation_type_cd = g.relation_type_cd
      ANDr.from_obj_type_cd = g.from_obj_type_cd
      AND r.to_obj_type_cd   = g.to_obj_type_cd
      ANDg.obj_type_cd      = 'GCRE'
      ANDg.analyst_edit_fl  = 'y'
    ORDER BY value
    OPEN relation_cur
    FETCH relation_curINTO  @rid, @value
    IF (@@sqlstatus = 0)
    BEGIN
        SELECT @prev_value= @value, @group_cnt = 1
        SELECT @msg_part ='0x' + bintostr(@rid)
        FETCH relation_curINTO  @rid, @value
        WHILE (@@sqlstatus = 0 )
        BEGIN
            IF (@value<> @prev_value)
            BEGIN
                IF(@group_cnt > 1)
                BEGIN
                    SELECT@is_dup = 1
                    IF(@msg_tmp IS NULL)
                    BEGIN
                       SELECT @msg_tmp = '(' + @msg_part + ')'
                    END
                    ELSE
                    BEGIN
                       SELECT @msg_tmp = @msg_tmp + ', (' + @msg_part + ')'
                    END
                END
                SELECT@prev_value = @value, @group_cnt = 1
                SELECT@msg_part = '0x' + bintostr(@rid)
            END
            ELSE
            BEGIN
                IF(@dup_cnt > 7)
                BEGIN
                    SELECT@is_overflow = 1
                    BREAK
                END
                IF(@group_cnt = 1)
                BEGIN
                    SELECT@dup_cnt = @dup_cnt + 1
                END
                SELECT@msg_part = @msg_part + ', 0x' + bintostr(@rid)
                SELECT@group_cnt = @group_cnt + 1,  @dup_cnt =@dup_cnt + 1
            END
            FETCHrelation_cur INTO @rid, @value
        END
        IF (@group_cnt> 1)
        BEGIN
            SELECT @is_dup= 1
            IF (@msg_tmpIS NULL)
            BEGIN
                SELECT@msg_tmp = '(' + @msg_part + ')'
            END
            ELSE
            BEGIN
                SELECT@msg_tmp = @msg_tmp + ', (' + @msg_part + ')'
            END
        END
        IF (@is_overflow =1)
        BEGIN
            SELECT@msg_tmp = @msg_tmp + ' and more'
        END
    END
    CLOSE relation_cur
    DEALLOCATErelation_cur
    IF (@is_dup = 1)
    BEGIN
        SELECT @rc = 0
        SELECT @msg ="Duplicate Relationships,relation_id: " + @msg_tmp + '.'
    END
    ELSE
    BEGIN
        SELECT @rc = 1
        SELECT @msg = NULL
    END
END
                                                                            
go
(return status = 0)
1>
 
select count(*) fromdmo_muni..asset t1,gcodes..asset_status_cds t2 where t1.asset_status_cd = 
 
t2.asset_status_cd andt2.active_fl = 'y'
 
System procedures:
• sp_who, sp__lock,sp__helptable, sp__helpcolumn,sp__helpdb, sp__helptext, sp__helpproc ,
sp__helptriggeretc.
333

creat table

alter reference

insert data into tables

inner join

use A table .cd B table .cd A table.cd char(2) B table.cd char(8)

select tmp_as.cd, typ_cds.cd from tmp_as innner join typ_cdson tmp_as.cd = typ_cds.cd

select * from tmp_as inner join typ_cds on tmp_as.cd = typ_cds.cd

select cd, count(*) from tmp_asgroup by cd

select cd,count(*) from tmp_asgroup by cd

order by count(*) desc

create index ,delete index

Bcp data out to files

Grant read permission to tables

create trigger

isql -U fis_dist -P fisreadw -SHGCDB1

1> use dmo_govcorp

2> go

Please find out records countwhich in different asset_info_src_cd in dmo_govcorp..asset table .

The result should be like thefollowing: ( tip: please use count(*), group by and order by )

asset_info_src_cd

----------------------------

RTR 1611637

WM 526630

ISM 441547

MPD 223134

KOS 185046

CDS 132780

BDG 80028

INF 59759

PRS 48576

PRO 45182

MKT 42835

LVA 34700

CSP 27592

RSC 21986

PAL 17757

CIB 12474

STM 9862

OKB 8200

AIA 6268

VLF 4862

IDI 4265

TRS 2725

COM 2294

VPS 2173

DTC 1905

SCR 1853

ERD 1850

AIF 1815

IDV 721

CBK 497

PUB 270

IFR 184

NULL 76

OTH 75

RRS 14

TRD 10

ANR 9

BIA 6

ELC 5

EUR 4

S&P 3

(41 rows affected)

alter table asset_ident addconstraint fk_id foreign key(asset_id) references asset(asset_id)

1> alter table asset_identmodify asset_id binary(8 ) null

2> go

Msg 10304, Level 14, State 1:

Server 'HXGCDB1', Line 1:

Only the owner of object'asset_ident' or a user with 'sa_role' role can run

this command.

1> alter table asset_identmodify asset_id null

2> go

Msg 10304, Level 14, State 1:

Server 'HXGCDB1', Line 1:

Only the owner of object'asset_ident' or a user with 'sa_role' role can run

this command.

eg:

1>selectasset_info_src_cd,count(*) from assetwheredatediff(week,asset_setup_dt,getdate())=0

2> group byasset_info_src_cd

eg:

select asset_info_src_cd,count (asset_info_src_cd) from asset wheredatepart(week,asset_setup_dt)=datepart(week,getdate()) group by asset_info_src_cd

eg: select asset_info_src_cd,count(*) from asset whereasset_setup_dt >'20150706' and asset_setup_dt <=getdate() group byasset_info_src_cd

Documents:

http://wenku.baidu.com/view/c8dbc908f12d2af90242e629.html

http://wenku.baidu.com/view/825558dc6f1aff00bed51e4b.html

运行：

http://wenku.baidu.com/view/37046d5133687e21af45a967.html?re=view

History

cd/home/users/cyu/work/src/OPS/feeds/wm_solva/scripts

mnsdev13:scripts> ls

solva_bucket.pl* solva_feed_loader.sh* solva_load_vendordb.pl* solva_parse.pl* t2.pl

solva_db_cleanup.pl* solva_fetch.pl* solva_map.pl* t.pl*

mnsdev13:scripts> ./t2.pl

ksh: ./t2.pl: cannot execute

mnsdev13:scripts> ls -al t2.pl

-rw-r--r-- 1cyu staff 48 Jul 13 21:16 t2.pl

mnsdev13:scripts> chmod +xt2.pl

mnsdev13:scripts> ls -alt2.pl

-rwxr-xr-x 1cyu

cat t2.pl

#!/usr/local/bin/perl5

print "hello world !\n"

mnsdev13:scripts> ./test.plFASTA FASTA.out

cd/home/users/cyu/work/src/OPS/feeds/wm_solva/scripts

mnsdev13:scripts> vim FASTA

cmnsdev13:scripts> cat t2.pl

#!/usr/local/bin/perl5

print "hello world !\n"

mnsdev13:scripts> vim t2^?^C

mnsdev13:scripts> vim test.pl

cmnsdev13:scripts> ls -altest.pl

-rw-r--r-- 1cyu staff 240 Jul 13 21:23 test.pl

mnsdev13:scripts> perl5 t2.pl

hello world !

mnsdev13:scripts> chmod +xtest.pl

mnsdev13:scripts> ./test.pl

ksh: ./test.pl: not found

mnsdev13:scripts> ls -al

total 247

drwxr-xr-x 3 cyu staff 14 Jul 13 21:23 ./

drwxr-xr-x 7cyu staff 9 Jul 9 01:49../

drwxr-xr-x 6cyu staff 9 Jul 9 01:49.svn/

-rw-r--r-- 1cyu staff 29 Jul 13 21:19 FASTA

-rwxr-xr-x 1cyu staff 24974Jul 9 01:49 solva_bucket.pl*

-rwxr-xr-x 1cyu staff 11415Jul 9 01:49 solva_db_cleanup.pl*

-rwxr-xr-x 1cyu staff 2400 Jul 9 01:49 solva_feed_loader.sh*

-rwxr-xr-x 1cyu staff 16035Jul 9 01:49 solva_fetch.pl*

-rwxr-xr-x 1cyu staff 21572Jul 9 01:49 solva_load_vendordb.pl*

-rwxr-xr-x 1cyu staff 16058Jul 9 01:49 solva_map.pl*

-rwxr-xr-x 1cyu staff 8589 Jul 9 01:49 solva_parse.pl*

-rwxr-xr-x 1cyu staff 43 Jul 13 21:15 t.pl*

-rwxr-xr-x 1cyu staff 48 Jul 13 21:16 t2.pl*

-rwxr-xr-x 1cyu staff 240 Jul 13 21:23 test.pl*

mnsdev13:scripts> ls test.pl

test.pl*

mnsdev13:scripts> vim test.pl

cmnsdev13:scripts> ./test.pl

Usage: perl ./test.pl<input.fa> <out.gc>

mnsdev13:scripts> ./test.plFASTA FASTA.out

Sent: Tuesday, July 14, 2015 10:27 AM

cd/home/users/cyu/work/src/OPS/feeds/wm_solva/scripts

mnsdev13:scripts> ls

solva_bucket.pl* solva_feed_loader.sh* solva_load_vendordb.pl* solva_parse.pl* t2.pl

solva_db_cleanup.pl* solva_fetch.pl* solva_map.pl* t.pl*

mnsdev13:scripts> ./t2.pl

ksh: ./t2.pl: cannot execute

mnsdev13:scripts> ls -alt2.pl

-rw-r--r-- 1cyu staff 48 Jul 13 21:16 t2.pl

mnsdev13:scripts> chmod +xt2.pl

mnsdev13:scripts> ls -alt2.pl

-rwxr-xr-x 1cyu

cat t2.pl

#!/usr/local/bin/perl5

print "hello world !\n"

mnsdev13:scripts> ./test.plFASTA FASTA.out

http://perldoc.perl.org/perlintro.html

7.22.2015

1 1 #!/usr/local/bin/perl5

2 use strict;

3 use warnings;

4 print "Hello world";

5 print "Hello, world";

6 print 'Hello, world';

2. 1 #!/usr/local/bin/perl5

2 use strict;

3 use warnings;

4 print "Hello, $name\n";

wever, only double quotes "interpolate" variables and specialcharacters such as newlines (\n):

1.   print "Hello, $name\n";     # works fine

2.   print 'Hello, $name\n';     # prints $name\n literally

3. 1 #!/usr/local/bin/perl5

      2 use strict;

      3 use warnings;

      4 print 'Hello, $name\n';

4.      1 #!/usr/local/bin/perl5

      2 use strict;

      3 use warnings;

      4 print 42;

5.    1 #!/usr/local/bin/perl5

      2 use strict;

      3 use warnings;

      4  print("Hello, world\n");

6.      1 #!/usr/local/bin/perl5

      2 use strict;

      3 use warnings;

      4  print "Hello, world\n";

7.      1 #!/usr/local/bin/perl5

      2 use strict;

      3 use warnings;

      4 my $animal = "camel";

      5 my $answer = 42;

      6 print $animal;

      7 print "The animal is $animal\n";

      8 print"The square of $answer is ",$answer * $answer, "\n";

8.      1 #!/usr/local/bin/perl5

      2 use strict;

      3 use warnings;

      4 print;

9.

10。

11.

o Whichjobs failed in hasched2 ? i.e. use fail command line

o Usejr to see the fail job of markit_xform_daily

o What’sthe error of market_xform_daily ? i.e. use analog -f market_xform_dailyto find out the error messages .

Which cycle the market_xform_daily belongs to ? i.e. usewhich_box command line to find it out

hasched2% fail

hasched2% jr markit_xform_daily

hasched2% analog -fmarkit_xform_daily

hasched2% which_boxmarkit_xform_daily

· 2> sp__helpf_action_hist

· 3> go

mnsdev13:yoliu> isql -U fis_dist -P fisreadw -S HXGCDB1

1> use dmo_govcorp

1> sp__helptext chk_relationship_hist#to_obj1

2> go

CREATE PROCEDURE dbo.chk_relationship_hist#to_obj1

@oid id_TY,

@rc int OUT,

@msg varchar(512) OUT

-- $Id: chk_relationship_hist#to_obj1.pr 57363 2013-06-06 07:21:47Zwinnie.wang

-- $Rev: 57363 $

BEGIN

DECLARE

@rid id_TY,

@is_dup int,

@is_overflow int,

@dup_cnt int,

@group_cnt int,

@value varchar(100),

@prev_value varchar(100),

@msg_part varchar(512),

@msg_tmp varchar(512)

SELECT @is_dup = 0,@dup_cnt = 0, @group_cnt = 0, @is_overflow = 0

-- Create cursor

DECLARE relation_curCURSOR FOR

SELECTr.relation_id, r.relation_type_cd +bintostr(r.from_obj_oid) +

r.from_obj_type_cd

+bintostr(r.to_obj_oid) + r.to_obj_type_cd

+isnull(convert(varchar(8), r.eff_zdt, 112),'NULL')

+isnull(convert(varchar(8), r.end_zdt, 112),'NULL') as value

FROM relationship_histr, gc_support..relation_type_cfg g

WHEREr.to_obj_oid = @oid

ANDr.relation_type_cd = g.relation_type_cd

ANDr.from_obj_type_cd = g.from_obj_type_cd

AND r.to_obj_type_cd = g.to_obj_type_cd

ANDg.obj_type_cd = 'GCRE'

ANDg.analyst_edit_fl = 'y'

ORDER BY value

OPEN relation_cur

FETCH relation_curINTO @rid, @value

IF (@@sqlstatus = 0)

BEGIN

SELECT @prev_value= @value, @group_cnt = 1

SELECT @msg_part ='0x' + bintostr(@rid)

FETCH relation_curINTO @rid, @value

WHILE (@@sqlstatus = 0 )

BEGIN

IF (@value<> @prev_value)

BEGIN

IF(@group_cnt > 1)

BEGIN

SELECT@is_dup = 1

IF(@msg_tmp IS NULL)

BEGIN

SELECT @msg_tmp = '(' + @msg_part + ')'

END

ELSE

BEGIN

SELECT @msg_tmp = @msg_tmp + ', (' + @msg_part + ')'

END

SELECT@prev_value = @value, @group_cnt = 1

SELECT@msg_part = '0x' + bintostr(@rid)

END

ELSE

BEGIN

IF(@dup_cnt > 7)

BEGIN

SELECT@is_overflow = 1

BREAK

END

IF(@group_cnt = 1)

BEGIN

SELECT@dup_cnt = @dup_cnt + 1

END

SELECT@msg_part = @msg_part + ', 0x' + bintostr(@rid)

SELECT@group_cnt = @group_cnt + 1, @dup_cnt =@dup_cnt + 1

END

FETCHrelation_cur INTO @rid, @value

END

IF (@group_cnt> 1)

BEGIN

SELECT @is_dup= 1

IF (@msg_tmpIS NULL)

BEGIN

SELECT@msg_tmp = '(' + @msg_part + ')'

END

ELSE

BEGIN

SELECT@msg_tmp = @msg_tmp + ', (' + @msg_part + ')'

END

IF (@is_overflow =1)

BEGIN

SELECT@msg_tmp = @msg_tmp + ' and more'

END

CLOSE relation_cur

DEALLOCATErelation_cur

IF (@is_dup = 1)

BEGIN

SELECT @rc = 0

SELECT @msg ="Duplicate Relationships,relation_id: " + @msg_tmp + '.'

END

ELSE

BEGIN

SELECT @rc = 1

SELECT @msg = NULL

END

(return status = 0)

select count(*) fromdmo_muni..asset t1,gcodes..asset_status_cds t2 where t1.asset_status_cd =

t2.asset_status_cd andt2.active_fl = 'y'

System procedures:

• sp_who, sp__lock,sp__helptable, sp__helpcolumn,sp__helpdb, sp__helptext, sp__helpproc ,

sp__helptriggeretc.

0 0