Showing posts with label fields. Show all posts
Showing posts with label fields. Show all posts

Sunday, March 11, 2012

Error In Replace With Spaces

Hi !
I'm trying to put a space after . and , in varchar fields.

I've tried

UPDATE Resultados
SET RespuestaAbierta = REPLACE(RespuestaAbierta, ',', ', ')

but nothing happened

What's wrong ??

Thanks and sorry if my english isnt so good.

Eduardo (from Argentina)This works fine for me:

drop table test2
create table test2(f1 varchar(1000))
insert test2 select '1,2,3,4,5,6,7'
select REPLACE(f1, ',', ', ') from test2

update test2 set f1=REPLACE(f1, ',', ', ')
select * from test2|||It seems to work fine for me, using:SELECT Replace('1,2,3,4,5,6,7,8,9', ',', ', ')Maybe the character you are looking for only looks like it is a comma.

-PatP|||Trabajos para m

DECLARE @.RespuestaAbierta varchar(50)
SELECT @.RespuestaAbierta = 'Brett,Kaiser'
SELECT REPLACE(@.RespuestaAbierta, ',', ', ')|||I can't understand,

this works fine ...

drop table test2
create table test2(f1 varchar(4000))
insert test2 select '1,2,3,4,5,6,7'
insert test2 select 'ninguna en especial,me la recomendo mi hermano.'
select REPLACE(f1, ',', ', ') from test2
update test2 set f1=REPLACE(f1, ',', ', ')

select * from test2
1, 2, 3, 4, 5, 6, 7
ninguna en especial, me la recomendo mi hermano.

but this one doesn't

select respuestaabierta,
REPLACE(respuestaabierta, ',', ', ')
from resultados
where idresultado = 1244591

ninguna en especial,me la recomendo mi hermano. ninguna en especial,me la recomendo mi hermano.

The field respuestaabierta is varchar(4000) null

Thanks for your answers !|||I remeber...the answer is that the comma in your column is not a comma!

That bugged us for a while once...

select char(130), ascii(',')

Do This

DECLARE @.x varchar(80)
SELECT @.x = 'This is not a comma' + CHAR(130) + ' even though it looks like one'
SELECT @.x
-- Then do

select CHARINDEX(CHAR(130),respuestaabierta)
from resultados
where idresultado = 1244591

-- If it's not 0, then that's what it is...

And let us know...|||Thanks, Brett

but that's not the problem :

select CHARINDEX(CHAR(130),respuestaabierta) , CHARINDEX(CHAR(44),respuestaabierta)
from resultados
where idresultado = 1244591
Output:

0 20

I've noticed that the table has

Collation = SQL_Latin1_General_CP1_CI_AS

and, with Collation = databasedefault works fine

what does it mean ?

Thanks|||Damn...I thought that might have been it...

Can you post the DDL of your table?

Do you know how to do that in Enterprise manager?|||Bred,
thanks for everything, I gave up
I did a stored procedure wich updates row by row (instead a single update command).
Thanks again. Eduardo (from Argentina)|||Too bad...I was gonna suggest that you find the position and say

SELECT ASCII(SUBSTRING(col1,n,1))

to see what the damn thing was...

what code did you use?

Friday, February 17, 2012

Error handling with regards to a caculation

I have a report that calculates a students GPA based on two fields in a dataset:

TrmGpaPts/CreditAwd = GPA SQL below

My issue is that the students with 0 (zero) or NULL credits awarded results in a "divide by zero error". How can I place a 0 (zero) in the GPA column when the student has 0 or NULL credits?

Thanks

select
school.schname as School,
rtrim(stugrp_active.lastname) + ', ' + rtrim(stugrp_active.firstname) as Student,
trnscrpt.suniq as Ident,
stugrp_active.graden as Grade,
trnscrpt.graden as Grd_Lvl,
sum(trnscrpt.gradcrawd) as CreditAwd,
round(sum(case when Trnscrpt.GpaCrAtt is null then 0 else Trnscrpt.GpaCrAtt end * gpamarks.gpavallvl0),3) AS TrmGpaPts

