Matriz SQL

Hi, I have the following problem
In a DB exist this inf:
(I can use many tables... and/or many columns)
-->
1 2 3 4
5 6 7 8
9 a b c
d e f g
<--
In need write a select sentence that move in "circle" all info, for
example
5 1 2 3
9 a 6 4
d b 7 8
e f g cDoes this help:
http://spaces.msn.com/drsql/Blog/cns!80677FB08B3162E4!908.entry
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Alejandro" <jalejandro0211@.gmail.com> wrote in message
news:1148327031.695609.65500@.g10g2000cwb.googlegroups.com...
> Hi, I have the following problem
> In a DB exist this inf:
> (I can use many tables... and/or many columns)
> -->
> 1 2 3 4
> 5 6 7 8
> 9 a b c
> d e f g
> <--
> In need write a select sentence that move in "circle" all info, for
> example
> 5 1 2 3
> 9 a 6 4
> d b 7 8
> e f g c
>|||What is your table structure and how are you getting this output to begin
with?
Post DDL and an explanation of how the original data is generated/selected.
http://www.aspfaq.com/etiquette.asp?id=5006
Also, this sounds like a class assignment. If so, what sort of class is it?
"Alejandro" <jalejandro0211@.gmail.com> wrote in message
news:1148327031.695609.65500@.g10g2000cwb.googlegroups.com...
> Hi, I have the following problem
> In a DB exist this inf:
> (I can use many tables... and/or many columns)
> -->
> 1 2 3 4
> 5 6 7 8
> 9 a b c
> d e f g
> <--
> In need write a select sentence that move in "circle" all info, for
> example
> 5 1 2 3
> 9 a 6 4
> d b 7 8
> e f g c
>|||the DB has not been created by now, the table structure can be anyone,
4 tables, 1 table/ 1 columns, 1 table 16 columns, etc.
Not class assignment.|||Can you explain the situation/application for this logic? It will certainly
help in determining a valid approach. Also, I am very curious as to how
this might be useful in a real world situation.
Anyway, if your matrix will always be 4x4, you can try this table setup. It
involves two tables, one storing the data with its original location in the
matrix (as columns and rows) and a second storing the matrix shift (original
cell and new cell). We use a case statement with an agregate function to
produce the matrix, and we join to the matrix shift table to determine the
new location. To shift the cells more than once we could join to the matrix
shift table as many times as we need to rotate the matrix values. We can
permanently update the values at each position in the matrix if needed.
Lastly, we could change the Value stored in the Data table to a FK pointing
to another table with as many columns as we need. This would still really
only work a column at a time, but you could use any column you wanted in the
matrix.
On SQL 2005, you could probably use the pivot/unpivot functions to
accomplish this.
/*
Create our table/view structure which has data in rows and columns (matrix
format)
*/
CREATE TABLE #tmpData (ColNum integer, RowNum Integer, Value varchar(10)
primary key(ColNum,RowNum))
go
INSERT #tmpData(ColNum,RowNum,Value) VALUES (1,1,'A');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (2,1,'B');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (3,1,'C');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (4,1,'D');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (1,2,'E');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (2,2,'F');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (3,2,'G');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (4,2,'H');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (1,3,'I');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (2,3,'J');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (3,3,'K');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (4,3,'L');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (1,4,'M');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (2,4,'N');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (3,4,'O');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (4,4,'P');
go
/*
Display data in the matrix format
*/
select
max(case when ColNum=1 then Value end) as Col1
,max(case when ColNum=2 then Value end) as Col2
,max(case when ColNum=3 then Value end) as Col3
,max(case when ColNum=4 then Value end) as Col4
from #tmpData
group by RowNum;
/*
Create our matrix mapping, showing how the matrix cells will move
*/
Create table #tmpMatrixShift
(ColNum1 integer,RowNum1 integer,ColNum2 integer,RowNum2 integer);
go
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(1,1,2,1);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(2,1,3,1);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(3,1,4,1);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(4,1,4,2);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(4,2,4,3);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(4,3,4,4);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(4,4,3,4);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(3,4,2,4);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(2,4,1,4);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(1,4,1,3);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(1,3,1,2);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(1,2,1,1);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(2,2,3,2);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(3,2,3,3);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(2,3,2,2);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(3,3,2,3);
go
/*
Join our data table to the matrix mapping table in order to get the new cell
locations
Display data in the matrix format
*/
select
max(case when b.ColNum2=1 then a.Value end) as Col1
,max(case when b.ColNum2=2 then a.Value end) as Col2
,max(case when b.ColNum2=3 then a.Value end) as Col3
,max(case when b.ColNum2=4 then a.Value end) as Col4
from #tmpData a
inner join #tmpMatrixShift b
on a.colnum = b.colnum1
and a.rownum = b.rownum1
group by b.RowNum2;
DROP TABLE #tmpData;
DROP TABLE #tmpMatrixShift;
"Alejandro" <jalejandro0211@.gmail.com> wrote in message
news:1148328455.125472.120050@.j55g2000cwa.googlegroups.com...
> the DB has not been created by now, the table structure can be anyone,
> 4 tables, 1 table/ 1 columns, 1 table 16 columns, etc.
> Not class assignment.
>|||I have a program in the office (very very old) don't have objects of
this program, but this take a sentence select from .ini to execute,
this program transform a values returned (contability) and calculate
some, I can modify that select to --select transformed tables...
=BF?...
Well I do this
CREATE TABLE "MATRIX" ( COL1 VARCHAR(10) NOT NULL ,
COL2 VARCHAR(10) NOT NULL ,
COL3 VARCHAR(10) NOT NULL ,
COL4 VARCHAR(10) NOT NULL );
insert into matrix values ('1','2','3','4');
insert into matrix values ('5','6','7','8');
insert into matrix values ('9','10','11','12');
insert into matrix values ('13','14','15','16');
now?|||Have you tried the approach I posted? As long as you are fixed at 4 columns
and 4 rows in the matrix, I believe it should do what you want. However,
some of the more math-intensive folks may be able to come up with an
algorithm that is more effective.
"Alejandro" <jalejandro0211@.gmail.com> wrote in message
news:1148333584.761203.102270@.j73g2000cwa.googlegroups.com...
I have a program in the office (very very old) don't have objects of
this program, but this take a sentence select from .ini to execute,
this program transform a values returned (contability) and calculate
some, I can modify that select to --select transformed tables...
?...
Well I do this
CREATE TABLE "MATRIX" ( COL1 VARCHAR(10) NOT NULL ,
COL2 VARCHAR(10) NOT NULL ,
COL3 VARCHAR(10) NOT NULL ,
COL4 VARCHAR(10) NOT NULL );
insert into matrix values ('1','2','3','4');
insert into matrix values ('5','6','7','8');
insert into matrix values ('9','10','11','12');
insert into matrix values ('13','14','15','16');
now?|||The link I posted shows a pretty interesting method as well... But it looks
like the OP just wants the answer handed to him without investing any of his
own thought.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:%232JeHYpfGHA.4864@.TK2MSFTNGP05.phx.gbl...
> Have you tried the approach I posted? As long as you are fixed at 4
> columns
> and 4 rows in the matrix, I believe it should do what you want. However,
> some of the more math-intensive folks may be able to come up with an
> algorithm that is more effective.
> "Alejandro" <jalejandro0211@.gmail.com> wrote in message
> news:1148333584.761203.102270@.j73g2000cwa.googlegroups.com...
> I have a program in the office (very very old) don't have objects of
> this program, but this take a sentence select from .ini to execute,
> this program transform a values returned (contability) and calculate
> some, I can modify that select to --select transformed tables...
> ?...
> Well I do this
> CREATE TABLE "MATRIX" ( COL1 VARCHAR(10) NOT NULL ,
> COL2 VARCHAR(10) NOT NULL ,
> COL3 VARCHAR(10) NOT NULL ,
> COL4 VARCHAR(10) NOT NULL );
> insert into matrix values ('1','2','3','4');
> insert into matrix values ('5','6','7','8');
> insert into matrix values ('9','10','11','12');
> insert into matrix values ('13','14','15','16');
> now?
>|||Your link looked more like a cross tab solution, flipping the columns and
rows. I tried to apply it to this situation, but couldn't think of how to
do it, since the cells are being rotated rather than flipped. I am still
trying to think of a valid application for this sort of thing...
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23$qSMGqfGHA.1456@.TK2MSFTNGP04.phx.gbl...
> The link I posted shows a pretty interesting method as well... But it
looks
> like the OP just wants the answer handed to him without investing any of
his
> own thought.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:%232JeHYpfGHA.4864@.TK2MSFTNGP05.phx.gbl...
However,
>

