Loss of Decimals Upon Link to Access

Hi. I have an Access DB that's linked to a SQL DB view. The SQL view is base
d
on a table which has some data types as float. I created a view on the table
.
The view shows me units of a product divided by units of all products. The
results are expressed in the view as decimals. So, for example, .499857. Thi
s
is what I want. However, when I link the view to Access, all of my decimals
become zero. For example, .499857 becomes 0. I'm completely confounded. Any
suggestions would be fantastic! Thanks!If the SQL view is correct and you can use Query Analyzer to
view the results and they are as expected then this is more
of any MS Access issue. Make sure you have the latest Jet
service pack installed on the client.
But this is more of an Access issue so you would want to try
posting in one of the Access newsgroups. When posting your
question, be sure to include versions (version of SQL
Server, version of Access), what service packs you are
using.
-Sue
On Wed, 19 Apr 2006 08:11:02 -0700, Mike C
<MikeC@.discussions.microsoft.com> wrote:

>Hi. I have an Access DB that's linked to a SQL DB view. The SQL view is bas
ed
>on a table which has some data types as float. I created a view on the tabl
e.
>The view shows me units of a product divided by units of all products. The
>results are expressed in the view as decimals. So, for example, .499857. Th
is
>is what I want. However, when I link the view to Access, all of my decimals
>become zero. For example, .499857 becomes 0. I'm completely confounded. Any
>suggestions would be fantastic! Thanks!

Looping through records

Hi,
I have a table with papers and second table with types of papers. Now
beetwen papers and types there is many to many relationship.
But in my application I would like to display records the way:
eg.
paper1, type1 type2 ... typex
paper2, type2
paper3, type3 type5
..
I don't know how to create view, procedure or function to create loop
through types for one paperid.
something like
for i = 0 to counter(numeber of records for paperid)
string_types = string_types + type
next record
Thank you
*** Sent via Developersdex http://www.examnotes.net ***Look at some examples that I'd not relied on it :-))))
Seriously, such kind of reports you will be better doing on the client side
create table w
(
id int,
t varchar(50)
)
insert into w values (1,'abc')
insert into w values (1,'def')
insert into w values (1,'ghi')
insert into w values (2,'ABC')
insert into w values (2,'DEF')
select * from w
create function dbo.fn_my ( @.id int)
returns varchar(100)
as
begin
declare @.w varchar(100)
set @.w=''
select @.w=@.w+coalesce(t,'')+',' from w where id=@.id
return @.w
end
select id,
dbo.fn_my (dd.id)
from
(
select distinct id from w
)
as dd
drop function dbo.fn_my
<schapopa> wrote in message news:uw4Wrl8%23FHA.272@.tk2msftngp13.phx.gbl...
> Hi,
> I have a table with papers and second table with types of papers. Now
> beetwen papers and types there is many to many relationship.
> But in my application I would like to display records the way:
> eg.
> paper1, type1 type2 ... typex
> paper2, type2
> paper3, type3 type5
> ..
> I don't know how to create view, procedure or function to create loop
> through types for one paperid.
> something like
> for i = 0 to counter(numeber of records for paperid)
> string_types = string_types + type
> next record
> Thank you
> *** Sent via Developersdex http://www.examnotes.net ***|||schapopa (schapopa) writes:
> I have a table with papers and second table with types of papers. Now
> beetwen papers and types there is many to many relationship.
> But in my application I would like to display records the way:
> eg.
> paper1, type1 type2 ... typex
> paper2, type2
> paper3, type3 type5
> ..
> I don't know how to create view, procedure or function to create loop
> through types for one paperid.
> something like
> for i = 0 to counter(numeber of records for paperid)
> string_types = string_types + type
> next record
> Thank you
In SQL 2000, the only reliable way to do this is to run a cursor. Uri showed
you an example with a function, but that relies an undefined behaviour, and
may not produce the correct result.
In SQL 2005, there is syntax for this, thanks to the XML support. (It's
somewhat obscure, but it works.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you for your help. I used function that Uri showed me as an
example. It works properly.
Cheers
Schapopa
*** Sent via Developersdex http://www.examnotes.net ***|||Thank you for your help. I used function that Uri showed me as an
example. It works properly.
Cheers
Schapopa
*** Sent via Developersdex http://www.examnotes.net ***

Lookup Transformation Join Column Types

Does anyone have a clue as to why DT_R4, DT_R8 are not allowed as join columns? This means I cannot join tables on AccountNumbers, InvoiceNumbers, etc. What a pain...

We didn't include support for floating point joins, because of the inherent inability of computers to accurately compare two floating point numbers.

To make the comparisons consistent, we would need to introduce some sort of rounding and user specified precision. If we did not, you may might be now posting a question about why 1.75 is not equal to 1.75 J

I hardly every see people store invoice or account numbers as floating point, I would be interested to know why you made this choice.

|||

>>inability of computers to accurately compare two floating point numbers

I had forgotten about that little fact.

I do not store account numbers and other similar keys as a floating point data type. However, the access database that I am importing with SSIS was created (by my predecessor) using double as the type for AccountNumber, rather than long integer. From here on out, I will do a data conversion before I do the look up and that should take care of my problem.

Thanks!

Lookup transform - incompatible data types mistery

I have a package that works fine in development. I move the package over to test and it fails validation in the lookup transform.

Error 46 Validation error. Data Flow Task - PO Lines Interface: Lookup - LIST PRICE [29621]: output column "LIST_PRICE_PER_UNIT" (29667) and reference column named "LIST_PRICE_PER_UNIT" have incompatible data types. SPO_TO_ORACLE_PO.dtsx 0 0

What strikes me as odd is the fact that I don't have a way of specifying the data types. I just specify the column I wish to return as a new column with the same name. Anyway, why would this work in one instance but not another?

thanks

John

Perhaps the data type on the lookup column is different in the new environment.|||

Check the regional settings of the development machine and que production machine!

Regards!

|||The tables are identical on both machines but I have noticed some subtle differences in the actual data values contained within the tables. I'm going to take a closer look at that. In one table the column has "0" values but in the other they are defined as "0.0000000". I'm actuall surprised that package validation would catch these kinds of differences.|||

Yeah!! :-)

