problem replace function

Question

This is my code. I want to set the u.zru_duurdec in komma instead of point and in two decimals.
When I runs this code the error is: converting varchar value 180,00 to a column of data type int.
zru_duurdec is in both tabel a decimal.


select distinct z.zop_nummer, u.cli_nummer, u.pro_nummer, u.zru_datum, u.afd_nummer,u.mdv_nummer,u.med_nummer,
convert (decimal(12,8), sum (replace (u.zru_duurdec, '.',',')/60))
from dat_urentotaal u, dat_zoreguur z
where u.pro_nummer <> '0' and u.fim_code is null and u.zru_datum > '2008-12-28'
and u.cli_nummer = z.cli_nummer
and z.pro_nummer = u.pro_nummer
and z.zru_datum = u.zru_datum
and z.med_nummer = u.med_nummer
and z.afd_nummer =u.afd_nummer
and z.mdv_nummer = u.mdv_nummer
and z.zru_volgnr = u.zru_volgnr
and z.zru_duurdec = u.zru_duurdec
and z.zru_aanmaakdat = u.zru_aanmaakdat
and z.zru_aanmaaktd = u.zru_aanmaaktd
group by  z.zop_nummer, u.pro_nummer, u.zru_datum, u.mdv_nummer,u.med_nummer,u.cli_nummer,u.afd_nummer
having sum(u.zru_duurdec) <> 0
order by z.zop_nummer, u.pro_nummer, u.zru_datum, u.mdv_nummer,u.med_nummer,u.cli_nummer,u.afd_nummer

Answer 1

 

   What is the datetype of u.zru_duurdec ? If it is other than integer you need to convert this to integer before doing sum operation.

---

Lakshman

Answer 2

Why do you want the '.' to be replaced with ',' in a numeric field? By doing so your value becomes a non-numeric and it cannot be converted to decimal again..

Answer 3

Just to add what has been said, i'd consider doing this at the application layer rather than at the database layer. Its better to keep your data in its native format and handle formatting "higher up"

---

every day is a school day

Answer 4

hai,

Zru_duurdec is decimal datatype. How can I convert this before doing a sum operation?

Can you give it to me in sql code?

Answer 5

Hi,

   Replace the highlighted portion with below expression.

 

      taken from your query      :     convert (decimal(12,8), sum (replace (u.zru_duurdec, '.',',')/60))

      need to updated with this :     replace(convert (decimal(12,8), sum (u.zru_duurdec/60)),'.',',')

---

Lakshman

Answer 6

Tnx! It works fine!

Regards,

Answer 7

Hi,
First of all why you are replacing '.' with ','? The meaning of the value 180.00 and 180,00 will totally differ?

Also you are trying to convert a value 180,00 to decimal which will fail becaue of the ',' in the value.

I didnt not understand your requirement but check whether this code will work out for you,

select distinct z.zop_nummer, u.cli_nummer, u.pro_nummer, u.zru_datum, u.afd_nummer,u.mdv_nummer,u.med_nummer,
replace (convert (decimal(12,8), sum (u.zru_duurdec/60)), '.',',')
from dat_urentotaal u, dat_zoreguur z
where u.pro_nummer <> '0' and u.fim_code is null and u.zru_datum > '2008-12-28'
and u.cli_nummer = z.cli_nummer 
and z.pro_nummer = u.pro_nummer
and z.zru_datum = u.zru_datum
and z.med_nummer = u.med_nummer
and z.afd_nummer =u.afd_nummer
and z.mdv_nummer = u.mdv_nummer
and z.zru_volgnr = u.zru_volgnr
and z.zru_duurdec = u.zru_duurdec
and z.zru_aanmaakdat = u.zru_aanmaakdat
and z.zru_aanmaaktd = u.zru_aanmaaktd
group by  z.zop_nummer, u.pro_nummer, u.zru_datum, u.mdv_nummer,u.med_nummer,u.cli_nummer,u.afd_nummer
having sum(u.zru_duurdec) <> 0
order by z.zop_nummer, u.pro_nummer, u.zru_datum, u.mdv_nummer,u.med_nummer,u.cli_nummer,u.afd_nummer

---

Thanks, Bharani M - Please mark the post as answered if it answers your question. - Please vote the post as Helpful if you find the post as helpful.