count of null values in columns

Hi,

I would like to ask how can I get count of null values in each column together?

I know I can use COUNT(*) - COUNT(<col>), but I need to explicitly name every column in the query.... is it possible without it?

SELECT COUNT(foo) WHERE Foo IS NULL;

GO

|||

Hi!

Thank you for your reply, however, this works only for one column. I meant how to do that if I don't know which columns are present, eg if I use SELECT * command...

|||

Here is my way:

select
coutOfNullsInCol01 = sum(case when col01 is null then 1 else 0 end),
coutOfNullsInCol02 = sum(case when col02 is null then 1 else 0 end)
from
Table

|||

This still requires you to know col01 and col02 names.

imagine table

A

B

x

NULL

NULL

NULL

x

NULL

What I'm looking for is something like

SELECT COUNTSofNULLSinCOLUMNS(*) FROM MyTable

which would return

1

3

You know I need to not specify any column names since I don't know them.

|||

If you're looking for an aggregate to count NULLS, you'll need to use a grouping function (such as GROUP BY) for that. Note that this function counts non-NULLS rather than NULLS, and still requires a name of the columns to work.

You could create a cursor of the column names by joining system tables to loop through them, but that sounds pretty un-elegant.

Why do you need this information? Are you looking for data density information?

|||

Hu! I dont know how to do this which lets you use a "SELECT" syntax.

But this can be done with a proc which takes the tables name as an input param and genrates an SQL statement dynamically to replace COL01, COL02... CONNN with acutal columns and give the same result.

Do you know how to do this? If not, wait for few minutes!

|||

Since I know row count I expect that non-NULLS or NULLS are the same for me.

System tables would be the solution, however, if the MyTable is not physically a table that won't help. Like SELECT thisstuff FROM (SELECT ...). And yes, since I'm not so experienced in SQL I just asked in case I'm missing some obvious elegant way. :)

This perhaps answers Laskhmana solution, which I guess is pretty similar idea.

Well... actually I was asking a year ago so I'll have to open some older project to answer your question, however I can't do it now. If I'll find out, I will let you know.

|||

here is the sample code (please note that this can be writen in a better way.):

declare

@.tableName sysname

set @.tableName = 'TableWithFewColumns'

-- you can create a proc with following code with above one as param

declare

@.strSQL varchar(8000)

if object_id('tempdb..#columns') is not null
drop table #columns

create table #columns(colName varchar(255))

