please suggest me help for following case stateme

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

please suggest me help for following case stateme

Author

Topic

dasu
Posting Yak Master

104 Posts

Posted - 2004-10-10 : 05:27:59

here iam updating the t_cbs_rqst_dtls table as following way

1.if t_cbs_epierr.upd_actn_fl='yy' then iam updating transactions and positions

2.if t_cbs_epierr.upd_actn_fl='yn' then iam updating transactions
t_cbs_rqst_dtls.rqst_cd=dbo.fn_cbs_trn()

3.if t_cbs_epierr.upd_actn_fl='ny' then iam updating positions
t_cbs_rqst_dtls.rqst_cd=dbo.fn_cbs_pstn()

plsease suggest me the solution in such a way that instead of 3 update statements suggest me one update statement
with case statement.
please suggest me the solution

--Updating both the records
UPDATE t_cbs_rqst_dtls
SET t_cbs_rqst_dtls.stat_cd=dbo.fn_cbs_rqst_err(),
t_cbs_rqst_dtls.rsn_tx=t_cbs_epierr.err_cd,
t_cbs_rqst_dtls.upd_job_id=@pintJobId
FROM t_cbs_rqst_dtls,t_cbs_epierr
WHERE cast (t_cbs_epierr.acct_no as int)=t_cbs_rqst_dtls.acct_no
and t_cbs_epierr.val_dt=t_cbs_rqst_dtls.val_dt
and t_cbs_epierr.upd_actn_fl='yy'

-- Update positions records in t_cbs_rqst_dtls table by using t_cbs_epierr

UPDATE t_cbs_rqst_dtls
SET t_cbs_rqst_dtls.stat_cd=dbo.fn_cbs_rqst_err(),
t_cbs_rqst_dtls.rsn_tx=t_cbs_epierr.err_cd,
t_cbs_rqst_dtls.upd_job_id=@pintJobId
FROM t_cbs_rqst_dtls,t_cbs_epierr
WHERE cast (t_cbs_epierr.acct_no as int)=t_cbs_rqst_dtls.acct_no
and t_cbs_epierr.val_dt=t_cbs_rqst_dtls.val_dt
and t_cbs_epierr.upd_actn_fl='yn'
and t_cbs_rqst_dtls.rqst_cd=dbo.fn_cbs_trn()

-- Update transactions records in t_cbs_rqst_dtls table by using t_cbs_epierr

UPDATE t_cbs_rqst_dtls
SET t_cbs_rqst_dtls.stat_cd=dbo.fn_cbs_rqst_err(),
t_cbs_rqst_dtls.rsn_tx=t_cbs_epierr.err_cd,
t_cbs_rqst_dtls.upd_job_id=@pintJobId
FROM t_cbs_rqst_dtls,t_cbs_epierr
WHERE cast (t_cbs_epierr.acct_no as int)=t_cbs_rqst_dtls.acct_no
and t_cbs_epierr.val_dt=t_cbs_rqst_dtls.val_dt
and t_cbs_epierr.upd_actn_fl='ny'
and t_cbs_rqst_dtls.rqst_cd=dbo.fn_cbs_pstn()

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-10 : 11:32:41

UPDATE t_cbs_rqst_dtls
SET stat_cd = case when t_cbs_epierr.val_dt=t_cbs_rqst_dtls.val_dt
and t_cbs_epierr.upd_actn_fl='ny'
and t_cbs_rqst_dtls.rqst_cd=dbo.fn_cbs_pstn() then dbo.fn_cbs_rqst_err()
when cast t_cbs_epierr.val_dt=t_cbs_rqst_dtls.val_dt
and t_cbs_epierr.upd_actn_fl='yn'
and t_cbs_rqst_dtls.rqst_cd=dbo.fn_cbs_trn() then dbo.fn_cbs_rqst_err()
when t_cbs_epierr.val_dt=t_cbs_rqst_dtls.val_dt
and t_cbs_epierr.upd_actn_fl='yy' then dbo.fn_cbs_rqst_err()
end
FROM t_cbs_rqst_dtls,t_cbs_epierr
WHERE cast (t_cbs_epierr.acct_no as int)=t_cbs_rqst_dtls.acct_no

this is just for one column. but you should get the idea hoe to go on...

Go with the flow & have fun! Else fight the flow

dasu
Posting Yak Master

104 Posts

Posted - 2004-10-11 : 02:31:34

thanks for above solution .
but my question is defferent
1.update transaction records
2.update positions records
3.update both positions and transaction records
so now i want to select any one from above three
by using case statement.For that iam getting flag like (ny)for 1
(yn) for 2 and (yy) for 3 statements which i specified above .
please
suggest me the solution
regards
dasu.g

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-11 : 05:10:22

Here we go...

UPDATE t_cbs_rqst_dtls SET
	t_cbs_rqst_dtls.stat_cd 	= dbo.fn_cbs_rqst_err(),
	t_cbs_rqst_dtls.rsn_tx 		= t_cbs_epierr.err_cd,
	t_cbs_rqst_dtls.upd_job_id 	= @pintJobId
FROM
	t_cbs_rqst_dtls
	JOIN t_cbs_epierr ON
		CAST (t_cbs_epierr.acct_no AS INT) = t_cbs_rqst_dtls.acct_no
		AND t_cbs_epierr.val_dt = t_cbs_rqst_dtls.val_dt
		AND( 	( t_cbs_epierr.upd_actn_fl = 'yy' )
			OR ( t_cbs_epierr.upd_actn_fl = 'yn' AND t_cbs_rqst_dtls.rqst_cd = dbo.fn_cbs_trn() )
			OR ( t_cbs_epierr.upd_actn_fl ='ny' AND t_cbs_rqst_dtls.rqst_cd=dbo.fn_cbs_pstn() ) )

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

dasu
Posting Yak Master

104 Posts

Posted - 2004-10-11 : 05:25:50

EXCELLENT BRAIN
THANKS
REGARDS
DASU.G

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources