Floyd's and Warshall's algorithms on relational DB schema

Hello,
I'm searching for an example of Floyd's and Warshall's algorithms in T-SQL
to find all possible paths (based on the PK - FK tables relations) in a
relational database schema (Graph).
Anyone some usefull tips?
Thanx,
Peter"PeterM" <PeterM@.discussions.microsoft.com> wrote in message
news:1914E310-C649-4A24-8B95-32DD63B625F4@.microsoft.com...
> Hello,
> I'm searching for an example of Floyd's and Warshall's algorithms in T-SQL
> to find all possible paths (based on the PK - FK tables relations) in a
> relational database schema (Graph).
> Anyone some usefull tips?
> Thanx,
> Peter
See http://tinyurl.com/49gft.
There's a recursive solution first, that you can't use with SQL Server 2000
but you can with SQL Server 2005, which is followed by an iterative solution
.
The solutions were implemented for DB2 but if you add an @. to the front of
variable names and change END WHILE to END it should be legal T-SQL.
JAG|||Peter,
Here is a link to a naive transitive closure algorithm (keep taking
powers of the adjacency matrix until you get nothing new). It might
at least help you implement Warshall's or other graph algorithms.
http://groups.google.co.uk/groups?q=kass+transclose
Steve Kass
Drew University
PeterM wrote:

>Hello,
>I'm searching for an example of Floyd's and Warshall's algorithms in T-SQL
>to find all possible paths (based on the PK - FK tables relations) in a
>relational database schema (Graph).
>Anyone some usefull tips?
>Thanx,
>Peter
>

Floats with no scientific notation

