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.
| Author |
Topic |
|
nm
Starting Member
2 Posts |
 Posted - 2002-08-30 : 03:52:01
|
| The follwoing procedure works well with MS SQL server 7.0>When i tried to create the same procedure in Sybase it is showing me a list of errors...any idea???THE PROCEDURE:--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- create procedure getclaims_allemp_new @policy char(5), @datefield_to_pick char(1), @date_from datetime, @date_to datetime, @field_on_sort char(1) as declare @selectpart nvarchar(2000) declare @orderbypart nvarchar(400) declare @completesql nvarchar(2500) if (@datefield_to_pick = '1') begin set @selectpart = N'select tclaim.policy,tclaim.claim_no,tclaim.nric_cert_no,' + char(13) set @selectpart = @selectpart + N'tclaim.sub_office,tclaim.insured_name,tclaim.claimant_name,' + char(13) set @selectpart = @selectpart + N'tdepmst.dep_desc,tlocmst.loc_desc,tben_desc.benefit_desc,' + char(13) set @selectpart = @selectpart + N'tclaim.incurred_date,tclaim.payment_date,' + char(13) set @selectpart = @selectpart + N'tclaim.presented_amt,tclaim.adjusted_amt' + char(13) set @selectpart = @selectpart + N'from (((tclaim' + char(13) set @selectpart = @selectpart + N'inner join tdepmst on tclaim.dependent_code = tdepmst.dependent_code)' + char(13) set @selectpart = @selectpart + N'inner join tlocmst on tclaim.loc_code = tlocmst.loc_code )' + char(13) set @selectpart = @selectpart + N'left outer join tben_desc on tclaim.benefit_code = tben_desc.benefit and tclaim.loc_code = tben_desc.loc_code)' + char(13) set @selectpart = @selectpart + N'where tclaim.policy = @in_policy ' + char(13) set @selectpart = @selectpart + N'and tclaim.payment_date between @in_date_from and @in_date_to' + char(13) set @selectpart = @selectpart + N'and tclaim.payment_date <> NULL ' + char(13) end else begin set @selectpart = N'select tclaim.policy,' + char(13) set @selectpart = @selectpart + N'tclaim.claim_no,tclaim.nric_cert_no,tclaim.sub_office,tclaim.insured_name,tclaim.claimant_name,' + char(13) set @selectpart = @selectpart + N'tdepmst.dep_desc,tlocmst.loc_desc,tben_desc.benefit_desc,' + char(13) set @selectpart = @selectpart + N'tclaim.incurred_date,tclaim.payment_date,' + char(13) set @selectpart = @selectpart + N'tclaim.presented_amt,tclaim.adjusted_amt' + char(13) set @selectpart = @selectpart + N'from (((tclaim' + char(13) set @selectpart = @selectpart + N'inner join tdepmst on tclaim.dependent_code = tdepmst.dependent_code)' + char(13) set @selectpart = @selectpart + N'inner join tlocmst on tclaim.loc_code = tlocmst.loc_code)' + char(13) set @selectpart = @selectpart + N'left outer join tben_desc on tclaim.benefit_code = tben_desc.benefit and tclaim.loc_code = tben_desc.loc_code)' + char(13) set @selectpart = @selectpart + N'where ' + char(13) set @selectpart = @selectpart + N'tclaim.policy = @in_policy ' + char(13) set @selectpart = @selectpart + N'and tclaim.incurred_date between @in_date_from and @in_date_to' + char(13) set @selectpart = @selectpart + N'and tclaim.payment_date <> NULL ' + char(13) end if (@field_on_sort = '0') begin set @orderbypart = N'order by tclaim.insured_name,tclaim.payment_date,tclaim.dependent_code ,tclaim.loc_code' end if (@field_on_sort = '1') begin set @orderbypart = N'order by tclaim.insured_name,tclaim.payment_date,tclaim.dependent_code ,tclaim.loc_code' end if (@field_on_sort = '2') begin set @orderbypart = N'order by tclaim.loc_code,tclaim.insured_name,tclaim.dependent_code,tclaim.payment_date' end if (@field_on_sort = '3') begin set @orderbypart = N'order by tclaim.incurred_date,tclaim.insured_name,tclaim.dependent_code,tclaim.loc_code' end if (@field_on_sort = '4') begin set @orderbypart = N'order by tclaim.sub_office,tclaim.insured_name,tclaim.dependent_code,tclaim.payment_date' end set @completesql = @selectpart + @orderbypart execute sp_executesql @completesql, N'@in_policy varchar(5),@in_datefield_to_pick varchar(1), @in_date_from datetime,@in_date_to datetime,@in_field_on_sort varchar(1)', @policy ,@datefield_to_pick , @date_from ,@date_to ,@field_on_sort THE ERRORS FROM SYBASE Procedure 'getclaims_allemp_new', Line 21:Length or precision specification 2500 is not within the range of 1 to 255. Server Message: Number 102, Severity 15Procedure 'getclaims_allemp_new', Line 29:Incorrect syntax near '@selectpart'. Server Message: Number 102, Severity 15Procedure 'getclaims_allemp_new', Line 61:Incorrect syntax near '@selectpart'. Server Message: Number 102, Severity 15Procedure 'getclaims_allemp_new', Line 93:Incorrect syntax near '@orderbypart'. Server Message: Number 102, Severity 15Procedure 'getclaims_allemp_new', Line 101:Incorrect syntax near '@orderbypart'. Server Message: Number 102, Severity 15Procedure 'getclaims_allemp_new', Line 109:Incorrect syntax near '@orderbypart'. Server Message: Number 102, Severity 15Procedure 'getclaims_allemp_new', Line 117:Incorrect syntax near '@orderbypart'. Server Message: Number 102, Severity 15Procedure 'getclaims_allemp_new', Line 125:Incorrect syntax near '@orderbypart'. Server Message: Number 137, Severity 15Procedure 'getclaims_allemp_new', Line 139:Must declare variable '@completesql'. |
|
|
KnooKie
Aged Yak Warrior
623 Posts |
Posted - 2002-09-02 : 10:13:33
|
| I don't know Sybase but it would seem that it doesn't support the nvarchar datatype or it does but not as big as SQL Server. Try decreasing their size or look for the Sybase equivalent.Do you need to use nvarchars ? What language are you using ?Paul |
 |
|
|
rihardh
Constraint Violating Yak Guru
307 Posts |
Posted - 2002-09-02 : 10:24:21
|
| This is a MSSQL forum!!! |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-09-02 : 22:18:51
|
| I'm no great expert on Sybase, but I'll bet 5 pacific pesos that it's the N' that it doesn't like, I've certainly never used them on Sybase....Try getting rid of that and let me know how you go.--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
nm
Starting Member
2 Posts |
Posted - 2002-09-09 : 11:43:23
|
| Knookie,rbb,I took out the nvarchar which was a main reason for the errors.The 'N' is to typecast the character data to nvarchar and that is not needed in the Sybase SQL.Letme know whether varchar can hold more than 255 characters?Also please let me know whether the syntax of the above posted procedure is correct for Sybase,like th if ..else constauct..begin-end syntax... |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
|
|
|
|
|
|
|
SQL HELP!!