Matrix tables - blank pages print

Hello,

I have a fairly large report with multiple matrix tables. They grow to a fixed width horizontally and may grow to various heights vertically. I have the interactive height set to zero so that it displays on the web page on one screen. When I go to print this report, I am getting a blank page between each page with data. Here are my dimensions:

Report:

height: 15 in

width: 8.5 in

interactive height: 0 in

interactive width: 8.5 in

left margin: .5 in

right margin: .5 in

top margin: .5 in

bottom margin: .5 in

Body:

height: 13.3875 in

width: 6.9 in

Would this problem be due to the fact that my matrix tables span an area greater than a normal page height in design mode even before they grow dynamically? Any suggestions would be appreciated.

Thanks.

Problem solved - it turned out to be hidden fields on the report that were causing the issue. Thanks.sql

Matrix report, date: 1900-01-01 wrong!! Suppose to be empty, how?

Hi

I am making a report in Visual Studio. I’m making a matrix report. I have made a UNION of 2 tables. One of them has a field called Date and the other one does not. But to make a UNION of these 2 tables so that the results are printed in a Matrix I have to have the same fields’ aliases at least. So what I did is that in the second table is:

SELECT ‘ ‘ AS ‘Date’

Right?

Now, in the report the first table gives me the dates in a format:

=Format(Fields!Estimated_Close_Date.Value, "yyyy-MM-dd")

But, the second table is it doesn’t have a date in that field when I run the report it gives me:

1900-01-01

Something I don’t want.

So, how do I make it understand that the second tables date field is suppose to be empty?

Try something like this:

SELECT myDate AS DATE FROM MyTable

UNION ALL

SELECT CASE WHEN myBlankField = ' ' THEN ' ' ELSE ' ' END FROM OtherTable

And choose a field that is NOT a date for myBlankField. This will put blanks in the result set, but you can always just filter those out later.

|||

Thanks for your quick answer, sounds interesting, but I don't know quite where to put your example. Here's the code I have, maybe you know.

SELECT estimatedclosedate AS 'Date', blablabla...

INTO TempTable1

FROM blablabla join blabla and so on

WHERE blablabla AND blabla AND blabla and so on

SELECT ' ' AS 'Date', blablabla... -- This is the date that is troubling me

INTO TempTable2

FROM blablabla join blabla and so on

WHERE blablabla AND blabla AND blabla and so on

SELECT * FROM TempTable1

UNION

SELECT * FROM TempTable2

The TempTable1 gives me Opportunities from a CRM system, the TempTable2 gives me Ongoing Business in the CRM system. Of course, Opportunities have an estimated close date and Ongoing Business has not … so I just want it to show an empty cell in the matrix report.

So, where exactly could I put your suggestion?

|||

try -- select null as 'Date'., xyz........into temptable

Priyank

|||

Worked perfectly!!! Thanks mate!!!|||

can you please mark it as answer...|||

By the way ... do you know anything about my other thread I have here? About putting a tooltip window thing on one of my filters? One of my filters is done that the user can write what ever he wishes to filter on, either exactly or even with a % ... the thing is that I don't want to write on the Prompt: "Tradelane (STO-LAX, or STO%, or %STO) ... it's just to long ... do you happen to know how?

|||Mark it as answer? DONE!!

Matrix Question

I have a view that gives me the following information from some tables.

Cust # Cust Name Date Order Type

001 John Doe 20070401 OR1

002 Miss Doe 20070401 OR2

001 John Doe 20070402 OR2

002 Miss Doe 20070402 OR2

What I would like to do is set up a matrix type report. The report is by the last 6 rolling order dates, with some % columns. So two row examples might be

Cust# Cust Name 20070401 20070402 % (of last x = OR1) %(of last x = OR2)

001 John Doe OR1 OR2 50% 50%

002 Miss Doe OR2 OR2 0% 100%

Another column just like the last two % based off a 3rd type, and finally a total % column that simply adds the 3 columns up (should always equal 100%, just an error check)

First, can this be done with a matrix? I tried a table but it lists cust# twice, but I can be doing it wrong. I am ok doing this within the query if need be, if someone gives me a hint how

Thanks,

When you tried the table, did you set a row group on the customer number?|||Yes, and it will not give me a column for each date in table form.

matrix or crosstab query

Hi friends,

I need to write a crosstab query in oracle like we do in Access. Here are my sample tables.

tblCat
---
sno (primary key)
cat_id

tblDistribution
------
area_id
dist_id (primary key)
dist_name

tblConsumer
-----
serviceno (primary key)
sno (references sno(tblCat))
area_id

I need the display as follows:

categories
----
dist_id & dist_name 1 2 3 4 5 ... (cat_ids...)
-------------------
id, name concatenated count(serviceno).....

ie., I want the No.of service for each category Id and for dist_id and dist_name.... The cat_id should not be duplicated.

Plz help me out of thios query as I need it badly. I've done it using crosstab query wizard in Access. here is the query

TRANSFORM Count(tb_consumer.Service_No) AS CountOfService_No
SELECT tb_distribution.Dist_Id + " - " +tb_distribution.Dist_Name AS Id_Name
FROM (tb_category INNER JOIN tb_consumer ON tb_category.SNO = tb_consumer.SNO) INNER JOIN tb_distribution ON tb_consumer.Area_Id = tb_distribution.Area_Id
GROUP BY tb_distribution.Dist_Id, tb_distribution.Dist_Name
PIVOT tb_category.Cat_Id;

If any one can, plz tell me how to write or convert the same to ORACLE or SQL Server.

Thanks in advance...!The process is very simple.

You need to create a pivot table :

field1 will appear as row header
field2 will appear as column header

Select field1, field2 into pivot from mytable where ....

This gives you a table with all the data you need to work with.

Now ...

Select field1, count(field2) as totalfield2,
sum(case field2 when 'xxxx' then 1 else 0) as colhdr1,
sum(case field2 when 'yyyy' then 1 else 0) as colhdr2,
sum(case field3 when 'zzzz' then 1 else 0) as colhdr3
from pivot
group by field1
order by field1

This should produce the results much like the Access CrossTab query. You can refine it to meet your requirements. I did not fully understand what you were looking for, but hopefully this will help.

you need to recreate the pivot table every time you run!

Matrix inserting spacing in my report

