|
Home > Archive > SQL Anywhere database > May 2005 > Stored Procedure
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| Andi Ihsan 2005-05-21, 8:23 pm |
| I Face the problem in ASA 8.03
I Create procedure in transact-sql mode
Create procedure hms. tax_dokter(@day_name
varchar(20),@DOCTID =
varchar(20),@prov_id
varchar(20))
as
begin
declare @tax_Jm decimal(4,2)
set @tax_Jm=3Dcase upper(@day_name) when 'SUNDAY' then
(select top 1 hms.jm_doctor.tax from
hms.jm_doctor where
hms.jm_doctor.doct_id =3D @DOCTID and upper(jm_hari) =3D 'MINGG=
U' and =
hms.jm_doctor.prov_id =3D @prov_id)
when 'MONDAY' then(select top 1 hms.jm_doctor.tax from
hms.jm_doctor where
hms.jm_doctor.doct_id =3D @DOCTID and upper(jm_hari) =3D 'SENIN=
' and =
hms.jm_doctor.prov_id =3D @prov_id)
when 'TUESDAY' then(select top 1 hms.jm_doctor.tax from
hms.jm_doctor where
hms.jm_doctor.doct_id =3D @DOCTID and upper(jm_hari) =3D 'SELAS=
A' and =
hms.jm_doctor.prov_id =3D @prov_id)
when 'WEDNESDAY' then(select top 1 hms.jm_doctor.tax from
hms.jm_doctor where
hms.jm_doctor.doct_id =3D @DOCTID and upper(jm_hari) =3D 'RABU'=
and =
hms.jm_doctor.prov_id =3D @prov_id)
when 'THURSDAY' then(select top 1 hms.jm_doctor.tax from
hms.jm_doctor where
hms.jm_doctor.doct_id =3D @DOCTID and upper(jm_hari) =3D 'KAMIS=
' and =
hms.jm_doctor.prov_id =3D @prov_id)
when 'FRIDAY' then(select top 1 hms.jm_doctor.tax from
hms.jm_doctor where
hms.jm_doctor.doct_id =3D @DOCTID and upper(jm_hari) =3D 'JUMAT=
' and =
hms.jm_doctor.prov_id =3D @prov_id)
when 'SATURDAY' then(select top 1 hms.jm_doctor.tax from
hms.jm_doctor where
hms.jm_doctor.doct_id =3D @DOCTID and upper(jm_hari) =3D 'SABTU=
' and =
hms.jm_doctor.prov_id =3D @prov_id)
end
return(@tax_Jm)
end
When i execute in ISQL Session
select hms. tax_dokter('MONDAY',
'DR2101- 0000040','PROV021000
1');
return value is 7 (wrong) -> should be 7.5
hms.jm_doctor.tax is decimal(4,2) and value is 7.50
Anyone know this problem ?
I use ASA 8.03.5223
TIA,
Ichank
-- =
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
| |
| Carlton Nichols 2005-05-21, 8:23 pm |
| Can you tell me what the table structure is of jm_doctor and the row that is
returned?
CMoney
"Andi Ihsan" <ichank@spymac.com> wrote in message
news:opsq5l4gcghlpfx
y@green-soft...
I Face the problem in ASA 8.03
I Create procedure in transact-sql mode
Create procedure hms. tax_dokter(@day_name
varchar(20),@DOCTID
varchar(20),@prov_id
varchar(20))
as
begin
declare @tax_Jm decimal(4,2)
set @tax_Jm=case upper(@day_name) when 'SUNDAY' then
(select top 1 hms.jm_doctor.tax from
hms.jm_doctor where
hms.jm_doctor.doct_id = @DOCTID and upper(jm_hari) = 'MINGGU' and
hms.jm_doctor.prov_id = @prov_id)
when 'MONDAY' then(select top 1 hms.jm_doctor.tax from
hms.jm_doctor where
hms.jm_doctor.doct_id = @DOCTID and upper(jm_hari) = 'SENIN' and
hms.jm_doctor.prov_id = @prov_id)
when 'TUESDAY' then(select top 1 hms.jm_doctor.tax from
hms.jm_doctor where
hms.jm_doctor.doct_id = @DOCTID and upper(jm_hari) = 'SELASA' and
hms.jm_doctor.prov_id = @prov_id)
when 'WEDNESDAY' then(select top 1 hms.jm_doctor.tax from
hms.jm_doctor where
hms.jm_doctor.doct_id = @DOCTID and upper(jm_hari) = 'RABU' and
hms.jm_doctor.prov_id = @prov_id)
when 'THURSDAY' then(select top 1 hms.jm_doctor.tax from
hms.jm_doctor where
hms.jm_doctor.doct_id = @DOCTID and upper(jm_hari) = 'KAMIS' and
hms.jm_doctor.prov_id = @prov_id)
when 'FRIDAY' then(select top 1 hms.jm_doctor.tax from
hms.jm_doctor where
hms.jm_doctor.doct_id = @DOCTID and upper(jm_hari) = 'JUMAT' and
hms.jm_doctor.prov_id = @prov_id)
when 'SATURDAY' then(select top 1 hms.jm_doctor.tax from
hms.jm_doctor where
hms.jm_doctor.doct_id = @DOCTID and upper(jm_hari) = 'SABTU' and
hms.jm_doctor.prov_id = @prov_id)
end
return(@tax_Jm)
end
When i execute in ISQL Session
select hms. tax_dokter('MONDAY',
'DR2101- 0000040','PROV021000
1');
return value is 7 (wrong) -> should be 7.5
hms.jm_doctor.tax is decimal(4,2) and value is 7.50
Anyone know this problem ?
I use ASA 8.03.5223
TIA,
Ichank
--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
| |
| Ichank 2005-05-22, 7:23 am |
| create table hms.JM_DOCTOR
(
JM_ID varchar(20) not null,
JM_HARI varchar(15) not null,
jm_pagi numeric(5,2) not null,
jm_siang numeric(5,2) not null,
jm_malam numeric(5,2) not null,
DOCT_ID varchar(20) not null,
LAST_USER varchar(25) not null,
LAST_UPDATE timestamp not null,
prov_id varchar(20) not null,
jmtd_pagi numeric(5,2),
jmtd_siang numeric(5,2),
jmtd_malam numeric(5,2),
jm_paramedik numeric(5,2),
TAX decimal(4,2),
primary key (JM_ID)
);
INSERT INTO hms.JM_DOCTOR (
JM_ID,JM_HARI,jm_pag
i,jm_siang,jm_malam,
DOCT_ID,LAST_USER,LA
ST_UPDATE,prov_i
d,jmtd_pagi,jmtd_sia
ng,jmtd_malam,jm_par
amedik,TAX ) VALUES (
'JM2101- 0000680','Minggu',65
,80,80,'DR2101- 0000040','ZAINAL','1
4/05/2005
3:35:00 PM','PROV0210001',85
,85,85,13.5,7.5 );
On 21 May 2005 17:36:54 -0700, you wrote in
sybase.public.sqlanywhere.general:
> Can you tell me what the table structure is of jm_doctor and the row
> that is
> returned?
>
> CMoney
>
> "Andi Ihsan" <ichank@spymac.com> wrote in message
> news:opsq5l4gcghlpfx
y@green-soft...
> I Face the problem in ASA 8.03
> I Create procedure in transact-sql mode
>
> Create procedure hms. tax_dokter(@day_name
varchar(20),@DOCTID
> varchar(20),@prov_id
varchar(20))
> as
> begin
> declare @tax_Jm decimal(4,2)
> set @tax_Jm=case upper(@day_name) when 'SUNDAY' then
> (select top 1 hms.jm_doctor.tax from
> hms.jm_doctor where
> hms.jm_doctor.doct_id = @DOCTID and upper(jm_hari) = 'MINGGU'
> and
> hms.jm_doctor.prov_id = @prov_id)
> when 'MONDAY' then(select top 1 hms.jm_doctor.tax from
> hms.jm_doctor where
> hms.jm_doctor.doct_id = @DOCTID and upper(jm_hari) = 'SENIN' and
> hms.jm_doctor.prov_id = @prov_id)
> when 'TUESDAY' then(select top 1 hms.jm_doctor.tax from
> hms.jm_doctor where
> hms.jm_doctor.doct_id = @DOCTID and upper(jm_hari) = 'SELASA'
> and
> hms.jm_doctor.prov_id = @prov_id)
> when 'WEDNESDAY' then(select top 1 hms.jm_doctor.tax from
> hms.jm_doctor where
> hms.jm_doctor.doct_id = @DOCTID and upper(jm_hari) = 'RABU' and
> hms.jm_doctor.prov_id = @prov_id)
> when 'THURSDAY' then(select top 1 hms.jm_doctor.tax from
> hms.jm_doctor where
> hms.jm_doctor.doct_id = @DOCTID and upper(jm_hari) = 'KAMIS' and
> hms.jm_doctor.prov_id = @prov_id)
> when 'FRIDAY' then(select top 1 hms.jm_doctor.tax from
> hms.jm_doctor where
> hms.jm_doctor.doct_id = @DOCTID and upper(jm_hari) = 'JUMAT' and
> hms.jm_doctor.prov_id = @prov_id)
> when 'SATURDAY' then(select top 1 hms.jm_doctor.tax from
> hms.jm_doctor where
> hms.jm_doctor.doct_id = @DOCTID and upper(jm_hari) = 'SABTU' and
> hms.jm_doctor.prov_id = @prov_id)
> end
> return(@tax_Jm)
> end
>
> When i execute in ISQL Session
> select hms. tax_dokter('MONDAY',
'DR2101- 0000040','PROV021000
1');
> return value is 7 (wrong) -> should be 7.5
>
> hms.jm_doctor.tax is decimal(4,2) and value is 7.50
>
> Anyone know this problem ?
>
> I use ASA 8.03.5223
>
> TIA,
>
>
> Ichank
| |
| Ichank 2005-05-22, 7:23 am |
| create table hms.JM_DOCTOR
(
JM_ID varchar(20) not null,
JM_HARI varchar(15) not null,
jm_pagi numeric(5,2) not null,
jm_siang numeric(5,2) not null,
jm_malam numeric(5,2) not null,
DOCT_ID varchar(20) not null,
LAST_USER varchar(25) not null,
LAST_UPDATE timestamp not null,
prov_id varchar(20) not null,
jmtd_pagi numeric(5,2),
jmtd_siang numeric(5,2),
jmtd_malam numeric(5,2),
jm_paramedik numeric(5,2),
TAX decimal(4,2),
primary key (JM_ID)
);
INSERT INTO hms.JM_DOCTOR (
JM_ID,JM_HARI,jm_pag
i,jm_siang,jm_malam,
DOCT_ID,LAST_USER,LA
ST_UPDATE,prov_i
d,jmtd_pagi,jmtd_sia
ng,jmtd_malam,jm_par
amedik,TAX ) VALUES (
'JM2101- 0000680','Minggu',65
,80,80,'DR2101- 0000040','ZAINAL','1
4/05/2005
3:35:00 PM','PROV0210001',85
,85,85,13.5,7.5 );
On 21 May 2005 17:36:54 -0700, you wrote in
sybase.public.sqlanywhere.general:
> Can you tell me what the table structure is of jm_doctor and the row
> that is
> returned?
>
> CMoney
>
> "Andi Ihsan" <ichank@spymac.com> wrote in message
> news:opsq5l4gcghlpfx
y@green-soft...
> I Face the problem in ASA 8.03
> I Create procedure in transact-sql mode
>
> Create procedure hms. tax_dokter(@day_name
varchar(20),@DOCTID
> varchar(20),@prov_id
varchar(20))
> as
> begin
> declare @tax_Jm decimal(4,2)
> set @tax_Jm=case upper(@day_name) when 'SUNDAY' then
> (select top 1 hms.jm_doctor.tax from
> hms.jm_doctor where
> hms.jm_doctor.doct_id = @DOCTID and upper(jm_hari) = 'MINGGU'
> and
> hms.jm_doctor.prov_id = @prov_id)
> when 'MONDAY' then(select top 1 hms.jm_doctor.tax from
> hms.jm_doctor where
> hms.jm_doctor.doct_id = @DOCTID and upper(jm_hari) = 'SENIN' and
> hms.jm_doctor.prov_id = @prov_id)
> when 'TUESDAY' then(select top 1 hms.jm_doctor.tax from
> hms.jm_doctor where
> hms.jm_doctor.doct_id = @DOCTID and upper(jm_hari) = 'SELASA'
> and
> hms.jm_doctor.prov_id = @prov_id)
> when 'WEDNESDAY' then(select top 1 hms.jm_doctor.tax from
> hms.jm_doctor where
> hms.jm_doctor.doct_id = @DOCTID and upper(jm_hari) = 'RABU' and
> hms.jm_doctor.prov_id = @prov_id)
> when 'THURSDAY' then(select top 1 hms.jm_doctor.tax from
> hms.jm_doctor where
> hms.jm_doctor.doct_id = @DOCTID and upper(jm_hari) = 'KAMIS' and
> hms.jm_doctor.prov_id = @prov_id)
> when 'FRIDAY' then(select top 1 hms.jm_doctor.tax from
> hms.jm_doctor where
> hms.jm_doctor.doct_id = @DOCTID and upper(jm_hari) = 'JUMAT' and
> hms.jm_doctor.prov_id = @prov_id)
> when 'SATURDAY' then(select top 1 hms.jm_doctor.tax from
> hms.jm_doctor where
> hms.jm_doctor.doct_id = @DOCTID and upper(jm_hari) = 'SABTU' and
> hms.jm_doctor.prov_id = @prov_id)
> end
> return(@tax_Jm)
> end
>
> When i execute in ISQL Session
> select hms. tax_dokter('MONDAY',
'DR2101- 0000040','PROV021000
1');
> return value is 7 (wrong) -> should be 7.5
>
> hms.jm_doctor.tax is decimal(4,2) and value is 7.50
>
> Anyone know this problem ?
>
> I use ASA 8.03.5223
>
> TIA,
>
>
> Ichank
| |
| Andi Ihsan 2005-05-23, 3:23 am |
| create table hms.JM_DOCTOR
(
JM_ID varchar(20) not null,
JM_HARI varchar(15) not null,
jm_pagi numeric(5,2) not null,
jm_siang numeric(5,2) not null,
jm_malam numeric(5,2) not null,
DOCT_ID varchar(20) not null,
LAST_USER varchar(25) not null,
LAST_UPDATE timestamp not null,
prov_id varchar(20) not null,
jmtd_pagi numeric(5,2),
jmtd_siang numeric(5,2),
jmtd_malam numeric(5,2),
jm_paramedik numeric(5,2),
TAX decimal(4,2),
primary key (JM_ID)
);
INSERT INTO hms.JM_DOCTOR (
JM_ID,JM_HARI,jm_pag
i,jm_siang,jm_malam,
DOCT_ID,LAST_USER,LA
ST_UPDATE,prov_i
d,jmtd_pagi,jmtd_sia
ng,jmtd_malam,jm_par
amedik,TAX ) VALUES (
'JM2101- 0000680','Minggu',65
,80,80,'DR2101- 0000040','ZAINAL','1
4/05/2005
3:35:00 PM','PROV0210001',85
,85,85,13.5,7.5 );
TIA
Ichank
| |
| Greg Fenton 2005-05-23, 3:23 am |
| Andi Ihsan wrote:
> I Face the problem in ASA 8.03
> I Create procedure in transact-sql mode
>
Answered in the ianywhere.public.general newsgroups.
Please do not post to multiple newsgroups. This newsgroup is the most
appropriate for ASA-related questions.
greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/
|
|
|
|
|