Hello,
I've been searching the groups for a way to accomplish this, but I've
come up short. What I ultimately need is a way to count the number of
decimal places in a floating point number (ie 37.88955 ==> 5, 0.02 ==>
2, etc).
What I'm currently working on is how to convert a float to decimal,
with no scientific notation.
cast(cast(f.formula_percent as decimal(20,10)) as varchar(20))
This almost does the trick, except that I also need to trim off the
trailing 0's (ie 0.02000000 -> 0.02 ==> 2).
My data has anywhere from 0 to 7 decimals, so I need a way to calculate
this dynamically.
Any Ideas?
Thanks in advance.
Brian AckermannThere have been many posts on the topic of floating point values and how
they are represented in a binary format. Simply put, what you are asking is
impossible since floating point datatypes are inherently inaccurate. There
was a post today with the topic "float return" that begins to delve into the
subject.
The short answer is to change the way you store the data if you need to
"see" the exact value that was entered. Otherwise, you will need to use
some algorithm based on an understanding of the floating point storage issue
and an analysis of the domain represented by the table/column. Something
along the lines of [convert from float to 7 place decimal number and
evaluate the result] - any logic that you do develop will be, by design, of
limited accuracy. Below is a query that you can use for testing (thanks to
Steve Kass via the thread mentioned earlier).
select cast (16.9 as float), cast (16.89999 as float)
Incidentally, presentation of data is best left to a client application.
"Brian Ackermann" <brian.ackermann@.gmail.com> wrote in message
news:1113326461.535229.251350@.f14g2000cwb.googlegroups.com...
> Hello,
> I've been searching the groups for a way to accomplish this, but I've
> come up short. What I ultimately need is a way to count the number of
> decimal places in a floating point number (ie 37.88955 ==> 5, 0.02 ==>
> 2, etc).
> What I'm currently working on is how to convert a float to decimal,
> with no scientific notation.
> cast(cast(f.formula_percent as decimal(20,10)) as varchar(20))
> This almost does the trick, except that I also need to trim off the
> trailing 0's (ie 0.02000000 -> 0.02 ==> 2).
> My data has anywhere from 0 to 7 decimals, so I need a way to calculate
> this dynamically.
> Any Ideas?
> Thanks in advance.
> Brian Ackermann
>|||Scott,
I agree with what you say. However, in my case, there are other
circumstances. For one, this is not a data presentation issue. I'm
actually adding functionality to an existing db, adding a seperate
column for the lab to be able to specify per line item precision. This
problem arises when trying to fill in this new field with data from the
table we are trying to make the precision adjustments on.
For the record, by trial and error I assembled the following. It
works, though I'm sure it does not do the job very well. Perhaps
others may find it useful too.
update formula
set Significant_Figures =
CASE
WHEN patindex('0000000000000', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) > 0
THEN len(cast(cast(f.formula_percent as decimal(20,10)) as
varchar(20))) - 1 - charindex('.', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) - 14
WHEN patindex('000000000000', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) > 0
THEN len(cast(cast(f.formula_percent as decimal(20,10)) as
varchar(20))) - 1 - charindex('.', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) - 13
WHEN patindex('00000000000', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) > 0
THEN len(cast(cast(f.formula_percent as decimal(20,10)) as
varchar(20))) - 1 - charindex('.', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) - 12
WHEN patindex('0000000000', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) > 0
THEN len(cast(cast(f.formula_percent as decimal(20,10)) as
varchar(20))) - 1 - charindex('.', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) - 11
WHEN patindex('000000000', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) > 0
THEN len(cast(cast(f.formula_percent as decimal(20,10)) as
varchar(20))) - 1 - charindex('.', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) - 10
WHEN patindex('00000000', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) > 0
THEN len(cast(cast(f.formula_percent as decimal(20,10)) as
varchar(20))) - 1 - charindex('.', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) - 9
WHEN patindex('0000000', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) > 0
THEN len(cast(cast(f.formula_percent as decimal(20,10)) as
varchar(20))) - 1 - charindex('.', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) - 8
WHEN patindex('000000', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) > 0
THEN len(cast(cast(f.formula_percent as decimal(20,10)) as
varchar(20))) - 1 - charindex('.', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) - 7
WHEN patindex('00000', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) > 0
THEN len(cast(cast(f.formula_percent as decimal(20,10)) as
varchar(20))) - 1 - charindex('.', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) - 6
WHEN patindex('0000', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) > 0
THEN len(cast(cast(f.formula_percent as decimal(20,10)) as
varchar(20))) - 1 - charindex('.', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) - 5
WHEN patindex('000', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) > 0
THEN len(cast(cast(f.formula_percent as decimal(20,10)) as
varchar(20))) - 1 - charindex('.', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) - 4
WHEN patindex('00', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) > 0
THEN len(cast(cast(f.formula_percent as decimal(20,10)) as
varchar(20))) - 1 - charindex('.', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) - 3
WHEN patindex('0', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) > 0
THEN len(cast(cast(f.formula_percent as decimal(20,10)) as
varchar(20))) - 1 - charindex('.', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) - 2
WHEN patindex('', cast(cast(f.formula_percent as decimal(20,10))
as varchar(20))) > 0
THEN len(cast(cast(f.formula_percent as decimal(20,10)) as
varchar(20))) - 1 - charindex('.', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) - 1
WHEN patindex('%0', cast(cast(f.formula_percent as decimal(20,10))
as varchar(20))) > 0
THEN len(cast(cast(f.formula_percent as decimal(20,10)) as
varchar(20))) - 1 - charindex('.', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) - 0
WHEN patindex('%', cast(cast(f.formula_percent as decimal(20,10))
as varchar(20))) > 0
THEN patindex('%', cast(cast(f.formula_percent as decimal(20,10))
as varchar(20)))
ELSE 'foo' --never happens
END
from formula f
Thanks,
Brian|||On 12 Apr 2005 11:46:55 -0700, Brian Ackermann wrote:
(snip)
>For the record, by trial and error I assembled the following. It
>works, though I'm sure it does not do the job very well. Perhaps
>others may find it useful too.
Hi Brian,
If you tested it, then I have no reason to doubt your statement that
this works - but why so complicated? Consider replacing the lengthy CASE
expression with a simple expression:
declare @.a float
set @.a = 37.800954
select len(rtrim(replace(cast(@.a as decimal(20,10)),'0',' ')))
- charindex('.',cast(@.a as decimal(20,10)))
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I work strictly on geochemical and/or hydrogeological databases in
Environmental Consulting. I have struggled for years with presenting number
s
with the correct number of significant figures, including trailing 0's(i.e.
1.00 is 1.00, not 1). The best fix that I have come up with was to create
corresponding text columns for my number fields. i.e. Result and Result_tex
t
I use this sp to populate the Result_text column
create proc update_result_text as
update lab_results
set result_text =
CASE
WHEN sig_figs + floor(-log10(abs(result))) < 0 THEN
-- The result will have trailing zeros - round first and then
use str
STR(round(result, cast(sig_figs + floor(-log10(abs(result))) as
int)), 10, 0)
ELSE
-- The result will have no trailing zeros - use str directly
STR(result, 10, cast(sig_figs + floor(-log10(abs(result))) as
int))
END
There is no way to count the number of decimal places in a floating point
number, but you can use the floor(-log10(abs(result))) function to find the
order of magnitude of the number you are looking for.
Archer
"Brian Ackermann" wrote:

> Hello,
> I've been searching the groups for a way to accomplish this, but I've
> come up short. What I ultimately need is a way to count the number of
> decimal places in a floating point number (ie 37.88955 ==> 5, 0.02 ==>
> 2, etc).
> What I'm currently working on is how to convert a float to decimal,
> with no scientific notation.
> cast(cast(f.formula_percent as decimal(20,10)) as varchar(20))
> This almost does the trick, except that I also need to trim off the
> trailing 0's (ie 0.02000000 -> 0.02 ==> 2).
> My data has anywhere from 0 to 7 decimals, so I need a way to calculate
> this dynamically.
> Any Ideas?
> Thanks in advance.
> Brian Ackermann
>|||It sounds like you might want to look at DECIMAL(s,p) and NUMERIC(s,p)
data types instead of FLOAT and REAL. The advantage of a floating
point representation is that can get it on a co-processor chip for lab
work. The advantage of a BCD representation is that there is limited
rounding errors within the range. but nobody makes BCD co-processor
chips that I know about; Burroughs had something like this in one of
their COBOL machines.
And throw in the usual lecture about doign display in the front end,
not the database.