The matrix in my report expands left to right and displaces the other
tables/charts in my report. I've left enough room for it to expand. Is
there a way to tell it not to push the other stuff?Try to move the items above the matrix (which get pushed to the right) into
one containing rectangle. The top/left of the rectangle has to be closer to
left page border than the matrix top/left position. Does it work then for
you?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Cindy Lee" <dangreece@.hotmail.com> wrote in message
news:ubM9QFiXEHA.644@.tk2msftngp13.phx.gbl...
> The matrix in my report expands left to right and displaces the other
> tables/charts in my report. I've left enough room for it to expand. Is
> there a way to tell it not to push the other stuff?
>|||Thanks, putting it in a rectangle works great.
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:%23HnITVkXEHA.1888@.TK2MSFTNGP11.phx.gbl...
> Try to move the items above the matrix (which get pushed to the right)
into
> one containing rectangle. The top/left of the rectangle has to be closer
to
> left page border than the matrix top/left position. Does it work then for
> you?
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Cindy Lee" <dangreece@.hotmail.com> wrote in message
> news:ubM9QFiXEHA.644@.tk2msftngp13.phx.gbl...
> > The matrix in my report expands left to right and displaces the other
> > tables/charts in my report. I've left enough room for it to expand. Is
> > there a way to tell it not to push the other stuff?
> >
> >
>

Matrix Control pushes out Graphs on Report

I have a report with multiple graphs below each other (some bar, some line, some pie)
To the left of each graph, I have a few text boxes and tables tables displaying information about each graph to the right. (fits on A4 portait page)
For lack of being able to display a pic:
Table Graph
_______ ________________________________
| | | |
| | | |
|______ | |_______________________________|
_______ ________________________________
| | | |
| | | |
|______ | |_______________________________|
Right at the bottom of the report, just below the last table/graph combination, I have a simple matrix control.
In the preview pane, all is well, no problem. When I deploy the report to the report server, the matrix control pushes all graphs out for the entire length of the matrix.
Table Blank space Graph
_______ ________________________________
| | | |
| | | |
|______ | |_______________________________|
_______ ________________________________
| | | |
| | | |
|______ | |_______________________________|
_______________________
| |
|______________________|
Matrix /\
The only way I get the report to display correctly is when I specify that the matrix must start on a new page. Unfortunately, the customer wants all on one page.
Any ideas?As items grow vertically, they push items below them.
As they grow horizontally, they push items beside them on the page.
An easy way to prevent this is to make sure your graphs aren't considered to
be to the right of the matrix by grouping the table and graph together in a
rectangle:
--
| -- -- |
| |Table| |Graph| |
| -- -- |
--
--
|Matrix|
--
--
My employer's lawyers require me to say:
"This posting is provided 'AS IS' with no warranties, and confers no
rights."
"Michelle" <Michelle@.discussions.microsoft.com> wrote in message
news:363724F0-D9EE-4BD3-9769-032E79430C6F@.microsoft.com...
> I have a report with multiple graphs below each other (some bar, some
line, some pie)
> To the left of each graph, I have a few text boxes and tables tables
displaying information about each graph to the right. (fits on A4 portait
page)
> For lack of being able to display a pic:
> Table Graph
> _______ ________________________________
> | | | |
> | | | |
> |______ | |_______________________________|
> _______ ________________________________
> | | | |
> | | | |
> |______ | |_______________________________|
>
> Right at the bottom of the report, just below the last table/graph
combination, I have a simple matrix control.
> In the preview pane, all is well, no problem. When I deploy the report to
the report server, the matrix control pushes all graphs out for the entire
length of the matrix.
> Table Blank space Graph
> _______ ________________________________
> | | |
|
> | | |
|
> |______ | |_______________________________|
> _______ ________________________________
> | | |
|
> | | |
|
> |______ | |_______________________________|
> _______________________
> | |
> |______________________|
> Matrix /\
> The only way I get the report to display correctly is when I specify that
the matrix must start on a new page. Unfortunately, the customer wants all
on one page.
> Any ideas?

Math functions

Good Afternoon LAdies & Gents

I was hoping you could assist me with this problem (more of a how-to)
I have a Database with 4 Tables and I need to incert some mathamatical functions to get totals, averages, percentages etc.

here is my problem

I have the following Tables

tblPrice, field(s) mfg_price, discount_price, ltc_price
tblCustomer, field(s) cpny_name, contact, address, phone, date
tblProduct, field(s) crt, server, notebook,
tblInventory, field(s) StockID, cust_num

I need to be able to combine the contact name from tblCustomer, with the product purshased from tblProduct, in addition with calculationsC my Post 2 u'r other Query http://dbforums.com/showthread.php?postid=2986841#post2986841 n start looking @. Grouping SUM() etc,

GW

Materialized view or table function in SQL 2005

Hi,
Please advise whether SQL 2005 has the smiliar function as belows:
Materialized View in Oracle
Materialized Query Tables in DB2
Thank you.
Best Regards,
Lynn
Im not sure since I dont work with either Oracle or DB2 but I believe
indexed view would be something like that.
MC
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:0E95254A-1C42-49D7-BE78-4F64C93CD18A@.microsoft.com...
> Hi,
> Please advise whether SQL 2005 has the smiliar function as belows:
> Materialized View in Oracle
> Materialized Query Tables in DB2
> Thank you.
> --
> Best Regards,
> Lynn
|||The purpose of this function is that the view or table is not repopulated the
data when it's queried. Usually, the data is already stored in that kind of
table when the original table is updated. When this kind of view/table is
queried, the data already exists without parsing the query to original table
and repopulated the table again. Indexed view in SQL 2005 don't have this
function.
Anyway, thanks.
Best Regards,
Lynn
"MC" wrote:

> Im not sure since I dont work with either Oracle or DB2 but I believe
> indexed view would be something like that.
>
> MC
> "Lynn" <Lynn@.discussions.microsoft.com> wrote in message
> news:0E95254A-1C42-49D7-BE78-4F64C93CD18A@.microsoft.com...
>
>
|||indexed views store the aggregate result of a query (group by something
queries) and when the source table change, the view content is updated too.
like a table, you can create an index on it.
for example, if you always want to sum the sales by product, the indexed
view will contains the result of this grouping with an index on the product
column. when the source table is updated the view is updated too at the same
time so the total by product contains the new total.
when a user ask for the total of sales by product (or the sales for a group
of products or all the products) SQL server will use the indexed views
instead of scanning the big source table.
so its exactly the result you looking for.
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:56067DCB-847F-4242-9B64-59463B9B79D6@.microsoft.com...[vbcol=seagreen]
> The purpose of this function is that the view or table is not repopulated
> the
> data when it's queried. Usually, the data is already stored in that kind
> of
> table when the original table is updated. When this kind of view/table is
> queried, the data already exists without parsing the query to original
> table
> and repopulated the table again. Indexed view in SQL 2005 don't have this
> function.
> Anyway, thanks.
> --
> Best Regards,
> Lynn
>
> "MC" wrote:
|||Lynn,
"On commit" materialized views in Oracle are conceptually the same as schema
bound views in SQL Server which have had a unique clustered index applied
prior to any other index. The Oracle materialized view grew out of the
snapshot functionality and has abilities like stale tolerance that do not
appear to be part of SQL Server Indexed Views.
The usage of indexed views can drastically increase the performace of select
queries at the cost insert, update, and delete. The following is a
reasonable introductory article which covers the concept:
http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx
Luke
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:0E95254A-1C42-49D7-BE78-4F64C93CD18A@.microsoft.com...
> Hi,
> Please advise whether SQL 2005 has the smiliar function as belows:
> Materialized View in Oracle
> Materialized Query Tables in DB2
> Thank you.
> --
> Best Regards,
> Lynn

Materialized view or table function in SQL 2005

Hi,
Please advise whether SQL 2005 has the smiliar function as belows:
Materialized View in Oracle
Materialized Query Tables in DB2
Thank you.
--
Best Regards,
LynnIm not sure since I dont work with either Oracle or DB2 but I believe
indexed view would be something like that.
MC
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:0E95254A-1C42-49D7-BE78-4F64C93CD18A@.microsoft.com...
> Hi,
> Please advise whether SQL 2005 has the smiliar function as belows:
> Materialized View in Oracle
> Materialized Query Tables in DB2
> Thank you.
> --
> Best Regards,
> Lynn|||The purpose of this function is that the view or table is not repopulated th
e
data when it's queried. Usually, the data is already stored in that kind of
table when the original table is updated. When this kind of view/table is
queried, the data already exists without parsing the query to original table
and repopulated the table again. Indexed view in SQL 2005 don't have this
function.
Anyway, thanks.
--
Best Regards,
Lynn
"MC" wrote:

> Im not sure since I dont work with either Oracle or DB2 but I believe
> indexed view would be something like that.
>
> MC
> "Lynn" <Lynn@.discussions.microsoft.com> wrote in message
> news:0E95254A-1C42-49D7-BE78-4F64C93CD18A@.microsoft.com...
>
>|||indexed views store the aggregate result of a query (group by something
queries) and when the source table change, the view content is updated too.
like a table, you can create an index on it.
for example, if you always want to sum the sales by product, the indexed
view will contains the result of this grouping with an index on the product
column. when the source table is updated the view is updated too at the same
time so the total by product contains the new total.
when a user ask for the total of sales by product (or the sales for a group
of products or all the products) SQL server will use the indexed views
instead of scanning the big source table.
so its exactly the result you looking for.
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:56067DCB-847F-4242-9B64-59463B9B79D6@.microsoft.com...[vbcol=seagreen]
> The purpose of this function is that the view or table is not repopulated
> the
> data when it's queried. Usually, the data is already stored in that kind
> of
> table when the original table is updated. When this kind of view/table is
> queried, the data already exists without parsing the query to original
> table
> and repopulated the table again. Indexed view in SQL 2005 don't have this
> function.
> Anyway, thanks.
> --
> Best Regards,
> Lynn
>
> "MC" wrote:
>|||Lynn,
"On commit" materialized views in Oracle are conceptually the same as schema
bound views in SQL Server which have had a unique clustered index applied
prior to any other index. The Oracle materialized view grew out of the
snapshot functionality and has abilities like stale tolerance that do not
appear to be part of SQL Server Indexed Views.
The usage of indexed views can drastically increase the performace of select
queries at the cost insert, update, and delete. The following is a
reasonable introductory article which covers the concept:
http://www.microsoft.com/technet/pr...5/impprfiv.mspx
Luke
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:0E95254A-1C42-49D7-BE78-4F64C93CD18A@.microsoft.com...
> Hi,
> Please advise whether SQL 2005 has the smiliar function as belows:
> Materialized View in Oracle
> Materialized Query Tables in DB2
> Thank you.
> --
> Best Regards,
> Lynn

Matching Transactions and Duplicates

Can anyone suggest a good approach to this problem?
I need to match transactions from two tables on several columns. When
I have a match, I need to set a match flag and a match number in each
table. There can be duplicate transactions in either table. If I have
two transactions in one table that match three in the other, I want to
set the match flag and match number on two transactions in each table
and leave the third one blank.
Results:
table 1
--
col A col B match_flag match_number
1 2 Y 1
1 2 Y 2
5 5 Y 3
5 5
table 2
--
col A col B match_flag match_number
1 2 Y 1
1 2 Y 2
1 2
5 5 Y 3>> Can anyone suggest a good approach to this problem?
Please post your table structures & expected results along with clear
explanation of what you are trying to do. For details, refer to :
www.aspfaq.com/5006
Anith|||I have two temp tables loaded with the transactions I want to match
with the other table. They need to match on mid, tran_date, amount,
and card. When there's a match, I want to set the match_flag to 'AM'
and set the match_number to a unique number given to each matching
transaction. It's possible that there can be duplicate transactions,
as in the samples below, where there isn't a corresponding match in the
other table. If there are three in one table and two in the other that
match, I want to set the match_flag and match_number on two from each
table and leave the third from one table null to match on a possible
future load. I'll be matching on several thousands of transactions.
Let me know if you need more information. Thanks.
create table #setl (
mid char(6) not null,
load_number int not null,
detail_number int not null,
batch_number int not null,
tran_date datetime not null,
amount decimal(8,2) not null,
card char(4) not null,
match_flag char(4) null,
match_number int null )
Data:
--
mid load_number detail_number batch_number
543684 23712 1 877
543684 23712 2 877
543684 23712 3 877
tran_date amount card match_flag match_number
2005-09-30 .01 4444 null null
2005-09-30 .01 4444 null null
2005-09-30 .01 4444 null null
create table #debit (
mid char(6) not null,
load_number int not null,
detail_number int not null,
tran_date datetime not null,
amount decimal(8,2) not null,
card char(4) not null,
match_flag char(4) null,
match_number int null )
Data:
--
mid load_number batch_number
543684 658 1
543684 658 1
tran_date amount card match_flag match_number
2005-09-30 .01 4444 null null
2005-09-30 .01 4444 null null
desired results:
#setl
--
mid load_number detail_number batch_number
543684 23712 1 877
543684 23712 2 877
543684 23712 3 877
tran_date amount card match_flag match_number
2005-09-30 .01 4444 AM 1
2005-09-30 .01 4444 AM 2
2005-09-30 .01 4444 null null
#debit
--
mid load_number batch_number
543684 658 1
543684 658 1
tran_date amount card match_flag match_number
2005-09-30 .01 4444 AM 1
2005-09-30 .01 4444 AM 2|||First all, in databases, duplicates nullify logic. In other words, without a
column or set of column to uniquely identify a row in a table, it is
impossible to logically manipulate the data in those tables.
Given the data in your tables, it is impossible to tell if the duplicate
rows represent actually duplicated transactions or simply erroneous entries.
So rather than trying to work with meaningless data, you should consider
eliminating the duplicates in the first place. For starters refer to:
http://support.microsoft.com/kb/139444/en-us
If you are looking for a short term workaround to please your boss, write up
a cursor to loop through the rows and assign the match_flag and match_number
values. However, such a solution will do no good since you will still be
left with redundant data with no keys and constraints.
Anith

Matching Strings In Different Tables Of Same Database

I have a situation where I want to pull strings from one table of a SQL 2000
database and find matches for it in other tables of the same database and
have those values returned. i.e. In one table I have prospects and I want
to match their names to a table that stores the names of prospects turned
into customers. I want to write a query that looks through every entry and
returns a match for each corresponding value (from prospects to customers).
So if "Smith" is found in prospects I want SQL to return "Smith" in
customers with full contact info.

Any pointers on getting started on this is greatly appreciated. Or if you
could just point me to a reference. Obviously, I need to do some kind of
parsing. I just need to be pointed in the right direction.

Thx."Smith" <gsmith@.tbanet.org.nospam> wrote in message
news:RPWdc.410622$B81.6621293@.twister.tampabay.rr. com...
> I have a situation where I want to pull strings from one table of a SQL
2000
> database and find matches for it in other tables of the same database and
> have those values returned. i.e. In one table I have prospects and I
want
> to match their names to a table that stores the names of prospects turned
> into customers. I want to write a query that looks through every entry
and
> returns a match for each corresponding value (from prospects to
customers).
> So if "Smith" is found in prospects I want SQL to return "Smith" in
> customers with full contact info.
> Any pointers on getting started on this is greatly appreciated. Or if you
> could just point me to a reference. Obviously, I need to do some kind of
> parsing. I just need to be pointed in the right direction.
> Thx.

If you are matching name columns, then this may be in the right direction:

select
c.CustomerID,
c.LastName,
c.CompanyName,
c.ContactPhone,
...
from
dbo.Customers c
join dbo.Prospects p
on c.LastName = p.LastName
where
p.LastName = 'Smith'

If this isn't what you're looking for, it would be helpful if you could post
CREATE TABLE statements for the tables, pluse INSERT statements for some
sample data, and the results you expect.

Simon|||"Smith" <gsmith@.tbanet.org.nospam> wrote in message
news:RPWdc.410622$B81.6621293@.twister.tampabay.rr. com...
> I have a situation where I want to pull strings from one table of a SQL
2000
> database and find matches for it in other tables of the same database and
> have those values returned. i.e. In one table I have prospects and I
want
> to match their names to a table that stores the names of prospects turned
> into customers. I want to write a query that looks through every entry
and
> returns a match for each corresponding value (from prospects to
customers).
> So if "Smith" is found in prospects I want SQL to return "Smith" in
> customers with full contact info.
> Any pointers on getting started on this is greatly appreciated. Or if you
> could just point me to a reference. Obviously, I need to do some kind of
> parsing. I just need to be pointed in the right direction.
> Thx.

If you are matching name columns, then this may be in the right direction:

select
c.CustomerID,
c.LastName,
c.CompanyName,
c.ContactPhone,
...
from
dbo.Customers c
join dbo.Prospects p
on c.LastName = p.LastName
where
p.LastName = 'Smith'

If this isn't what you're looking for, it would be helpful if you could post
CREATE TABLE statements for the tables, pluse INSERT statements for some
sample data, and the results you expect.

Simon

Matching on null?

I'm sure I already know the answer to this, but...
I have two tables, one of transactions and another of inventory. The
inventory table is essentially a SUMmed GROUPed version of the transactions.
We do a lot of queries on inventory though, and since it only changes once a
night it makes sense to keep the rollup.
Anyway one of the users noticed that some of the inventory rows are missing
information. After a little poking about, I found that this was due to one of
the columns being null. For instance, here's one of the problem rows from the
transactions...
item id shelf owner group bank sum
15214 NULL300220MS 8.0
That NULL in the second column is perfectly fine, from a data point of view.
Now compare this with the same row in the inventory table...
15214NULL300220MS
So why is it that when I try to insert that 8.0 into the inventory table it
fails to match? If I simply remove the = on that second column it works, but
that is only valid in the case they are NULL, which is the exception.
I know this has something to do with a long debate about NULL in SQL, but I
don't pretend to understand it. What's the solution here? Will this work...
WHERE (p.shelfId= m.shelfIdOR (p.shelfId IS NULL and m.shelfId IS NULL))
NULL is not equal to NULL. So the NULL in your first table is not equal to
any NULL in the second table.
You could do something like what you proposed below, or you could do
something like this:
WHERE COALESCE(p.shelfId, -1) = COALESCE(m.shelfId, -1)
Here I'm assuming that -1 is an invalid shelf # (which may or may not be the
case ... substitute your own value here if it is).
BTW, what are the primary keys on those two tables? It sort of looks like
it should be (item id, shelf) or some combination including those (based
just on what you posted), in which case shelf shouldn't be NULL anyway.
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:6652ED0C-74D0-413E-BF05-DDDC2F4F7E7A@.microsoft.com...
> I'm sure I already know the answer to this, but...
> I have two tables, one of transactions and another of inventory. The
> inventory table is essentially a SUMmed GROUPed version of the
> transactions.
> We do a lot of queries on inventory though, and since it only changes once
> a
> night it makes sense to keep the rollup.
> Anyway one of the users noticed that some of the inventory rows are
> missing
> information. After a little poking about, I found that this was due to one
> of
> the columns being null. For instance, here's one of the problem rows from
> the
> transactions...
> item id shelf owner group bank sum
> 15214 NULL 300 220 MS 8.0
> That NULL in the second column is perfectly fine, from a data point of
> view.
> Now compare this with the same row in the inventory table...
> 15214 NULL 300 220 MS
> So why is it that when I try to insert that 8.0 into the inventory table
> it
> fails to match? If I simply remove the = on that second column it works,
> but
> that is only valid in the case they are NULL, which is the exception.
> I know this has something to do with a long debate about NULL in SQL, but
> I
> don't pretend to understand it. What's the solution here? Will this
> work...
> WHERE (p.shelfId= m.shelfIdOR (p.shelfId IS NULL and m.shelfId IS NULL))
|||"Mike C#" wrote:

> WHERE COALESCE(p.shelfId, -1) = COALESCE(m.shelfId, -1)
Ohhh, I like that. In this particular case the Id is (not my fault!) a
string, so '' would likely be the best solution. Thanks!
Maury

Matching on null?

I'm sure I already know the answer to this, but...
I have two tables, one of transactions and another of inventory. The
inventory table is essentially a SUMmed GROUPed version of the transactions.
We do a lot of queries on inventory though, and since it only changes once a
night it makes sense to keep the rollup.
Anyway one of the users noticed that some of the inventory rows are missing
information. After a little poking about, I found that this was due to one o
f
the columns being null. For instance, here's one of the problem rows from th
e
transactions...
item id shelf owner group bank sum
15214 NULL 300 220 MS 8.0
That NULL in the second column is perfectly fine, from a data point of view.
Now compare this with the same row in the inventory table...
15214 NULL 300 220 MS
So why is it that when I try to insert that 8.0 into the inventory table it
fails to match? If I simply remove the = on that second column it works, but
that is only valid in the case they are NULL, which is the exception.
I know this has something to do with a long debate about NULL in SQL, but I
don't pretend to understand it. What's the solution here? Will this work...
WHERE (p.shelfId= m.shelfIdOR (p.shelfId IS NULL and m.shelfId IS NULL))NULL is not equal to NULL. So the NULL in your first table is not equal to
any NULL in the second table.
You could do something like what you proposed below, or you could do
something like this:
WHERE COALESCE(p.shelfId, -1) = COALESCE(m.shelfId, -1)
Here I'm assuming that -1 is an invalid shelf # (which may or may not be the
case ... substitute your own value here if it is).
BTW, what are the primary keys on those two tables? It sort of looks like
it should be (item id, shelf) or some combination including those (based
just on what you posted), in which case shelf shouldn't be NULL anyway.
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:6652ED0C-74D0-413E-BF05-DDDC2F4F7E7A@.microsoft.com...
> I'm sure I already know the answer to this, but...
> I have two tables, one of transactions and another of inventory. The
> inventory table is essentially a SUMmed GROUPed version of the
> transactions.
> We do a lot of queries on inventory though, and since it only changes once
> a
> night it makes sense to keep the rollup.
> Anyway one of the users noticed that some of the inventory rows are
> missing
> information. After a little poking about, I found that this was due to one
> of
> the columns being null. For instance, here's one of the problem rows from
> the
> transactions...
> item id shelf owner group bank sum
> 15214 NULL 300 220 MS 8.0
> That NULL in the second column is perfectly fine, from a data point of
> view.
> Now compare this with the same row in the inventory table...
> 15214 NULL 300 220 MS
> So why is it that when I try to insert that 8.0 into the inventory table
> it
> fails to match? If I simply remove the = on that second column it works,
> but
> that is only valid in the case they are NULL, which is the exception.
> I know this has something to do with a long debate about NULL in SQL, but
> I
> don't pretend to understand it. What's the solution here? Will this
> work...
> WHERE (p.shelfId= m.shelfIdOR (p.shelfId IS NULL and m.shelfId IS NULL))|||"Mike C#" wrote:

> WHERE COALESCE(p.shelfId, -1) = COALESCE(m.shelfId, -1)
Ohhh, I like that. In this particular case the Id is (not my fault!) a
string, so '' would likely be the best solution. Thanks!
Maury

Matching multiple columns from two tables

Hi all..
I have two tables such as cisco and ciscocom. and i wan to compare each
row of ciscocom with cisco having same column values. i wan to get the
count of matching columns for each row in cisco...
eg:
Ciscocom has columns: Products,fw,ports,sec,des,tput etc and cisco has
columns:fw,ports,sec,des,tput etc. i wan the number of matching colum
for each row in ciscocom. please provide me with the procedure...
Waiting for your response...Kuttan
SELECT cisco .*, ciscocom.*
FROM cisco
FULL OUTER JOIN
ciscocom
ON cisco .c1 = ciscocom.c1
AND cisco .c2 = ciscocom.c2
..
AND cisco .cn = ciscocom.cn
WHERE cisco .key IS NULL OR ciscocom.key IS NULL
"Kuttan" <vvyshak@.gmail.com> wrote in message
news:1141210889.041213.70320@.v46g2000cwv.googlegroups.com...
> Hi all..
> I have two tables such as cisco and ciscocom. and i wan to compare each
> row of ciscocom with cisco having same column values. i wan to get the
> count of matching columns for each row in cisco...
> eg:
> Ciscocom has columns: Products,fw,ports,sec,des,tput etc and cisco has
> columns:fw,ports,sec,des,tput etc. i wan the number of matching colum
> for each row in ciscocom. please provide me with the procedure...
> Waiting for your response...
>|||thanks a lot..
But it doesnt worked.
Actually i wan the columns that matches with each rows of the cisco
from ciscocom...
Anyway thanx a lot for your response...
thank u so much..|||Hope this is what you're looking for
SELECT COUNT (cisco.Products)
FROM cisco INNER JOIN ciscocom
ON
cisco.Products=cisco.Products AND
cisco.fw=cisco.fw AND
cisco.ports=cisco.ports
/*
* If you want to specify a value for a column
*
*/
WHERE cisco.Products='your_value'

Matching multiple columns from two tables

Hi all..
I have two tables such as cisco and ciscocom. and i wan to compare each
row of ciscocom with cisco having same column values. i wan to get the
count of matching columns for each row in cisco...
eg:
Ciscocom has columns: Products,fw,ports,sec,des,tput etc and cisco has
columns:fw,ports,sec,des,tput etc. i wan the number of matching colum
for each row in ciscocom. please provide me with the procedure...
Waiting for your response...On 1 Mar 2006 03:01:21 -0800, Kuttan wrote:

>Hi all..
>I have two tables such as cisco and ciscocom. and i wan to compare each
>row of ciscocom with cisco having same column values. i wan to get the
>count of matching columns for each row in cisco...
>eg:
>Ciscocom has columns: Products,fw,ports,sec,des,tput etc and cisco has
>columns:fw,ports,sec,des,tput etc. i wan the number of matching colum
>for each row in ciscocom. please provide me with the procedure...
>Waiting for your response...
Hi Kuttan,
Not sure if I fully understand your requirements. If the answer below is
not what you're looking for, then please check www.aspfaq.com/5006 to
find out how to post CREATE TABLE statements, INSERT statements and
required results in order to get better help.
SELECT a.KeyColumn,
CASE WHEN a.DataColumn1 = b.DataColumn1 THEN 1 ELSE 0 END
+ CASE WHEN a.DataColumn2 = b.DataColumn2 THEN 1 ELSE 0 END
.....
+ CASE WHEN a.DataColumnN = b.DataColumnN THEN 1 ELSE 0 END
AS MatchCount
FROM Table1 AS a
INNER JOIN Table2 AS b
ON a.KeyColumn = b.KeyColumn
Hugo Kornelis, SQL Server MVP

Matching fileds between two tables

I hope I can explain this a little better. I have two tables that I need
information from. The first table has all but two fields that I need. I am
having two problems. First all I want to do is read the first table take the
part number and check the second table. If it is not there I want print the
information from the table and continue on reading the first table and if it
is there I want to take the invoice and print it with the information from
the first table. The second problem is the part number in the second table
is part of a large field(132 bytes). It is always at the same postion 10
bytes in the field
It sounds like you'll want an outer join. Example:
SELECT T1.col1, T1.col2, T1.part_num, T2.col1, T2.col2
FROM Table1 AS T1
LEFT OUTER JOIN Table2 AS T2
ON T1.part_num = SUBSTRING(T2.large_col, 10, 5) ;
David Portas
SQL Server MVP
"Daniell" <Daniell@.discussions.microsoft.com> wrote in message
news:E1187FC7-9EA8-4EEE-B142-ECFA9807991A@.microsoft.com...
>I hope I can explain this a little better. I have two tables that I need
> information from. The first table has all but two fields that I need. I
> am
> having two problems. First all I want to do is read the first table take
> the
> part number and check the second table. If it is not there I want print
> the
> information from the table and continue on reading the first table and if
> it
> is there I want to take the invoice and print it with the information from
> the first table. The second problem is the part number in the second
> table
> is part of a large field(132 bytes). It is always at the same postion 10
> bytes in the field
|||Thanks I will give that a try.
"David Portas" wrote:

> It sounds like you'll want an outer join. Example:
> SELECT T1.col1, T1.col2, T1.part_num, T2.col1, T2.col2
> FROM Table1 AS T1
> LEFT OUTER JOIN Table2 AS T2
> ON T1.part_num = SUBSTRING(T2.large_col, 10, 5) ;
> --
> David Portas
> SQL Server MVP
> --
> "Daniell" <Daniell@.discussions.microsoft.com> wrote in message
> news:E1187FC7-9EA8-4EEE-B142-ECFA9807991A@.microsoft.com...
>
>

Matching a Views columns to its underlying tables columns

Hello,

Using SQL Server 2000, I'm trying to put together a query that will
tell me the following information about a view:
The View Name
The names of the View's columns
The names of the source tables used in the view
The names of the columns that are used from the source tables

Borrowing code from the VIEW_COLUMN_USAGE view, I've got the code
below, which gives me the View Name, Source Table Name, and Source
Column Name. And I can easily enough get the View columns from the
syscolumns table. The problem is that I haven't figured out how to
link a source column name to a view column name. Any help would be
appreciated.

Gary