from
dbo.trnscrpt
inner join dbo.stugrp_active on (trnscrpt.suniq = stugrp_active.suniq) INNER JOIN
school ON stugrp_active.schoolc = school.schoolc INNER JOIN
gpamarks ON trnscrpt.marksetc1 = gpamarks.marksetc AND trnscrpt.markawd1 = gpamarks.mark

where
school.schname = @.school and
stugrp_active.graden =@.graden and
trnscrpt.graden = @.trnscrptgraden and
trnscrpt.termc = @.termc

group by
school.schname,
trnscrpt.termc,
rtrim(stugrp_active.lastname) + ', ' + rtrim(stugrp_active.firstname),
trnscrpt.suniq,
stugrp_active.graden,
trnscrpt.graden

order by
School,
Student

When CreditAwd is zero then you don't want to do the division (TrmGpaPts/CreditAward)

You should put something like this in the expression for your report to get the GPA.

=IIf(Fields!CreditAward.Value = 0, "N/A", (Fields!TrmGpaPts.Value) / IIf(Fields!CreditAward.Value = 0, 1, Fields!CreditAward.Value))

|||

Thanks! I see the general direction I need to take.

|||Glad I could help.

|||

Using GregSQL's expression from above, I'm getting the error below.

What does this mean to specify the data set scope and where would I do that?

The Value expression for the textbox ‘GPA’ refers to the field ‘CreditAward’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

Build complete

|||It's saying that field is not in your dataset. Open your dataset in business intelligence studio (View -> datasets). Right click Report Datasets and click refresh. See if CreditAward is then added to your dataset in that window. If not, then you are not selecting it properly in your SQL query.

|||

Thanks. Makes sense once I spotted the typing error.

Error handling with regards to a caculation

I have a report that calculates a students GPA based on two fields in a dataset:

TrmGpaPts/CreditAwd = GPA SQL below

My issue is that the students with 0 (zero) or NULL credits awarded results in a "divide by zero error". How can I place a 0 (zero) in the GPA column when the student has 0 or NULL credits?

Thanks

select
school.schname as School,
rtrim(stugrp_active.lastname) + ', ' + rtrim(stugrp_active.firstname) as Student,
trnscrpt.suniq as Ident,
stugrp_active.graden as Grade,
trnscrpt.graden as Grd_Lvl,
sum(trnscrpt.gradcrawd) as CreditAwd,
round(sum(case when Trnscrpt.GpaCrAtt is null then 0 else Trnscrpt.GpaCrAtt end * gpamarks.gpavallvl0),3) AS TrmGpaPts

from
dbo.trnscrpt
inner join dbo.stugrp_active on (trnscrpt.suniq = stugrp_active.suniq) INNER JOIN
school ON stugrp_active.schoolc = school.schoolc INNER JOIN
gpamarks ON trnscrpt.marksetc1 = gpamarks.marksetc AND trnscrpt.markawd1 = gpamarks.mark

where
school.schname = @.school and
stugrp_active.graden =@.graden and
trnscrpt.graden = @.trnscrptgraden and
trnscrpt.termc = @.termc

group by
school.schname,
trnscrpt.termc,
rtrim(stugrp_active.lastname) + ', ' + rtrim(stugrp_active.firstname),
trnscrpt.suniq,
stugrp_active.graden,
trnscrpt.graden

order by
School,
Student

When CreditAwd is zero then you don't want to do the division (TrmGpaPts/CreditAward)

You should put something like this in the expression for your report to get the GPA.

=IIf(Fields!CreditAward.Value = 0, "N/A", (Fields!TrmGpaPts.Value) / IIf(Fields!CreditAward.Value = 0, 1, Fields!CreditAward.Value))

|||

Thanks! I see the general direction I need to take.

|||Glad I could help.

|||

Using GregSQL's expression from above, I'm getting the error below.

What does this mean to specify the data set scope and where would I do that?

The Value expression for the textbox ‘GPA’ refers to the field ‘CreditAward’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

Build complete

|||It's saying that field is not in your dataset. Open your dataset in business intelligence studio (View -> datasets). Right click Report Datasets and click refresh. See if CreditAward is then added to your dataset in that window. If not, then you are not selecting it properly in your SQL query.

|||

Thanks. Makes sense once I spotted the typing error.