flat files without column names; how to map over 250 columns

hi,

i am sure this question must have been anwsered some where, but after a lot of searching i still have not find the anwser.

i have flat files without column headers (267 columns in total).
since i have the file's description i have created a table to house these extracts with the columns in the same order as in the flat files.
additionally, i have an excel containing a list of the column names their data types and length as well as their position on the flat files.
in the old, DTS would map the columns without headers to those columns in the destination table using their order, in which case it works like a breeze for me. but i can not find a way of doing that in SSIS.

i would very much appreciate someone's assistance on this one since i am sure that there must be a better way than manually (and tediously & error prone) to map all those columns.

thanks in advance

nicolasdiogo wrote:

hi,

i am sure this question must have been anwsered some where, but after a lot of searching i still have not find the anwser.

i have flat files without column headers (267 columns in total).
since i have the file's description i have created a table to house these extracts with the columns in the same order as in the flat files.
additionally, i have an excel containing a list of the column names their data types and length as well as their position on the flat files.
in the old, DTS would map the columns without headers to those columns in the destination table using their order, in which case it works like a breeze for me. but i can not find a way of doing that in SSIS.

i would very much appreciate someone's assistance on this one since i am sure that there must be a better way than manually (and tediously & error prone) to map all those columns.

thanks in advance

I'm afraid its manual. I agree this is very tedious but I don't agree that its any more error prone than DTS. One of the big problems with DTS was that it would often do things for you but do things wrongly. One of the aims with SSIS was to put all responsibility in the hands of the package developer rather than letting DTS "guess".

I'm sure your answer will be "but it always works for me in DTS" and that's a valid point of course. I guess you just can't please all of the people all of the time. Personally I think the approach of putting all decisions in the hands of the developer is a good thing - but that's just me.

How long would it take to map 250 columns? I would guess at about 20 minutes? Yes its onerous but its not TOO much time out of your day is it? It must have taken you about 5 minutes to write the email above.

Another alternative might be to see if the import/export wizard does any auto-mapping for you. I'm not sure what it does to be honest but it might be worth checking out.

Sorry, I think you may be stuck doing it manually.

-Jamie

flat files without column names; how to map over 250 columns

hi,

i am sure this question must have been anwsered some where, but after a lot of searching i still have not find the anwser.

i have flat files without column headers (267 columns in total).
since i have the file's description i have created a table to house these extracts with the columns in the same order as in the flat files.
additionally, i have an excel containing a list of the column names their data types and length as well as their position on the flat files.
in the old, DTS would map the columns without headers to those columns in the destination table using their order, in which case it works like a breeze for me. but i can not find a way of doing that in SSIS.

i would very much appreciate someone's assistance on this one since i am sure that there must be a better way than manually (and tediously & error prone) to map all those columns.

thanks in advance

nicolasdiogo wrote:

hi,

i am sure this question must have been anwsered some where, but after a lot of searching i still have not find the anwser.

i have flat files without column headers (267 columns in total).
since i have the file's description i have created a table to house these extracts with the columns in the same order as in the flat files.
additionally, i have an excel containing a list of the column names their data types and length as well as their position on the flat files.
in the old, DTS would map the columns without headers to those columns in the destination table using their order, in which case it works like a breeze for me. but i can not find a way of doing that in SSIS.

i would very much appreciate someone's assistance on this one since i am sure that there must be a better way than manually (and tediously & error prone) to map all those columns.

thanks in advance

I'm afraid its manual. I agree this is very tedious but I don't agree that its any more error prone than DTS. One of the big problems with DTS was that it would often do things for you but do things wrongly. One of the aims with SSIS was to put all responsibility in the hands of the package developer rather than letting DTS "guess".

I'm sure your answer will be "but it always works for me in DTS" and that's a valid point of course. I guess you just can't please all of the people all of the time. Personally I think the approach of putting all decisions in the hands of the developer is a good thing - but that's just me.

How long would it take to map 250 columns? I would guess at about 20 minutes? Yes its onerous but its not TOO much time out of your day is it? It must have taken you about 5 minutes to write the email above.

Another alternative might be to see if the import/export wizard does any auto-mapping for you. I'm not sure what it does to be honest but it might be worth checking out.

Sorry, I think you may be stuck doing it manually.

-Jamie