select
v_obj.name as ViewName,
t_obj.name as SourceTable,
t_col.name as SourceColumn
from
sysobjects t_obj,
sysobjects v_obj,
sysdepends dep,
syscolumns t_col
where
v_obj.xtype = 'V'
and dep.id = v_obj.id
and dep.depid = t_obj.id
and t_obj.id = t_col.id
and dep.depnumber = t_col.colid
order by
v_obj.name,
t_obj.name,
t_col.namegaryderousse@.yahoo.com (Gary DeRousse) wrote in message news:<9ce1cc62.0311051041.2dd0f428@.posting.google.com>...
> Hello,
> Using SQL Server 2000, I'm trying to put together a query that will
> tell me the following information about a view:
> The View Name
> The names of the View's columns
> The names of the source tables used in the view
> The names of the columns that are used from the source tables
> Borrowing code from the VIEW_COLUMN_USAGE view, I've got the code
> below, which gives me the View Name, Source Table Name, and Source
> Column Name. And I can easily enough get the View columns from the
> syscolumns table. The problem is that I haven't figured out how to
> link a source column name to a view column name. Any help would be
> appreciated.
> Gary
>
> select
> v_obj.name as ViewName,
> t_obj.name as SourceTable,
> t_col.name as SourceColumn
> from
> sysobjects t_obj,
> sysobjects v_obj,
> sysdepends dep,
> syscolumns t_col
> where
> v_obj.xtype = 'V'
> and dep.id = v_obj.id
> and dep.depid = t_obj.id
> and t_obj.id = t_col.id
> and dep.depnumber = t_col.colid
> order by
> v_obj.name,
> t_obj.name,
> t_col.name

I don't believe that this information is available - sysdepends
records that the dependency exists, but not exactly what the
dependency is. The mapping of view to table columns could be 1:N or
M:N (or 1:0, in fact), so I would guess that MS decided that it wasn't
worth the effort to try and capture the detailed column mapping.

Simon|||Simon,

Thanks for the information, even though it wasn't what I wanted to hear.

Gary

sql@.hayes.ch (Simon Hayes) wrote in message news:<60cd0137.0311060041.35542cec@.posting.google.com>...
> garyderousse@.yahoo.com (Gary DeRousse) wrote in message news:<9ce1cc62.0311051041.2dd0f428@.posting.google.com>...
> > Hello,
> > Using SQL Server 2000, I'm trying to put together a query that will
> > tell me the following information about a view:
> > The View Name
> > The names of the View's columns
> > The names of the source tables used in the view
> > The names of the columns that are used from the source tables
> > Borrowing code from the VIEW_COLUMN_USAGE view, I've got the code
> > below, which gives me the View Name, Source Table Name, and Source
> > Column Name. And I can easily enough get the View columns from the
> > syscolumns table. The problem is that I haven't figured out how to
> > link a source column name to a view column name. Any help would be
> > appreciated.
> > Gary
> > select
> > v_obj.name as ViewName,
> > t_obj.name as SourceTable,
> > t_col.name as SourceColumn
> > from
> > sysobjects t_obj,
> > sysobjects v_obj,
> > sysdepends dep,
> > syscolumns t_col
> > where
> > v_obj.xtype = 'V'
> > and dep.id = v_obj.id
> > and dep.depid = t_obj.id
> > and t_obj.id = t_col.id
> > and dep.depnumber = t_col.colid
> > order by
> > v_obj.name,
> > t_obj.name,
> > t_col.name
> I don't believe that this information is available - sysdepends
> records that the dependency exists, but not exactly what the
> dependency is. The mapping of view to table columns could be 1:N or
> M:N (or 1:0, in fact), so I would guess that MS decided that it wasn't
> worth the effort to try and capture the detailed column mapping.
> Simon

Master-Detail Searching, best approach?