Let we know if you resolved your problem!

Regards!!

Looking for table design pattern for different value types

Hi,

I need to store a list of parameters in a database. Each parameter has a name, description, comment and a value. Easy so far.

However the values are of different types. Each individual parameter has a value which may be of type int, decimal, string, boolean, custom type etc.

Which table design pattern is most appropriate?
We have a heated in-house discussion and I need supporting arguments.

Options explored so far:

1) (De-)serializing the value to a string-type.
2) Adding a column for each type, using only one column at a time.
3) Adding extra value-tables, one table for each type.
The disadvantages for each option are obvious and the basis for our discussion.

Your help in this matter will be appreciated.
Regards, Tonn

Tonn:

Can you take advantage of the SQL_VARIANT data type? Something like:

drop table dbo.parameter
go

create table dbo.parameter
( parmName varchar (40),
parmDescription varchar (80),
parmType tinyint,
parmValue sql_variant
)
go

insert into parameter values ('Integer Parm', 'Just an integer parameter', 1, 1)
insert into parameter values ('Numeric (9,2) parm', 'Yeah', 2, cast (17.50 as numeric (9,2)))
insert into parameter values ('Varchar parm', 'A varchar parm', 3, 'Yes, a varchar')
select * from parameter

-- Sample Output:

-- Warning: The table 'parameter' has been created but its maximum row size (8164) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

-- parmName parmDescription parmType parmValue
-- -- -- --
-- Integer Parm Just an integer parameter 1 1
-- Numeric (9,2) parm Yeah 2 17.50
-- Varchar parm A varchar parm 3 Yes, a varchar

|||

Hi Waldrop,

Thanks for your reply.
It most certainly is an interesting suggestion. It's like option no.1 except that the serialization has been moved to the database.

Is this the preferred way of handling this type of problem? We're in the financial market and this is a common problem for us, but there seems to be no documentation, best practices etc. available so our individual programmers tend to insist on their personal preference.

|||

I think that your purpose is the type of application that the SQL_VARIANT datatype is intended to address. It would be helpful if you could provide some sample update or select statements where you might use your proposed column. I don't think I would want to have a column for each different potential datatype. Below is an example of how you might load the data into variables. Note the need to CAST the data:

declare @.numericVar numeric (9,2)
declare @.varcharVar varchar (80)
declare @.intVar integer

set @.numericVar = (select cast (parmValue as numeric (9,2)) from parameter where parmName = 'Numeric (9,2) parm')
set @.varcharVar = (select cast (parmValue as varchar (80)) from parameter where parmName = 'Varchar parm')
set @.intVar = (select cast (parmValue as integer) from parameter where parmName = 'Integer Parm')

select @.numericVar as [@.numericVar],
@.varcharVar as [@.varcharVar],
@.intVar as [@.intVar]

-- S A M P L E O U T P U T :

-- @.numericVar @.varcharVar @.intVar
-- -- --
-- 17.50 Yes, a varchar 1

Dave

|||

Hi Dave,
I think you've already made your point and put your suggestion at the top of the list.

It's no use sending an example since every proposed alternative so far works. I'm actually looking for some form of authority to stop spending all those man-hours debating about it.
In the past I've had success regarding programming issues by referencing Gamma's "Design Patterns". I was hoping to do the same for database issues, but I found out that it's not going to be that easy.