Help with cross products and functions

Hello,
I have two tables that i need to join. The first table has a column which
is an encoded addition of one or more values in a second table.
table 1:
col1 col2 col3
1 a b 12
...
table 2:
col1 col2
1 x 4
2 y 8
3 z 16
...
the record for table 1, maps to records 1 and 2 from table 2. I know that
the value 12 in table 1 (col3) mapps to rows 1,2 in table 2 because of this
math function that decodes the encryption.
I have a function that is able to return a table that lists all the values
in table 2 that correspond to the supplied value in table 1..ie if i call
function fn_mappings(12) i get a table called @.result:
table2value
1 4
2 8
what i need to do is then combine these records together to get a view as
follows
col1 col2 table2value
1 a b 4
2 a b 8
...
is this possible to do? i need this to happen for EACH row in table 1 to
find corresponding records in table 2.
thanks for any and all help!
BenIn order to link both tables, I took the liberty of "re-create" a convenient
environment to get the results you asked. I hope it is ok.
Let me know if it works for you
-- Begin Script
create table tbl1
( id int primary key
, col1 varchar(10)
, col2 varchar(10)
, col3 int
)
create table tbl2
(
id int primary key
, fkid int
, col1 varchar(10)
, col2 int
)
insert into tbl1
values (1, 'a', 'b', 12)
insert into tbl2
values (1, 1, 'x', 4)
insert into tbl2
values (2, 1, 'x', 8)
insert into tbl2
values (3, 2, 'x', 16)
-- View the output of both tables
select * from tbl1
select * from tbl2
go
create function dbo.fn_mappings(@.in int)
returns table
as
return (select id, col2 from tbl2 where fkid = (select id from tbl1 where
col3 = @.in))
go
-- View the dbo.fn_mappings() output
select * from dbo.fn_mappings(12)
-- Output requested
select t1.id
, t1.col1
, t1.col2
, t2.col2
from tbl1 t1
inner join tbl2 t2
on t1.id = t2.fkid
-- Drop all objects
drop function dbo.fn_mappings
drop table tbl1
drop table tbl2
"Ben" wrote:

> Hello,
> I have two tables that i need to join. The first table has a column which
> is an encoded addition of one or more values in a second table.
> table 1:
> col1 col2 col3
> 1 a b 12
> ...
> table 2:
> col1 col2
> 1 x 4
> 2 y 8
> 3 z 16
> ...
> the record for table 1, maps to records 1 and 2 from table 2. I know that
> the value 12 in table 1 (col3) mapps to rows 1,2 in table 2 because of thi
s
> math function that decodes the encryption.
> I have a function that is able to return a table that lists all the values
> in table 2 that correspond to the supplied value in table 1..ie if i call
> function fn_mappings(12) i get a table called @.result:
> table2value
> 1 4
> 2 8
> what i need to do is then combine these records together to get a view as
> follows
> col1 col2 table2value
> 1 a b 4
> 2 a b 8
> ...
> is this possible to do? i need this to happen for EACH row in table 1 to
> find corresponding records in table 2.
> thanks for any and all help!
> Ben|||Edgardo,
Thank you for your response...however, I do not have the liberty of changing
table2's structure. The data is provided by an external source and I am
creating custom reporting for it. Therefor i cant give it a foreign key typ
e
column.
do you have any other ideas?
Thanks again
Ben
"Edgardo Valdez, MCSD, MCDBA" wrote:
> In order to link both tables, I took the liberty of "re-create" a convenie
nt
> environment to get the results you asked. I hope it is ok.
> Let me know if it works for you
> -- Begin Script
> create table tbl1
> ( id int primary key
> , col1 varchar(10)
> , col2 varchar(10)
> , col3 int
> )
> create table tbl2
> (
> id int primary key
> , fkid int
> , col1 varchar(10)
> , col2 int
> )
> insert into tbl1
> values (1, 'a', 'b', 12)
> insert into tbl2
> values (1, 1, 'x', 4)
> insert into tbl2
> values (2, 1, 'x', 8)
> insert into tbl2
> values (3, 2, 'x', 16)
> -- View the output of both tables
> select * from tbl1
> select * from tbl2
> go
> create function dbo.fn_mappings(@.in int)
> returns table
> as
> return (select id, col2 from tbl2 where fkid = (select id from tbl1 where
> col3 = @.in))
> go
> -- View the dbo.fn_mappings() output
> select * from dbo.fn_mappings(12)
> -- Output requested
> select t1.id
> , t1.col1
> , t1.col2
> , t2.col2
> from tbl1 t1
> inner join tbl2 t2
> on t1.id = t2.fkid
> -- Drop all objects
> drop function dbo.fn_mappings
> drop table tbl1
> drop table tbl2
>
> "Ben" wrote:
>

Help using a value from one dataset to lookup a value from a second dataset

Hi All,

I'm quite new to SSRS (and .net development as well) so any help would be useful. My problem is this: I'm running a report from a Sybase ASE datasource to pull data into a table control. One of the fields I'm pulling in is called assignment group. Now, in the same report output I also need to display who the manager of that group is... unfortunately this information is not stored in the same database. We store the manager information in a completely seperate Oracle database. So, I need some method of 'linking' the data from my Sybase dataset to a table in Oracle and I can't quite figure out how to go about it. Every record would have a manager and there could be 10k records returned so I need to be carefull about performance issues.

I was starting to go down the path of creating a .net dataset in a custom assembly which would be filled via a query to the Oracle database. What I was thinking is to create and fill a dataset with my lookup data from Oracle then, in the report, call a function from the table control which would search through the dataset in memory and return the actual manager name.

Am I on the right track or am I overcomplicating the solution? Will this be too inefficient from a performance perspective? Would a subreport be a more efficient solution?

There are a couple of solutions for you:

1) you can write a custom data extension that does the magic merge under the covers. A custom assembly won't help here because it operates on one row at a time - if you have a reasonable nuumber of rows, it might work, but with lots of rows it will probably be inefficient.

2) you can use the Linked server feature of SQL Server. This allows you to build a table/view that uses ODBC connections to other data bases to retrieve data. It has pretty reasonable performance and you don't have to create and maintain custom code. Downside, is you need to add a SQL database in addition to your existing Oracle and Sybase ones :-).

Hope that helps,

-Lukasz

help to define a search criteria with FTS

Hi!
I'm using FTS in MSSQL2000.
1. i have a string "bcd" and i want the results : "abcd" or "1bcd" but
not "bcda" or "aabcd" (always from the second letter).
2. the search column is a long string. the begining of the string is
more important that the end. i want the results found in the begining
to get higher RANK so i can present them first.
Please help.
You can't do prefix based searches in SQL FTS. You might be able to store
your content in reverse and then do wildcard based searches, i.e.
select * from tablename where contains(ColumnName,'dcb*') which will return
hits to the reversed content of abcd, or 1bcd, but unfortunately also aabcd
reversed.
You may have to use like for this type of query.
Another option would be to use a thesaurus based search if you know in
advance all search tokens which you need to map to bdc, and expand then to
search on bcd,
so bcd will expand to a search on bcd and abcd, and 1bcd.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"rom_ask_u" <nimrod4u@.gmail.com> wrote in message
news:1123138487.817248.51670@.g47g2000cwa.googlegro ups.com...
> Hi!
> I'm using FTS in MSSQL2000.
> 1. i have a string "bcd" and i want the results : "abcd" or "1bcd" but
> not "bcda" or "aabcd" (always from the second letter).
> 2. the search column is a long string. the begining of the string is
> more important that the end. i want the results found in the begining
> to get higher RANK so i can present them first.
> Please help.
>

Help Running out of disk space.. filegroup question

Thanks so much Paul.
I'll try adding the second file to the primary filegroup.
Just one question about the proportional fill manner
(relative to the %occupied space. If drive E:/ has 471MBs
free and Drive F:\ (which will hold the new file) has 20
GBs free, will Drive E: still fill up and stop the
database or will the data just flow to the F:/ drive?
of the 2 files). Thanks.
Don

>--Original Message--
>Don,
>yes, you can add the new file to the existing filegroup.
>You won't be able to move objects to this file unless you
>create a new filegroup and add the new file to it,
however
>in your case this is probably not required as when new
>space is required in the database, it is added in
>proportional fill manner (relative to the %occupied space
>of the 2 files).
>HTH,
>Paul Ibison
>.
>
IIRC, data will flow to the new empty file before the old file will expand
beyond its current size. Once they both have the same free space
(relative), I'm not sure how SQL Server will decide which one to grow. To
prolong the wait, just make sure the new file is bigger than the old one.
;-)
http://www.aspfaq.com/
(Reverse address to reply.)
<anonymous@.discussions.microsoft.com> wrote in message
news:1ac8701c44f03$2b1996d0$a101280a@.phx.gbl...[vbcol=seagreen]
> Thanks so much Paul.
> I'll try adding the second file to the primary filegroup.
> Just one question about the proportional fill manner
> (relative to the %occupied space. If drive E:/ has 471MBs
> free and Drive F:\ (which will hold the new file) has 20
> GBs free, will Drive E: still fill up and stop the
> database or will the data just flow to the F:/ drive?
> of the 2 files). Thanks.
> Don
> however
|||Thanks Paul and Aaron,
I added the second primary group file to the F:/ Drive and
it looks like the data is going to there much faster than
to the E:/ drive that was filling up. It may be too early
to tell the exact ratio of E to F of data flow. It looks
like this fix saved the day.
Don
>--Original Message--
>IIRC, data will flow to the new empty file before the old
file will expand
>beyond its current size. Once they both have the same
free space
>(relative), I'm not sure how SQL Server will decide which
one to grow. To
>prolong the wait, just make sure the new file is bigger
than the old one.[vbcol=seagreen]
>;-)
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:1ac8701c44f03$2b1996d0$a101280a@.phx.gbl...
filegroup.[vbcol=seagreen]
471MBs[vbcol=seagreen]
filegroup.[vbcol=seagreen]
you[vbcol=seagreen]
space
>
>.
>
|||To prevent any further SQL initiated filegrowth on your E: drive you can disable automatic filegrow on the primary datafile
for this particular database all together and just allow the newly created data file on your other partition to grow, and/or add other data files as need arises. This will prevent SQL from filling up the precious remaining disk space (maybe you are alread
y using more than 90%) on your E: drive before the system halts running out of diskspace. I occasionally have to do this to balance disk load on terabyte size dbs.
|||Hi there,
One of our drives is completely filled up with a database file and so I would like to move some of the db objects to a newly created data file on another drive. Can anyone give me a hint on how to do this?
Thanks
Andreas
"Sassan Karai" wrote:

> To prevent any further SQL initiated filegrowth on your E: drive you can disable automatic filegrow on the primary datafile
> for this particular database all together and just allow the newly created data file on your other partition to grow, and/or add other data files as need arises. This will prevent SQL from filling up the precious remaining disk space (maybe you are alre
ady using more than 90%) on your E: drive before the system halts running out of diskspace. I occasionally have to do this to balance disk load on terabyte size dbs.
>
|||Hi
http://support.microsoft.com/directo...Q224071--
Moving SQL Server Databases to a New Location
"Andreas" <Andreas@.discussions.microsoft.com> wrote in message
news:58B7A052-6AAB-4E31-AD6F-A13F29A895CF@.microsoft.com...
> Hi there,
> One of our drives is completely filled up with a database file and so I
would like to move some of the db objects to a newly created data file on
another drive. Can anyone give me a hint on how to do this?[vbcol=seagreen]
> Thanks
> Andreas
>
> "Sassan Karai" wrote:
disable automatic filegrow on the primary datafile[vbcol=seagreen]
created data file on your other partition to grow, and/or add other data
files as need arises. This will prevent SQL from filling up the precious
remaining disk space (maybe you are already using more than 90%) on your E:
drive before the system halts running out of diskspace. I occasionally have
to do this to balance disk load on terabyte size dbs.[vbcol=seagreen]

Help Running out of disk space.. filegroup question

Thanks so much Paul.
I'll try adding the second file to the primary filegroup.
Just one question about the proportional fill manner
(relative to the %occupied space. If drive E:/ has 471MBs
free and Drive F:\ (which will hold the new file) has 20
GBs free, will Drive E: still fill up and stop the
database or will the data just flow to the F:/ drive?
of the 2 files). Thanks.
Don

>--Original Message--
>Don,
>yes, you can add the new file to the existing filegroup.
>You won't be able to move objects to this file unless you
>create a new filegroup and add the new file to it,
however
>in your case this is probably not required as when new
>space is required in the database, it is added in
>proportional fill manner (relative to the %occupied space
>of the 2 files).
>HTH,
>Paul Ibison
>.
>IIRC, data will flow to the new empty file before the old file will expand
beyond its current size. Once they both have the same free space
(relative), I'm not sure how SQL Server will decide which one to grow. To
prolong the wait, just make sure the new file is bigger than the old one.
;-)
http://www.aspfaq.com/
(Reverse address to reply.)
<anonymous@.discussions.microsoft.com> wrote in message
news:1ac8701c44f03$2b1996d0$a101280a@.phx
.gbl...[vbcol=seagreen]
> Thanks so much Paul.
> I'll try adding the second file to the primary filegroup.
> Just one question about the proportional fill manner
> (relative to the %occupied space. If drive E:/ has 471MBs
> free and Drive F:\ (which will hold the new file) has 20
> GBs free, will Drive E: still fill up and stop the
> database or will the data just flow to the F:/ drive?
> of the 2 files). Thanks.
> Don
>
> however|||Thanks Paul and Aaron,
I added the second primary group file to the F:/ Drive and
it looks like the data is going to there much faster than
to the E:/ drive that was filling up. It may be too early
to tell the exact ratio of E to F of data flow. It looks
like this fix saved the day.
Don
>--Original Message--
>IIRC, data will flow to the new empty file before the old
file will expand
>beyond its current size. Once they both have the same
free space
>(relative), I'm not sure how SQL Server will decide which
one to grow. To
>prolong the wait, just make sure the new file is bigger
than the old one.
>;-)
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
><anonymous@.discussions.microsoft.com> wrote in message
> news:1ac8701c44f03$2b1996d0$a101280a@.phx
.gbl...
filegroup.[vbcol=seagreen]
471MBs[vbcol=seagreen]
filegroup.[vbcol=seagreen]
you[vbcol=seagreen]
space[vbcol=seagreen]
>
>.
>|||To prevent any further SQL initiated filegrowth on your E: drive you can dis
able automatic filegrow on the primary datafile
for this particular database all together and just allow the newly created d
ata file on your other partition to grow, and/or add other data files as nee
d arises. This will prevent SQL from filling up the precious remaining disk
space (maybe you are alread
y using more than 90%) on your E: drive before the system halts running out
of diskspace. I occasionally have to do this to balance disk load on terabyt
e size dbs.|||Hi there,
One of our drives is completely filled up with a database file and so I woul
d like to move some of the db objects to a newly created data file on anothe
r drive. Can anyone give me a hint on how to do this?
Thanks
Andreas
"Sassan Karai" wrote:

> To prevent any further SQL initiated filegrowth on your E: drive you can d
isable automatic filegrow on the primary datafile
> for this particular database all together and just allow the newly created data fi
le on your other partition to grow, and/or add other data files as need arises. This
will prevent SQL from filling up the precious remaining disk space (maybe you are a
lre
ady using more than 90%) on your E: drive before the system halts running out of diskspace.
I occasionally have to do this to balance disk load on terabyte size dbs.
>|||Hi
[url]http://support.microsoft.com/directory/article.asp?ID=kb;en-us;Q224071--[/url
]
Moving SQL Server Databases to a New Location
"Andreas" <Andreas@.discussions.microsoft.com> wrote in message
news:58B7A052-6AAB-4E31-AD6F-A13F29A895CF@.microsoft.com...
> Hi there,
> One of our drives is completely filled up with a database file and so I
would like to move some of the db objects to a newly created data file on
another drive. Can anyone give me a hint on how to do this?[vbcol=seagreen]
> Thanks
> Andreas
>
> "Sassan Karai" wrote:
>
disable automatic filegrow on the primary datafile[vbcol=seagreen]
created data file on your other partition to grow, and/or add other data
files as need arises. This will prevent SQL from filling up the precious
remaining disk space (maybe you are already using more than 90%) on your E:
drive before the system halts running out of diskspace. I occasionally have
to do this to balance disk load on terabyte size dbs.[vbcol=seagreen]

Help retrieving values from 2 similar stored procs

I created one stored proc, then copied it to create another similar stored proc with just some filtering changes in the second. Now I want to obtain the results from both like this:
ProjFee ProjGross DailyRunRate Var1 InHouse1 InHouse2 GrossGoal Group Name PostedAmount
---------
Row# 1 from current stored proc
Row # 2 from called stored proc
so something like this I should get in the end for example when both results are combined:
ProjFee ProjGross DailyRunRate Var1 InHouse1 InHouse2 GrossGoal Group Name PostedAmount
---------
100000 33455 200 300 345555 4455555 5666666 Arizona 56000
103400 22455 900 700 777555 3333555 5444666 Illinois 660000

The first stored proc (CurrentMonthCollections_AZ) attempts to include the results of the second stored proc at the end (CurrentMonthCollections_IL) by calling it and inserting the fields into a temp table.
I was told by Angel to instead convert the second stored proc into a UDF...but having trouble with this.
What is the best approach to get the desired 2 rows back that I need in that fasion?
Here is the code for the 2 stored procs (very long so I will post as as links):
http://www.webfound.net/CurrentMonthCollections_AZ.txt
http://www.webfound.net/CurrentMonthCollections_IL.txt
Look at the end of CurrentMonthCollections_AZ.txt to see where I'm stuck in trying to select the results (ProjFee ProjGross DailyRunRate Var1 InHouse1 InHouse2 GrossGoal Var1 PostedAmount
) from both stored procs.
I don't think UNION is what I want because it will combine stuff...I just want 2 separated rows

"I don't think UNION is what I want because it will combine stuff...I just want 2 separated rows"

Union is what you want:

select 1
union
select 2

returns

1
2

It seems to me that you could parameterize the procedures and get back the two rows that you want, or at least have one procedure and pass in the state you are interested in...that is a lot of proc to deal with though...

|||what about union all ?|||

The difference between the 2 is that union removes duplicates and union all does not

union all is also faster because of that

select 1
union
select 2
union
select 2

select 1
union all
select 2
union all
select 2

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Sorry, I should have been clearer:

UNION ALL doesn't eliminate duplicates, UNION does.

UNION ALL is probably the more correct way to go in this case, but it won't really make a difference with such a small set.

|||in this case, there are no dups so it doesn't matter I guess then.|||so I thought UNION puts it all in one row? I must be wrong...