insert into #columns
exec('select name from syscolumns (nolock) where id = object_id(''' + @.tableName + ''')')

set @.strSQL = ''

select
@.strSQL = @.strSQL + ',coutOfNulls' + colName + ' = sum(case when ' + colName + ' is null then 1 else 0 end)'
from
#columns

set @.strSQL = 'select ''' + @.tableName + ''' as SourceTableName' + @.strSQL + ' from ' + @.tableName

exec(@.strSQL)

count of null values in columns

Hi,

I would like to ask how can I get count of null values in each column together?

I know I can use COUNT(*) - COUNT(<col>), but I need to explicitly name every column in the query.... is it possible without it?

SELECT COUNT(foo) WHERE Foo IS NULL;

GO

|||

Hi!

Thank you for your reply, however, this works only for one column. I meant how to do that if I don't know which columns are present, eg if I use SELECT * command...

|||

Here is my way:

select
coutOfNullsInCol01 = sum(case when col01 is null then 1 else 0 end),
coutOfNullsInCol02 = sum(case when col02 is null then 1 else 0 end)
from
Table

|||

This still requires you to know col01 and col02 names.

imagine table

A B x NULL NULL NULL x NULL

What I'm looking for is something like

SELECT COUNTSofNULLSinCOLUMNS(*) FROM MyTable

which would return

1 3

You know I need to not specify any column names since I don't know them.

|||

If you're looking for an aggregate to count NULLS, you'll need to use a grouping function (such as GROUP BY) for that. Note that this function counts non-NULLS rather than NULLS, and still requires a name of the columns to work.

You could create a cursor of the column names by joining system tables to loop through them, but that sounds pretty un-elegant.

Why do you need this information? Are you looking for data density information?

|||

Hu! I dont know how to do this which lets you use a "SELECT" syntax.

But this can be done with a proc which takes the tables name as an input param and genrates an SQL statement dynamically to replace COL01, COL02... CONNN with acutal columns and give the same result.

Do you know how to do this? If not, wait for few minutes!

|||

Since I know row count I expect that non-NULLS or NULLS are the same for me.

System tables would be the solution, however, if the MyTable is not physically a table that won't help. Like SELECT thisstuff FROM (SELECT ...). And yes, since I'm not so experienced in SQL I just asked in case I'm missing some obvious elegant way. :)

This perhaps answers Laskhmana solution, which I guess is pretty similar idea.

Well... actually I was asking a year ago so I'll have to open some older project to answer your question, however I can't do it now. If I'll find out, I will let you know.

|||

here is the sample code (please note that this can be writen in a better way.):

declare

@.tableName sysname

set @.tableName = 'TableWithFewColumns'

-- you can create a proc with following code with above one as param

declare

@.strSQL varchar(8000)

if object_id('tempdb..#columns') is not null
drop table #columns

create table #columns(colName varchar(255))

insert into #columns
exec('select name from syscolumns (nolock) where id = object_id(''' + @.tableName + ''')')

set @.strSQL = ''

select
@.strSQL = @.strSQL + ',coutOfNulls' + colName + ' = sum(case when ' + colName + ' is null then 1 else 0 end)'
from
#columns

set @.strSQL = 'select ''' + @.tableName + ''' as SourceTableName' + @.strSQL + ' from ' + @.tableName

exec(@.strSQL)

Count of is null of datetime field

Hi All,

Im having a problem with count of null values fields and indexes...

I have a table (tb_Propose) with around 8 million lines.

A field Dt_flag -- Datetime

An index Ix_Dt_flag, nonclustered, with field Dt_flag, only

When I do the "select count(*) from tb_Propose where Dt_Flag is null", the results comes so different from real... When I do "reindex", on first time, the select works fine. However, from the second execution the results coming wrong. The database is with option "Auto Update Stats" enabled.

Results:

select count(*) from tb_Propose where Dt_flag is null
select count(*) from tb_Propose where Dt_flag is not null
select count(*) from tb_Propose

----
8405710

(1 row(s) affected)

----
3818428

(1 row(s) affected)

----
8978255

(1 row(s) affected)

[]s
Carlos Eduardo
Bizplace
www.bizplace.com.brThat is really strange. Perhaps you could build a simple test case that exhibits this behavior and post the code so I can try it out on my system?

Count of different values

Hi all,
I have a table in which there is a column that can only contain two values: Male and Female.
How should I write a query that will give me a total of both Male and Female?
For example: Male: 23 Female:27
Thanks!
SanderHello Sander,

this is a SQL statement that give you the needed datas in two records

select MaleFemale, count(1) from statistic
group by MaleFemale

Is that good for you ?

Greetings
Manfred Peter
(Alligator Company)
http://www.alligatorsql.com

Count Null Values

Hi
How do I count Null and non null values in reporting services...'
Thanks
Carlos AntonioThis will count all rows where the value of the X-field is not null:
=Count(Fields!X.Value)
You may also want to check the CountDistinct(...) aggregate function.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"C a r l o s - A n t o n i o" <carlvazpr?arroba¿yahoo.com> wrote in message
news:eVqO6h2oFHA.3316@.tk2msftngp13.phx.gbl...
> Hi
> How do I count Null and non null values in reporting services...'
> Thanks
> Carlos Antonio
>|||You can also do counts via the SUM function with a nested IIF like
this:
// This counts NOT NULL values
=SUM(IIF(Fields!MyField.Value = Nothing, 1, 0))
// This counts NULL values
=SUM(IIF(Fields!MyField.Value = Nothing, 0, 1))
Lance
http://www.lance-hunt.net|||Sorry, my comments are backwards...
// This counts NULL values
=SUM(IIF(Fields!MyField.Value = Nothing, 1, 0))
// This counts NOT NULL values
=SUM(IIF(Fields!MyField.Value = Nothing, 0, 1))sql

Count need to bring back a zero when no matches

I am trying to do a calculation with the below query and it works long as d.closegoal has values and d1.opengoal has values but the problem is when there is no count for either, I need to bring back a value of zero if there are no matches. Since I am using it in an outer select statement for a calculation it not bringing anything back because of no matches.

This is my code:

select d.lwia,

cast((d.closegoal + d1.opengoal) as float)denominator

from

(

select yg.lwia,

cast(count(yg.appid)as float) closegoal

from dbo.wiayouthgoals yg

where yg.lwia = @.RWB

-- Attained a goal in the timeframe timely or untimely

and ((convert(smalldatetime, convert(varchar(10),yg.youthattaindate, 101)) >= @.BeginDte -- Parm date for beginning of time frame needed

and convert(smalldatetime, convert(varchar(10),yg.youthattaindate, 101)) <= @.EndDte) -- Parm date for end of time frame needed

-- Goal due but not attained

or (convert(smalldatetime, convert(varchar(10),yg.youthgoalanniversary, 101)) >= @.BeginDte -- Parm date for beginning of time frame needed

and convert(smalldatetime, convert(varchar(10),yg.youthgoalanniversary, 101)) <= @.EndDte -- Parm date for end of time frame needed

and yg.youthattaingoal <> 1))

group by yg.lwia

)d,

(

-- Closure with open goal

select cast(count(yg.appid)as float) opengoal

from dbo.tbl_caseclosure cc,

dbo.wiayouthgoals yg

where yg.appid = cc.col_idnum

and convert(smalldatetime, convert(varchar(10),cc.col_closuredate, 101)) >= @.BeginDte -- Parm date for beginning of time frame needed

and convert(smalldatetime, convert(varchar(10),cc.col_closuredate, 101)) <= @.EndDte -- Parm date for end of time frame needed

and yg.youthattaindate is null

and yg.lwia = @.RWB

group by yg.lwia

)d1

)d2

I'd be tempted to put your results into a temptable or table variable and add the value 0 if the insert adds 0 rows.

eg

Code Snippet

DECLARE @.Table TABLE (Iwia INT, Denominator FLOAT)

INSERT INTO @.Table

SELECT......

IF @.@.ROWCOUNT = 0

INSERT INTO @.Table
VALUES (0, 0)

SELECT Iwia, Denominator
FROM @.Table

HTH!

|||

Use ISNULL(column, 0) around your two arguments.

e.g.

Code Snippet

select d.lwia,

cast((ISNULL(d.closegoal, 0) + ISNULL(d1.opengoal, 0)) as float)denominator

...

|||

Try this

SELECT d.lwia

--, cast((d.closegoal + d1.opengoal) as float)denominator

, SUM(goal) as denominator

FROM (

SELECT yg.lwia

,0 as goal

from dbo.wiayouthgoals yg

where yg.lwia = @.RWB

--==========

UNION ALL

--==========

SELECT yg.lwia

,IsNull(cast(count(yg.appid)as int),0) as goal-- closegoal

from dbo.wiayouthgoals yg

where yg.lwia = @.RWB

-- Attained a goal in the timeframe timely or untimely

and ((convert(smalldatetime, convert(varchar(10),yg.youthattaindate, 101)) >= @.BeginDte -- Parm date for beginning of time frame needed

and convert(smalldatetime, convert(varchar(10),yg.youthattaindate, 101)) <= @.EndDte) -- Parm date for end of time frame needed

-- Goal due but not attained

or (convert(smalldatetime, convert(varchar(10),yg.youthgoalanniversary, 101)) >= @.BeginDte -- Parm date for beginning of time frame needed

and convert(smalldatetime, convert(varchar(10),yg.youthgoalanniversary, 101)) <= @.EndDte -- Parm date for end of time frame needed

and yg.youthattaingoal <> 1))

group by yg.lwia

--==========

UNION ALL

--==========

-- Closure with open goal

select yg.lwia

, IsNull(cast(count(yg.appid)as int) as goal--opengoal

from dbo.tbl_caseclosure cc,

dbo.wiayouthgoals yg

where yg.appid = cc.col_idnum

and convert(smalldatetime, convert(varchar(10),cc.col_closuredate, 101)) >= @.BeginDte -- Parm date for beginning of time frame needed

and convert(smalldatetime, convert(varchar(10),cc.col_closuredate, 101)) <= @.EndDte -- Parm date for end of time frame needed

and yg.youthattaindate is null

and yg.lwia = @.RWB

group by yg.lwia

)d

GROUP BY d.lwia

Count need to bring back a zero when no matches

I am trying to do a calculation with the below query and it works long as d.closegoal has values and d1.opengoal has values but the problem is when there is no count for either, I need to bring back a value of zero if there are no matches. Since I am using it in an outer select statement for a calculation it not bringing anything back because of no matches.

This is my code:

select d.lwia,

cast((d.closegoal + d1.opengoal) as float)denominator

from

(

select yg.lwia,

cast(count(yg.appid)as float) closegoal

from dbo.wiayouthgoals yg

where yg.lwia = @.RWB

-- Attained a goal in the timeframe timely or untimely

and ((convert(smalldatetime, convert(varchar(10),yg.youthattaindate, 101)) >= @.BeginDte -- Parm date for beginning of time frame needed

and convert(smalldatetime, convert(varchar(10),yg.youthattaindate, 101)) <= @.EndDte) -- Parm date for end of time frame needed

-- Goal due but not attained

or (convert(smalldatetime, convert(varchar(10),yg.youthgoalanniversary, 101)) >= @.BeginDte -- Parm date for beginning of time frame needed

and convert(smalldatetime, convert(varchar(10),yg.youthgoalanniversary, 101)) <= @.EndDte -- Parm date for end of time frame needed

and yg.youthattaingoal <> 1))

group by yg.lwia

)d,

(

-- Closure with open goal

select cast(count(yg.appid)as float) opengoal

from dbo.tbl_caseclosure cc,

dbo.wiayouthgoals yg

where yg.appid = cc.col_idnum

and convert(smalldatetime, convert(varchar(10),cc.col_closuredate, 101)) >= @.BeginDte -- Parm date for beginning of time frame needed

and convert(smalldatetime, convert(varchar(10),cc.col_closuredate, 101)) <= @.EndDte -- Parm date for end of time frame needed

and yg.youthattaindate is null

and yg.lwia = @.RWB

group by yg.lwia

)d1

)d2

I'd be tempted to put your results into a temptable or table variable and add the value 0 if the insert adds 0 rows.

eg

Code Snippet

DECLARE @.Table TABLE (Iwia INT, Denominator FLOAT)

INSERT INTO @.Table

SELECT......

IF @.@.ROWCOUNT = 0

INSERT INTO @.Table
VALUES (0, 0)

SELECT Iwia, Denominator
FROM @.Table

HTH!

|||

Use ISNULL(column, 0) around your two arguments.

e.g.

Code Snippet

select d.lwia,

cast((ISNULL(d.closegoal, 0) + ISNULL(d1.opengoal, 0)) as float)denominator

...

|||

Try this

SELECT d.lwia

--, cast((d.closegoal + d1.opengoal) as float)denominator

, SUM(goal) as denominator

FROM (

SELECT yg.lwia

,0 as goal

from dbo.wiayouthgoals yg

where yg.lwia = @.RWB

--==========

UNION ALL

--==========

SELECT yg.lwia

,IsNull(cast(count(yg.appid)as int),0) as goal-- closegoal

from dbo.wiayouthgoals yg

where yg.lwia = @.RWB

-- Attained a goal in the timeframe timely or untimely

and ((convert(smalldatetime, convert(varchar(10),yg.youthattaindate, 101)) >= @.BeginDte -- Parm date for beginning of time frame needed

and convert(smalldatetime, convert(varchar(10),yg.youthattaindate, 101)) <= @.EndDte) -- Parm date for end of time frame needed

-- Goal due but not attained

or (convert(smalldatetime, convert(varchar(10),yg.youthgoalanniversary, 101)) >= @.BeginDte -- Parm date for beginning of time frame needed

and convert(smalldatetime, convert(varchar(10),yg.youthgoalanniversary, 101)) <= @.EndDte -- Parm date for end of time frame needed

and yg.youthattaingoal <> 1))

group by yg.lwia

--==========

UNION ALL

--==========

-- Closure with open goal

select yg.lwia

, IsNull(cast(count(yg.appid)as int) as goal--opengoal

from dbo.tbl_caseclosure cc,

dbo.wiayouthgoals yg

where yg.appid = cc.col_idnum

and convert(smalldatetime, convert(varchar(10),cc.col_closuredate, 101)) >= @.BeginDte -- Parm date for beginning of time frame needed

and convert(smalldatetime, convert(varchar(10),cc.col_closuredate, 101)) <= @.EndDte -- Parm date for end of time frame needed

and yg.youthattaindate is null

and yg.lwia = @.RWB

group by yg.lwia

)d

GROUP BY d.lwia

count measure question

Hi,

I have table with column which is containing two values ("Y" and "N") and I want to create measure which will count Y values and second measure which will count N value. How can I create it?

Thank you

Pavel

Is the Y/N column part of a fact or dimension table?

Probably the easiest solution would be to add two INT fields to your fact table named IsYes and IsNo. Set the values to 0 or 1 appropriately and use them as measures with a SUM aggregation function. It's simple but easy.

B.

count iif

Hope someone can help.
I am trying to do a count where a field is equal to a certain number of
values.
The example below doesn't work but this is the idea.
=Count(iif(Fields!VisitType.Value = "RTF" or "ARF" or "ERF" or "WOK",
Fields!JobNo.Value, Nothing))
I wondered if someone could point me in the right direction.
Thanks PaulSELECT SUM(CASE WHEN VisitType IN ('RTF', 'ARF', 'ERF', 'WOK') THEN 1 ELSE 0
END)
FROM your_table
--
Jacco Schalkwijk
SQL Server MVP
"pcalv" <pcalv@.discussions.microsoft.com> wrote in message
news:6D921197-A432-4C19-B5AE-1958B94CE795@.microsoft.com...
> Hope someone can help.
> I am trying to do a count where a field is equal to a certain number of
> values.
> The example below doesn't work but this is the idea.
> =Count(iif(Fields!VisitType.Value = "RTF" or "ARF" or "ERF" or "WOK",
> Fields!JobNo.Value, Nothing))
> I wondered if someone could point me in the right direction.
> Thanks Paul|||Try the posted case statement first, but if that doesn't work, you have to
spell out your fields again.
Like:
=Count(iif(Fields!VisitType.Value = "RTF" or Fields!VisitType.Value = "ARF"
or Fields!VisitType.Value = "ERF" or Fields!VisitType.Value = "WOK",
Fields!JobNo.Value, Nothing))
Hope that helps!
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"pcalv" wrote:
> Hope someone can help.
> I am trying to do a count where a field is equal to a certain number of
> values.
> The example below doesn't work but this is the idea.
> =Count(iif(Fields!VisitType.Value = "RTF" or "ARF" or "ERF" or "WOK",
> Fields!JobNo.Value, Nothing))
> I wondered if someone could point me in the right direction.
> Thanks Paul|||Thanks for the replies.
Catadmin i used your suggestion which worked great.
Cheers Paul
"Catadmin" wrote:
> Try the posted case statement first, but if that doesn't work, you have to
> spell out your fields again.
> Like:
> =Count(iif(Fields!VisitType.Value = "RTF" or Fields!VisitType.Value = "ARF"
> or Fields!VisitType.Value = "ERF" or Fields!VisitType.Value = "WOK",
> Fields!JobNo.Value, Nothing))
> Hope that helps!
> Catadmin
> --
> MCDBA, MCSA
> Random Thoughts: If a person is Microsoft Certified, does that mean that
> Microsoft pays the bills for the funny white jackets that tie in the back?
> @.=)
>
> "pcalv" wrote:
> > Hope someone can help.
> >
> > I am trying to do a count where a field is equal to a certain number of
> > values.
> >
> > The example below doesn't work but this is the idea.
> >
> > =Count(iif(Fields!VisitType.Value = "RTF" or "ARF" or "ERF" or "WOK",
> > Fields!JobNo.Value, Nothing))
> >
> > I wondered if someone could point me in the right direction.
> >
> > Thanks Paul|||Glad I could help. @.=)
Catadmin
"pcalv" wrote:
> Thanks for the replies.
> Catadmin i used your suggestion which worked great.
> Cheers Paul
>

COUNT Expression Values

Hello,

I am trying to create totals of the different values of a certain expression in the Report Footer. Currently I have the expression in a group which gives me a running subtotal of the 4 different values of the expression. Now I need 4 running Totals of the 4 different value subtotals. I tried placing some code in the Report Properties but I had a hard time trying to code visual basic within the editor.

Any info is helpful.

I answered my own question. I didn't need any coding. It was basically creating new fields and applying the calculations to the new created fields.

COUNT Expression Values

Hello,

I am trying to create totals of the different values of a certain expression in the Report Footer. Currently I have the expression in a group which gives me a running subtotal of the 4 different values of the expression. Now I need 4 running Totals of the 4 different value subtotals. I tried placing some code in the Report Properties but I had a hard time trying to code visual basic within the editor.

Any info is helpful.

I answered my own question. I didn't need any coding. It was basically creating new fields and applying the calculations to the new created fields.sql

Count Changing due to 2 values

why when I leave out the Amount fields, does my query stand correct at 15 records? When I add those 2 amouns (dc.amount and p.amount) back in it for some reason adds 3 more unwanted records and the count is then 18?

SELECT m.customer,
c.name,
c.customer,
c.state,
/*(SELECT Top 1 fd.Fee1 FROM FeeScheduleDetails fd
where c.feeSchedule = fd.code)
AS FeeSchedule, */
m.Branch,
CASE WHEN ph.batchtype = 'PUR' OR ph.batchtype = 'PAR' OR ph.batchtype = 'PCR' OR ph.batchtype = 'DUR' OR ph.batchtype = 'DAR' Then
(-ph.totalpaid + ph.ForwardeeFee)
WHEN ph.batchtype = 'PU' OR ph.batchtype = 'PC' OR ph.batchtype = 'PA' OR ph.batchtype = 'DC' OR ph.batchtype = 'DA' Then
(ph.totalpaid + ph.ForwardeeFee)
END AS [Posted Amount],
ph.systemmonth,
ph.systemyear,
ph.datepaid,
ph.totalpaid,
ph.batchtype,
m.desk,
0 AS [New Old CC],
0 AS [New Old PDC],
'In-House' AS Type,
1 AS Active,
ph.UID,
m.number,
dc.amount CC,
--p.amount AS PDC,
m.original,
CONVERT(money, ph.OverPaidAmt),
0,
0,
''
FROM dbo.Master m (NOLOCK) INNER JOIN dbo.payhistory ph ON m.number = ph.number
LEFT JOIN dbo.DebtorCreditCards dc ON dc.number = m.number
LEFT JOIN dbo.pdc p ON p.number = m.number
LEFT JOIN dbo.Customer c ON c.Customer = m.Customer

GROUP BY m.customer,
c.name,
c.customer,
c.state,
c.FeeSchedule,
m.Branch,
ph.OverPaidAmt,
ph.systemmonth,
ph.systemyear,
ph.datepaid,
ph.totalpaid,
ph.batchtype,
m.desk,
ph.UID,
m.number,
--dc.amount,
--p.amount,
m.original ,
ph.systemmonth,
ph.systemyear,
ph.ForwardeeFee

HAVING ph.systemmonth = 12 AND ph.systemyear = 2005
AND m.customer = '0000002'AND m.branch = '00002'

ORDER BY m.customer, batchtype

If you have different values, then this could imply different groups and thus increase the number of rows returned from the query.

Conor

Count (Distinct ?

Count(Distinct col1) only works with one column. What you can do is
something like this:
insert @.Table values('Jeff', 'Jones')
insert @.Table values('Jeff', 'Jones')
insert @.Table values('Jeff', 'James')
insert @.Table values('Ed', 'James')
insert @.Table values('Ed', 'James')
select Count (Distinct FName+'|'+LName)
from @.Table
Note that the I just didn't concatenate Fname and Lname because 'Tom' +
'aster' is not the same at 'To' + 'Master'> select Count (Distinct FName+'|'+LName)
Careful, make sure neither is NULLable.

'could not be converted because of a potential loss of data'

I have a FoxPro dbf that includes From Milepost (f_mp) and To Milepost (t_mp) fields. These fields contain values between -1 and 9999.9999.

I don't have FoxPro installed, but when I attach the dbf to Access, I see the fields defined as datatype Double.

I have a SQL table that I'm trying to import the dbf data into. In that table the two fields are defined as datatype Real.

When I execute the task, it fails at the first milepost value with 4 digits to the left of the decimal point.

I read up on datatypes, then redefined the milepost fields as Floats, but nothing changed.

Any ideas or suggestions would be greatly appreciated.

ginnyk

Which component is givng the error message? If it's the OLE DB Source adapter, you can change its output column data type for the milepost fields that would allow a lossless conversion. If you can't find a suitable type, as a last resort, you could try converting the data to string.|||

Ted,

The full error text is:

"[SQL DcsEx [574]] Error: There was an error with input column "t_mp" (646) on input "OLE DB Destination Input" (587). The column status returned was: "The value could not be converted because of a potential loss of data.".

No matter which FoxPro Ex source dbf I use, the task fails at the destination input, on the first record that has a number greater than or equal to 1000...specifically - 1022, 1341.76 and 1002.849. The 4 digits left of the decimal in the erroring field is the only thing the 3 records seem to have in common.

I have other tasks in the same package that convert Fox doubles to SQL real or float numbers, greater than 1000, with no trouble. I am completely stumped and don't know where to look next.

|||

Could you give us more information on what your package actually does with this data. Do you only copy or have some transforms as well? Take a look at data types used in SSIS components for the "t_mp" column.

Thanks.

corrupted values in database

I just started working on reports on an SQL 2000 database.
Many field contain numerical values stored as varchar. To make matters worse these strings contain both '.' and ',' as decimal sign.
So far this has really been a great pain :-(

The database and its application are bought as is, I cannot modify fieldtypes or anything else.

Can anyone give a good strategy to convert the strings back to numerical so I can calculate revenue's and such??

P.S.

In extreme cases string contain both ',' AND '.' , like:

5.608,42Use

convert (float, replace(column_name,',',''))

or convert (int,replace(replace(column_name,',',''),'.','') if you are sure the value is an integer|||Note quite what I need,

The string can contain either a . or a ,
With the conversion you give all figures with a ',' come out 100 fold higher than they should be, while those with a '.' come out right.

Some sort of validation seems to be needed to get all them right.

Right now I am focussing on prices which means the highest value is just 900.00 (or 900.00) so I have no problem in this column with both a ',' and a '.'|||The following seems to work:

SELECT
CASE
WHEN
SUBSTRING(REVERSE(RTRIM(LTRIM(PRIJS))),3,1) = ',' THEN
convert(float,replace(PRIJS,',',''))/100
WHEN
SUBSTRING(REVERSE(RTRIM(LTRIM(PRIJS))),3,1) = '.' THEN
convert(dec(9,2),PRIJS)
ELSE 0 END

not getting very good feelings though on having to do these king of conversions :-(|||No, me either! You should consider to clean your data before reporting them. You are not allowed to change the field type, but you should be allowed to update your data. So, define your required format, and update everything, that does not match your format, first. Your format may be "#.###,##" or "####.##"; I would prefer the last, because that can be converted into a number directly. You may even consider to convert everything to cents, which makes it easy for you to detect new values, that are not yet converted.

Another approach would be to identify your different formats, and make a union query, handling each of your formats in a separate branch.|||Can't do that.

Some of the fields are filled by procedures from the AS400 system.
THere is something fundamentaly wrong with the way the data is handled on the SQL database. (For which I just mailed an angry mail to all involved) ,

but doctor maybe you can help me with this one:

I have all the differnent flavors in the DB:

1.203,56
1,203.56
456,67
456.67

You see replacing the comma is okay if it comes as first one in the string.
If there is just a comma replace it by a '.'
If a '.' precedes a ',' replace ','by '.' and get rid of the '.'

Nice challenge ain't it?

Next private message next week, gotta cycle to the north this afternoon
:-)|||No, me either! You should consider to clean your data before reporting them. You are not allowed to change the field type, but you should be allowed to update your data. So, define your required format, and update everything, that does not match your format, first. Your format may be "#.###,##" or "####.##"; I would prefer the last, because that can be converted into a number directly. You may even consider to convert everything to cents, which makes it easy for you to detect new values, that are not yet converted.

I dont uderstand the format "#.###,##" . What does it exactly mean ?|||Originally posted by blom0344
Can't do that.

Some of the fields are filled by procedures from the AS400 system.
THere is something fundamentaly wrong with the way the data is handled on the SQL database. (For which I just mailed an angry mail to all involved) ,


I understand, that you don't have control of the import process, but who is preventing you from cleaning your data afterwards?

Originally posted by blom0344
I have all the differnent flavors in the DB:

1.203,56
1,203.56
456,67
456.67

You see replacing the comma is okay if it comes as first one in the string.
If there is just a comma replace it by a '.'
If a '.' precedes a ',' replace ','by '.' and get rid of the '.'

Nice challenge ain't it?


As I said, determine first your possible flavors. What is imported when your price is 456.- or 456.50? Is is 456[,|.]00 or just 456? So, actually I'm asking whether your decimal point is always on the 3rd last position? Your algorithm depends on that. Another point is whether you can expect a maximum number of digits, or not. So, can you also have, for example 1,234,567.89?

Assuming, your possible flavors are those 4 you gave me, a query like I propose would look like (your price field is called p)

SELECT p FROM T WHERE len(p)=6 AND SubString(p, 4,1)="."
UNION
SELECT replace(p,',','.') FROM T WHERE len(p)=6 AND SubString(p, 4,1)=","
UNION
SELECT replace(p,',','') FROM T WHERE len(p)=8 AND SubString(p, 6,1)="."
UNION
SELECT replace(replace(p,'.',''), ',','.') FROM T WHERE len(p)=8 AND SubString(p, 6,1)=","

Calling this union query U you can do your accumulation like

SELECT sum(cast(p as decimal(10,2))) from (<U>)|||-- replace(replace(p,'.',''), ',','.') --

Yep,

I am working with the replace on replace in my solution as well.
Does not seem to work like it should.
I want to use the CASE instead of UNION solution , cause I am going to assign it to a BO object

We'll continue next week...|||If you know you have only two decimal places, how about something along the lines of eliminating all commas/periods, then dividing by 100?

select convert(numeric (10, 2), replace(replace(value, ',', ''), '.', '')))/100.00

A bit pressed for time here, so I have not tested that code snippet. Hope it helps.|||Elegant solution, MCrowley. :cool:

...but make sure the values don't just only have two decimal places, but that the ALWAYS have two decimal places.

blindman|||Originally posted by blom0344
I just started working on reports on an SQL 2000 database.
Many field contain numerical values stored as varchar. To make matters worse these strings contain both '.' and ',' as decimal sign.
So far this has really been a great pain :-(

The database and its application are bought as is, I cannot modify fieldtypes or anything else.

Can anyone give a good strategy to convert the strings back to numerical so I can calculate revenue's and such??

P.S.

In extreme cases string contain both ',' AND '.' , like:

5.608,42

Dumb question ... but, any chance you are dealing with software that handles multi-currency? Do you have the CCSID translation turned on from the AS400 to the SQL server?|||Most certainly not a dumb question, but in this case we are talking about E-commerce orders from two euro countries, so every order amount is always just in one currency.

McCrowleys solution does indeed work for me, cause every order is calculated down to the euro-cent