Hello, I'm relatively new to using full-text search. I'm working on a =
document management program.
Basically my data it stored in two tables, [Documents], and =
[DocDetails], with a one-to-many relationship. [Documents] contains a =
record for each document stored, and [DocDetails] has many records =
concerning a document, each with an associated text field.
I'm really after the ability to search on a query '"jane" and "doe"', =
where I would be able to pull from [Documents] all documents that have =
matching records in [DocDetails]. My problem comes in how to query =
[DocDetails], some records would have "jane" in them, and some records =
would have "doe" in them, never would you see one record in [DocDetails] =
having both terms in the same text field. (I think this kinda negates =
using a strategy where the query has an "and" or "or" in it.
Maybe there is a way around this, I just haven't thought of. Right now, =
the only thing I can this is to split up my queries into only one word, =
and join the results.
If someone could give me a nudge in the right direction I'd appreciate =
it!
Thanks!,
--Michael
Raterus,
The below code was posted recently in regards to a "Parent - Child"
relationship, and should also work with your "Master - Detail" searching.
Tables:
contact(contact_id, name)
role(role_id, description)
contact_role (contact_id, role_id)
How can I find all contact where the search string is in the contact name
and or roles description? This is the quick and simplest way. You can
rewrite this query using outer-joins. The code is not tested. So expect some
syntax errors.
select * from contact where contactid in
(select contactid from contact_role where contactid in (select key from
containstable(contact,name,'search string') or
roleid in (select key from containstable(role,description,'search
string'))
You should be able to alter the above code to fit your tables. If you have
an alternative approach or additional questions, please post them here..
Regards,
John
"Raterus" <raterus@.spam.org> wrote in message
news:OI#yuv1NEHA.556@.TK2MSFTNGP10.phx.gbl...
Hello, I'm relatively new to using full-text search. I'm working on a
document management program.
Basically my data it stored in two tables, [Documents], and [DocDetails],
with a one-to-many relationship. [Documents] contains a record for each
document stored, and [DocDetails] has many records concerning a document,
each with an associated text field.
I'm really after the ability to search on a query '"jane" and "doe"', where
I would be able to pull from [Documents] all documents that have matching
records in [DocDetails]. My problem comes in how to query [DocDetails],
some records would have "jane" in them, and some records would have "doe" in
them, never would you see one record in [DocDetails] having both terms in
the same text field. (I think this kinda negates using a strategy where the
query has an "and" or "or" in it.
Maybe there is a way around this, I just haven't thought of. Right now, the
only thing I can this is to split up my queries into only one word, and join
the results.
If someone could give me a nudge in the right direction I'd appreciate it!
Thanks!,
--Michael
|||Actually that was the first post I read before I posted, it helped =
initally. I've adapted it a little bit and have come up with this. =
It's a mess, I know it :-) It is working how I want it to though, the =
only problem I forsee, is how do I have different queries for a =
different number of search terms. Using this strategy I'll have to have =
a different query if they search for "jane doe john brown" rather than =
just "jane doe" This is all for an asp.net web application, so it =
wouldn't be that difficult for me to create my own query on the fly and =
send it over, but if there is a better way, I'd love to hear it!
select * from documents where docID in
(
select t1.docID
from (
select docID
from docdetails
where ddID in
(
select [key]
from containstable(docdetails,value,'"jane"')
)
) as t1 inner join=20
(
select docID
from docdetails
where ddID in
(
select [key]
from containstable(docdetails,value,'"doe"')
)
) as t2 on t1.docID =3D t2.docID
)
"John Kane" <jt-kane@.comcast.net> wrote in message =
news:%235axQM2NEHA.3348@.TK2MSFTNGP09.phx.gbl...
> Raterus,
> The below code was posted recently in regards to a "Parent - Child"
> relationship, and should also work with your "Master - Detail" =
searching.
>=20
> Tables:
> contact(contact_id, name)
> role(role_id, description)
> contact_role (contact_id, role_id)
>=20
> How can I find all contact where the search string is in the contact =
name
> and or roles description? This is the quick and simplest way. You can
> rewrite this query using outer-joins. The code is not tested. So =
expect some
> syntax errors.
>=20
> select * from contact where contactid in
> (select contactid from contact_role where contactid in (select key =
from
> containstable(contact,name,'search string') or
> roleid in (select key from containstable(role,description,'search
> string'))
>=20
> You should be able to alter the above code to fit your tables. If you =
have
> an alternative approach or additional questions, please post them =
here..
>=20
> Regards,
> John
>=20
>=20
>=20
>=20
> "Raterus" <raterus@.spam.org> wrote in message
> news:OI#yuv1NEHA.556@.TK2MSFTNGP10.phx.gbl...
> Hello, I'm relatively new to using full-text search. I'm working on a
> document management program.
>=20
> Basically my data it stored in two tables, [Documents], and =
[DocDetails],
> with a one-to-many relationship. [Documents] contains a record for =
each
> document stored, and [DocDetails] has many records concerning a =
document,
> each with an associated text field.
>=20
> I'm really after the ability to search on a query '"jane" and "doe"', =
where
> I would be able to pull from [Documents] all documents that have =
matching
> records in [DocDetails]. My problem comes in how to query =
[DocDetails],
> some records would have "jane" in them, and some records would have =
"doe" in
> them, never would you see one record in [DocDetails] having both terms =
in
> the same text field. (I think this kinda negates using a strategy =
where the
> query has an "and" or "or" in it.
>=20
> Maybe there is a way around this, I just haven't thought of. Right =
now, the
> only thing I can this is to split up my queries into only one word, =
and join
> the results.
>=20
> If someone could give me a nudge in the right direction I'd appreciate =
it!
>=20
> Thanks!,
> --Michael
>=20
>

Master/Detail Query - Got confused

Hallo all,

I’ve two tables (Order_Headers and Order_Details) in SQL Server 2005:

Order_Headers

DocType Order#SubTotalVATTotal

01110018118

01220036236

Order_Details

Order#Line# QTY SKUUnit_Price

111A30

121B70

211C40

221D100

231E60

I need to query both tables and return Master/Detail XML with the following format, ie. Need to return all order lines for a particular order header#. I’m using FOR XML PATH('Document'), ROOT('XML_QueryDocument') but I’m not able to add the “<LineItem>” tag for each order line.

<XML_QueryDocument>

<Document>

<DocType>01</DocumentType>

<OrderNumber>1</OrderNumber>

<SubTotal>100</SubTotal>

<VAT>18</VAT>

<Total>118</Total>

<LineItem>

<LineNumber>1</LineNumber>

<QTY>1</QTY>

<SKU>A</SKU>

<Unit_Price>30</Unit_Price>

</LineItem>

<LineItem>

<LineNumber>2</LineNumber>

<QTY>1</QTY>

<SKU>B</SKU>

<Unit_Price>70</Unit_Price>

</LineItem>

</Document>

</XML_QueryDocument>

Could anyone please shed some light on how to accomplish this? I think I need several SELECT statements but I got confused.

Thanks in advance,

ST

Code Snippet

CREATE TABLE #Order_Headers(DocType int, OrderNum int, SubTotal int, VAT int, Total int)

CREATE TABLE #Order_Detail(OrderNum int, LineNum int, QTY int, SKU char(10), UnitPrice int)

INSERT INTO #Order_Headers SELECT 1, 1, 100, 18, 118

INSERT INTO #Order_Headers SELECT 1, 2, 200, 36, 236

INSERT INTO #Order_Detail SELECT 1, 1, 1, 'A', 30

INSERT INTO #Order_Detail SELECT 2, 1, 1, 'B', 70

INSERT INTO #Order_Detail SELECT 1, 1, 1, 'C', 40

INSERT INTO #Order_Detail SELECT 3, 1, 1, 'D', 100

INSERT INTO #Order_Detail SELECT 4, 1, 1, 'E', 60

SELECT * FROM #Order_Headers oh

FULL OUTER JOIN #Order_Detail od on oh.OrderNum = od.OrderNum

DROP TABLE #Order_Headers

DROP TABLE #Order_Detail

Adamus

|||

Thanks Adamus for your prompt reply!

If I add FOR XML PATH('Document'), ROOT('XML_QueryDocument') at the end of the SELECT statement, I get the following.

It's repeating tags DocType, OrderNum, SubTotal for each order line. This is exactly what I wanted left out. I think the query needs to create a LineItem tag for each order line and show the order header just once either at the top or at the bottom inside a <document> tag. This is where I got confused.

Thanks,

ST

<XML_QueryDocument>
<Document>
<DocType>1</DocType>
<OrderNum>1</OrderNum>
<SubTotal>100</SubTotal>
<VAT>18</VAT>
<Total>118</Total>
<OrderNum>1</OrderNum>
<LineNum>1</LineNum>
<QTY>1</QTY>
<SKU>A </SKU>
<UnitPrice>30</UnitPrice>
</Document>
<Document>
<DocType>1</DocType>
<OrderNum>1</OrderNum>
<SubTotal>100</SubTotal>
<VAT>18</VAT>
<Total>118</Total>
<OrderNum>1</OrderNum>
<LineNum>1</LineNum>
<QTY>1</QTY>
<SKU>C </SKU>
<UnitPrice>40</UnitPrice>
</Document>

.......

|||

Perhaps something like this:

Code Snippet

SELECT
Orders.DocType,
Orders.OrderNum,
Orders.SubTotal,
Orders.VAT,
Orders.Total,
LineItem.LineNum,
LineItem.QTY,
LineItem.SKU,
LineItem.UnitPrice
FROM #Order_Headers Orders
JOIN #Order_Detail LineItem
ON Orders.OrderNum = LineItem.OrderNum
FOR XML AUTO, ELEMENTS

XML_F52E2B61-18A1-11d1-B105-00805F49916B
--
<Orders>
<DocType>1</DocType>
<OrderNum>1</OrderNum>
<SubTotal>100</SubTotal>
<VAT>18</VAT>
<Total>118</Total>
<LineItem>
<LineNum>1</LineNum>
<QTY>1</QTY>
<SKU>A </SKU>
<UnitPrice>30</UnitPrice>
</LineItem>
</Orders>
{etc.}

|||

Thanks Arnie also for a good insight.

I modified the code a little bit as the code above doesn't produce well-formed XML (it shows "XML document cannot contain multiple root level elements"). I've also changed the INSERT on #Order_Detail for order# 1 to have 3 lines

Since I only need to return a document at the time I just added a WHERE clause and that's exactly what I need.

Here's the code just in case someone else is after this also:

CREATE TABLE #Order_Headers(DocType int, OrderNum int, SubTotal int, VAT int, Total int)
CREATE TABLE #Order_Detail(OrderNum int, LineNum int, QTY int, SKU char(10), UnitPrice int)

INSERT INTO #Order_Headers SELECT 1, 1, 100, 18, 118
INSERT INTO #Order_Headers SELECT 1, 2, 200, 36, 236

INSERT INTO #Order_Detail SELECT 1, 1, 10, 'A', 30
INSERT INTO #Order_Detail SELECT 1, 2, 20, 'B', 700
INSERT INTO #Order_Detail SELECT 1, 3, 30, 'C', 4000
INSERT INTO #Order_Detail SELECT 3, 1, 1, 'D', 100
INSERT INTO #Order_Detail SELECT 4, 1, 1, 'E', 60

SELECT
Orders.DocType,
Orders.OrderNum,
Orders.SubTotal,
Orders.VAT,
Orders.Total,
LineItem.LineNum,
LineItem.QTY,
LineItem.SKU,
LineItem.UnitPrice
FROM #Order_Headers Orders
JOIN #Order_Detail LineItem
ON Orders.OrderNum = LineItem.OrderNum
WHERE Orders.OrderNum = 1
FOR XML AUTO, ELEMENTS

DROP TABLE #Order_Headers
DROP TABLE #Order_Detail