query: child of child of child of...: Which are the descendants?

Hi,
I have a table like this:
tblDescendants
ParentID ChildID
A B
A C
B D
D E
So this table contains parent-child relations, but they can go infinite
long.
What I need is a select-query, that returns me for al the 'original'
parents, their relatiosn with their cildren, grandchildren, and further
descendants etc...
So with this records:
ParentID DescendantID
A B
A C
A D (because D is a child of B which is a child of A)
A E (same reason).
How can I do this? I need to use this select in a join. I have a solution
with a temporary table, but I would prefer not to have to make this
temporary table each time I run my query... I would prefer a solution with
an Indexed View.
Does anybody knows how? Any help will be really appreciated!
Thansk a lot in advance,
PieterHi
Take a look at Itzik Ben-Gan's examples
IF object_id('dbo.Employees') IS NOT NULL
DROP TABLE Employees
GO
IF object_id('dbo.ufn_GetSubtree') IS NOT NULL
DROP FUNCTION dbo.ufn_GetSubtree
GO
CREATE TABLE Employees
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
CONSTRAINT PK_Employees_empid PRIMARY KEY(empid),
CONSTRAINT FK_Employees_mgrid_empid
FOREIGN KEY(mgrid)
REFERENCES Employees(empid)
)
CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)
INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)
INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)
INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)
INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)
INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)
INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)
INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)
INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)
INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)
INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)
INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)
INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)
INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)
GO
CREATE FUNCTION dbo.ufn_GetSubtree
(
@.mgrid AS int
)
RETURNS @.tree table
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
lvl int NOT NULL,
path varchar(900) NOT NULL
)
AS
BEGIN
DECLARE @.lvl AS int, @.path AS varchar(900)
SELECT @.lvl = 0, @.path = '.'
INSERT INTO @.tree
SELECT empid, mgrid, empname, salary,
@.lvl, '.' + CAST(empid AS varchar(10)) + '.'
FROM Employees
WHERE empid = @.mgrid
WHILE @.@.ROWCOUNT > 0
BEGIN
SET @.lvl = @.lvl + 1
INSERT INTO @.tree
SELECT E.empid, E.mgrid, E.empname, E.salary,
@.lvl, T.path + CAST(E.empid AS varchar(10)) + '.'
FROM Employees AS E JOIN @.tree AS T
ON E.mgrid = T.empid AND T.lvl = @.lvl - 1
END
RETURN
END
GO
SELECT empid, mgrid, empname, salary
FROM ufn_GetSubtree(3)
GO
/*
empid mgrid empname salary
2 1 Andrew 5000.0000
5 2 Steven 2500.0000
6 2 Michael 2500.0000
*/
--SQL Server 2005--
With TreeCTE (empid, mgrid, empname, salary, lvl, [path]) as
(
select empid, mgrid, empname, salary, 0 as lvl, cast('' as varchar(200))
as [path]
from Employees
union all
select tc.empid, e.mgrid, tc.empname, tc.salary, lvl + 1,
cast([path]+cast(e.empid as varchar(20))+'/' as varchar(200))
from Employees e
inner join TreeCTE tc on e.empid = tc.mgrid
)
select * from TreeCTE where mgrid = 1
----
/*
SELECT REPLICATE (' | ', lvl) + empname AS employee
FROM ufn_GetSubtree(1)
ORDER BY path
*/
/*
employee
--
Nancy
| Andrew
| | Steven
| | Michael
| Janet
| | Robert
| | | David
| | | | James
| | | Ron
| | | Dan
| | Laura
| | Ann
| Margaret
| | Ina
*/
"Pieter" <pieterNOSPAMcoucke@.hotmail.com> wrote in message
news:%23orHcToIHHA.1064@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have a table like this:
> tblDescendants
> ParentID ChildID
> A B
> A C
> B D
> D E
>
> So this table contains parent-child relations, but they can go infinite
> long.
> What I need is a select-query, that returns me for al the 'original'
> parents, their relatiosn with their cildren, grandchildren, and further
> descendants etc...
> So with this records:
> ParentID DescendantID
> A B
> A C
> A D (because D is a child of B which is a child of A)
> A E (same reason).
> How can I do this? I need to use this select in a join. I have a solution
> with a temporary table, but I would prefer not to have to make this
> temporary table each time I run my query... I would prefer a solution with
> an Indexed View.
> Does anybody knows how? Any help will be really appreciated!
> Thansk a lot in advance,
> Pieter
>|||Ok thanks, putting it all in a function works :-)

query: child of child of child of...: Which are the descendants?

Hi,
I have a table like this:
tblDescendants
ParentID ChildID
A B
A C
B D
D E
So this table contains parent-child relations, but they can go infinite
long.
What I need is a select-query, that returns me for al the 'original'
parents, their relatiosn with their cildren, grandchildren, and further
descendants etc...
So with this records:
ParentID DescendantID
A B
A C
A D (because D is a child of B which is a child of A)
A E (same reason).
How can I do this? I need to use this select in a join. I have a solution
with a temporary table, but I would prefer not to have to make this
temporary table each time I run my query... I would prefer a solution with
an Indexed View.
Does anybody knows how? Any help will be really appreciated!
Thansk a lot in advance,
PieterHi
Take a look at Itzik Ben-Gan's examples
IF object_id('dbo.Employees') IS NOT NULL
DROP TABLE Employees
GO
IF object_id('dbo.ufn_GetSubtree') IS NOT NULL
DROP FUNCTION dbo.ufn_GetSubtree
GO
CREATE TABLE Employees
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
CONSTRAINT PK_Employees_empid PRIMARY KEY(empid),
CONSTRAINT FK_Employees_mgrid_empid
FOREIGN KEY(mgrid)
REFERENCES Employees(empid)
)
CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)
INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)
INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)
INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)
INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)
INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)
INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)
INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)
INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)
INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)
INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)
INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)
INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)
INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)
GO
CREATE FUNCTION dbo.ufn_GetSubtree
(
@.mgrid AS int
)
RETURNS @.tree table
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
lvl int NOT NULL,
path varchar(900) NOT NULL
)
AS
BEGIN
DECLARE @.lvl AS int, @.path AS varchar(900)
SELECT @.lvl = 0, @.path = '.'
INSERT INTO @.tree
SELECT empid, mgrid, empname, salary,
@.lvl, '.' + CAST(empid AS varchar(10)) + '.'
FROM Employees
WHERE empid = @.mgrid
WHILE @.@.ROWCOUNT > 0
BEGIN
SET @.lvl = @.lvl + 1
INSERT INTO @.tree
SELECT E.empid, E.mgrid, E.empname, E.salary,
@.lvl, T.path + CAST(E.empid AS varchar(10)) + '.'
FROM Employees AS E JOIN @.tree AS T
ON E.mgrid = T.empid AND T.lvl = @.lvl - 1
END
RETURN
END
GO
SELECT empid, mgrid, empname, salary
FROM ufn_GetSubtree(3)
GO
/*
empid mgrid empname salary
2 1 Andrew 5000.0000
5 2 Steven 2500.0000
6 2 Michael 2500.0000
*/
--SQL Server 2005--
With TreeCTE (empid, mgrid, empname, salary, lvl, [path]) as
(
select empid, mgrid, empname, salary, 0 as lvl, cast('' as varchar(200))
as [path]
from Employees
union all
select tc.empid, e.mgrid, tc.empname, tc.salary, lvl + 1,
cast([path]+cast(e.empid as varchar(20))+'/' as varchar(200))
from Employees e
inner join TreeCTE tc on e.empid = tc.mgrid
)
select * from TreeCTE where mgrid = 1
----
/*
SELECT REPLICATE (' | ', lvl) + empname AS employee
FROM ufn_GetSubtree(1)
ORDER BY path
*/
/*
employee
--
Nancy
| Andrew
| | Steven
| | Michael
| Janet
| | Robert
| | | David
| | | | James
| | | Ron
| | | Dan
| | Laura
| | Ann
| Margaret
| | Ina
*/
"Pieter" <pieterNOSPAMcoucke@.hotmail.com> wrote in message
news:%23orHcToIHHA.1064@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have a table like this:
> tblDescendants
> ParentID ChildID
> A B
> A C
> B D
> D E
>
> So this table contains parent-child relations, but they can go infinite
> long.
> What I need is a select-query, that returns me for al the 'original'
> parents, their relatiosn with their cildren, grandchildren, and further
> descendants etc...
> So with this records:
> ParentID DescendantID
> A B
> A C
> A D (because D is a child of B which is a child of A)
> A E (same reason).
> How can I do this? I need to use this select in a join. I have a solution
> with a temporary table, but I would prefer not to have to make this
> temporary table each time I run my query... I would prefer a solution with
> an Indexed View.
> Does anybody knows how? Any help will be really appreciated!
> Thansk a lot in advance,
> Pieter
>|||Ok thanks, putting it all in a function works :-)sql

Query with MAX funtion

Hello,
I have 2 tables.
one table contains employees with their salary.
second table contains department.
I can select the highest salary in each department but
I would like to select the name of the employee who makes the highest salary in each department.
Can you help me to make this query ?
Thanks,
Aur=E9lieThis is a multi-part message in MIME format.
--=_NextPart_000_033B_01C37935.CF55CC40
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Try:
select
d.DeptName
, e.EmployeeName
from
Depts as d
join
Employees as e on e.Dept =3D d.Dept
where
e.Salary =3D
(
select
max (e2.Salary)
from
Employees as e2
where
e2.Dept =3D d.Dept
)
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Aur=E9lie" <av@.lbn.fr> wrote in message =news:294501c37955$7b0322d0$a601280a@.phx.gbl...
Hello,
I have 2 tables.
one table contains employees with their salary.
second table contains department.
I can select the highest salary in each department but
I would like to select the name of the employee who makes the highest salary in each department.
Can you help me to make this query ?
Thanks,
Aur=E9lie
--=_NextPart_000_033B_01C37935.CF55CC40
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Try:
select
=d.DeptName
, e.EmployeeName
from
Depts as =d
join
Employees as =e on e.Dept =3D d.Dept
where
e.Salary ==3D
(
=select
= max (e2.Salary)
=from
= Employees as e2
=where
= e2.Dept =3D d.Dept
)
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Aur=E9lie" =wrote in message news:294501c37955$7b=0322d0$a601280a@.phx.gbl...Hello,I have 2 tables.one table contains employees with their =salary.second table contains department.I can select the highest salary in each =department butI would like to select the name of the employee who makes the =highest salary in each department.Can you help me to make this query ?Thanks,Aur=E9lie

---

--=_NextPart_000_033B_01C37935.CF55CC40--|||This is a multi-part message in MIME format.
--=_NextPart_000_0016_01C37937.E00063F0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Tom's Query will work provided that the Salary field is declared as a =money or decimal field. I have seen many databases where (for =portability and the capability of storing very large numbers) that money =field are defined as "float" columns. If this is the case you should =use a 'delta' value when comparing the salaries - because of computer =rounding errors you should never directly compare floats, doubles, =anything with a sliding decimal...so the comparison would be:
where abs(e.salary - (select max (e2.Salary) from Employees as e2 where
e2.Dept =3D d.Dept) ) < 0.0001
Bruce Carson
Director of Technology
Edgewater Technology
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:%23QjHCdVeDHA.2680@.TK2MSFTNGP11.phx.gbl...
Try:
select
d.DeptName
, e.EmployeeName
from
Depts as d
join
Employees as e on e.Dept =3D d.Dept
where
e.Salary =3D
(
select
max (e2.Salary)
from
Employees as e2
where
e2.Dept =3D d.Dept
)
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Aur=E9lie" <av@.lbn.fr> wrote in message =news:294501c37955$7b0322d0$a601280a@.phx.gbl...
Hello,
I have 2 tables.
one table contains employees with their salary.
second table contains department.
I can select the highest salary in each department but
I would like to select the name of the employee who makes the highest salary in each department.
Can you help me to make this query ?
Thanks,
Aur=E9lie
--=_NextPart_000_0016_01C37937.E00063F0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Tom's Query will work provided that the =Salary field is declared as a money or decimal field. I have seen many =databases where (for portability and the capability of storing very large numbers) =that money field are defined as "float" columns. If this is the case =you should use a 'delta' value when comparing the salaries - because of computer =rounding errors you should never directly compare floats, doubles, anything with =a sliding decimal...so the comparison would be:
where abs(e.salary - (select max (e2.Salary) from Employees as =e2 where
= e2.Dept =3D d.Dept) ) < 0.0001
Bruce Carson
Director of Technology
Edgewater =Technology
"Tom Moreau" = wrote in message news:%23QjHCdVeDHA.=2680@.TK2MSFTNGP11.phx.gbl...
Try:

select
d.DeptName
, e.EmployeeName
from
Depts as d
join
Employees =as e on e.Dept =3D d.Dept
where
e.Salary =3D
(
=select
= max (e2.Salary)
=from
= Employees as e2
=where
= e2.Dept =3D d.Dept
)
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"Aur=E9lie" =wrote in message news:294501c37955$7b=0322d0$a601280a@.phx.gbl...Hello,I have 2 tables.one table contains employees with their =salary.second table contains department.I can select the highest salary in each department butI would like to select the name of the employee who =makes the highest salary in each department.Can you help me to =make this query ?Thanks,Aur=E9lie

---

--=_NextPart_000_0016_01C37937.E00063F0--|||This is a multi-part message in MIME format.
--=_NextPart_000_037D_01C37939.390DAC40
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Good point. I just assumed it was stored as money. When you assume, =...
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Bruce A. Carson" <bcarson@.asgoth.com> wrote in message =news:eBBfolVeDHA.3576@.tk2msftngp13.phx.gbl...
Tom's Query will work provided that the Salary field is declared as a =money or decimal field. I have seen many databases where (for =portability and the capability of storing very large numbers) that money =field are defined as "float" columns. If this is the case you should =use a 'delta' value when comparing the salaries - because of computer =rounding errors you should never directly compare floats, doubles, =anything with a sliding decimal...so the comparison would be:
where abs(e.salary - (select max (e2.Salary) from Employees as e2 where e2.Dept =3D d.Dept) ) < 0.0001
Bruce Carson
Director of Technology
Edgewater Technology
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:%23QjHCdVeDHA.2680@.TK2MSFTNGP11.phx.gbl...
Try:
select
d.DeptName
, e.EmployeeName
from
Depts as d
join
Employees as e on e.Dept =3D d.Dept
where
e.Salary =3D
(
select
max (e2.Salary)
from
Employees as e2
where
e2.Dept =3D d.Dept
)
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Aur=E9lie" <av@.lbn.fr> wrote in message =news:294501c37955$7b0322d0$a601280a@.phx.gbl...
Hello,
I have 2 tables.
one table contains employees with their salary.
second table contains department.
I can select the highest salary in each department but
I would like to select the name of the employee who makes the highest salary in each department.
Can you help me to make this query ?
Thanks,
Aur=E9lie
--=_NextPart_000_037D_01C37939.390DAC40
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Good point. I just assumed it =was stored as money. When you assume, ...
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Bruce A. Carson" wrote in =message news:eBBfolVeDHA.3576=@.tk2msftngp13.phx.gbl...
Tom's Query will work provided that the =Salary field is declared as a money or decimal field. I have seen many =databases where (for portability and the capability of storing very large numbers) =that money field are defined as "float" columns. If this is the case =you should use a 'delta' value when comparing the salaries - because of computer =rounding errors you should never directly compare floats, doubles, anything with =a sliding decimal...so the comparison would be:
where abs(e.salary - (select max (e2.Salary) from Employees as =e2 where = e2.Dept =3D d.Dept) ) < 0.0001
Bruce Carson
Director of Technology
Edgewater =Technology
"Tom Moreau" = wrote in message news:%23QjHCdVeDHA.=2680@.TK2MSFTNGP11.phx.gbl...
Try:

select
d.DeptName
, e.EmployeeName
from
Depts as d
join
Employees =as e on e.Dept =3D d.Dept
where
e.Salary =3D
(
=select
= max (e2.Salary)
=from
= Employees as e2
=where
= e2.Dept =3D d.Dept
)
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"Aur=E9lie" =wrote in message news:294501c37955$7b=0322d0$a601280a@.phx.gbl...Hello,I have 2 tables.one table contains employees with their =salary.second table contains department.I can select the highest salary in each department butI would like to select the name of the employee who =makes the highest salary in each department.Can you help me to =make this query ?Thanks,Aur=E9lie

---

--=_NextPart_000_037D_01C37939.390DAC40--

Query VS Stored Procedure problem

Hello.

I am having a strange problem with SQL Server 2005. I have written a SELECT query that contains unions, joins and group functions. when the sql query is run using t-sql statements, the query completed execution in about 10-12 seconds. When the same query is written in a stored procedure without making any changes in the SELECT query (only adding a date parameter), it does not generate any result.

I waited for about 1 hour for the stored procedure to give me the result but it did not. Can anyone help me out with this problem?

Thanks in advance.

Raza:

Please provide a listing of your stored procedure.

Dave

|||

Definitely you need to provide the query, but also how much data is involved. Definitely look at the plans of the query (post them here too using set showplan_text on to get the plan) for clues as to what might be happening.

|||The data involved is huge (millions of rows) but regardless the sql statements copied from the proc and written in query window returns result in 5 - 7 seconds and when the same proc is executed it doesnot return any result.

I have MS Sql 2005 64 bit Enterprise Edition with SP1 installed.

here is the query.

SELECT sim.DEALER_CODE, sim.TRANSACTION_STAMP, sid.PRODUCT_CODE, dbo.REFERENCE_PRODUCT_CODES.SALE_PRICE AS UNIT_PRICE, sid.AMOUNT, sid.QUANTITY, sim.INVOICE_NUMBER, dbo.REFERENCE_TRANSACTION_TYPES.DESCRIPTION AS TRANSACTION_TYPE, sim.TRANSACTION_USER, icl.LOCATION_CODE, icl.REGION_NAME, icl.COUNTRY, (CASE WHEN dbo.REFERENCE_DEALER_CODES.DEALER_TYPE = 'I' THEN 'D' WHEN dbo.REFERENCE_DEALER_CODES.DEALER_TYPE = 'N' THEN 'D' ELSE 'E' END) AS DEALER_TYPE, sim.PARAMETER_1 AS ITEM_SERIAL FROM dbo.SALES_INVOICE_DETAIL AS sid INNER JOIN dbo.SALES_INVOICE_MASTER AS sim ON sid.INVOICE_NUMBER = sim.INVOICE_NUMBER INNER JOIN dbo.VIEW_USER_INFORMATION_COUNTRY_LEVEL AS icl ON sim.TRANSACTION_USER = icl.USER_ID INNER JOIN dbo.REFERENCE_TRANSACTION_TYPES ON sim.TRANSACTION_TYPE = dbo.REFERENCE_TRANSACTION_TYPES.TRANSACTION_TYPE INNER JOIN dbo.REFERENCE_PRODUCT_CODES ON sid.PRODUCT_CODE = dbo.REFERENCE_PRODUCT_CODES.PRODUCT_CODE LEFT OUTER JOIN dbo.REFERENCE_DEALER_CODES ON sim.DEALER_CODE = dbo.REFERENCE_DEALER_CODES.DEALER_CODE WHERE (sim.TRANSACTION_STAMP BETWEEN CONVERT(CHAR(10), GETDATE() - 1, 101) AND CONVERT(CHAR(10), GETDATE() - 1, 101) + '

23:59:59') AND (NOT (dbo.REFERENCE_TRANSACTION_TYPES.TRANSACTION_TYPE IN ('2', '7')))

Thanks

|||

Some suggestions to identify the problem:

1- Limit the number of rows returned (maybe by adding an extra predicate) and see if the sproc returns any results at all. If the sproc is still hanging, it maybe an urelated issue with the query.

2- If the sproc returns results, try to open a cursor on the original query and print messages after every fetch to verify the query is returning results inside the proc.

Thanks.

|||

What do you mean "doesn't return any result" Do you mean it takes forever, or it returns no rows?

So the procedure is:

create procedure procName
as

<your query>

go

Or is there anything else? I don't know why that wouldn't use as good of a plan as an ad hoc query...especially if you recompile the procedure.

|||You are using between to compare a string. This NEVER works.

Try this:

WHERE (sim.TRANSACTION_STAMP BETWEEN CAST(CONVERT(CHAR(10), GETDATE() - 1, 101) AS DATETIME) AND CAST(CONVERT(CHAR(10), GETDATE() - 1, 101) + '

23:59:59') AS DATETIME) AND (NOT (dbo.REFERENCE_TRANSACTION_TYPES.TRANSACTION_TYPE IN ('2', '7')))|||

obviously probelm is with the date. remove the date from SP and verify.

Can you explain what is your requierment on date field

|||Could you verify whether there any records which satisfies the date condition mentioned in the where clause?|||

Tom Phillips wrote:

You are using between to compare a string. This NEVER works.

Try this:

WHERE (sim.TRANSACTION_STAMP BETWEEN CAST(CONVERT(CHAR(10), GETDATE() - 1, 101) AS DATETIME) AND CAST(CONVERT(CHAR(10), GETDATE() - 1, 101) + ' 23:59:59') AS DATETIME) AND (NOT (dbo.REFERENCE_TRANSACTION_TYPES.TRANSACTION_TYPE IN ('2', '7')))

Tom,

That is not true. BETWEEN works with string values, the problem is that it is more difficult to anticipate the results, and a greater reliance upon good indexing. For example, try these two queries:

USE Northwind
GO

SELECT
EmployeeID,
LastName,
FirstName
FROM Employees
WHERE LastName BETWEEN 'a' AND 'f'

SELECT
OrderID,
OrderDate
FROM Orders
WHERE OrderDate BETWEEN cast( convert( char(10), getdate() - 3850, 101 ) AS datetime )
AND ( cast( convert( char(10), getdate() - 3800, 101 ) AS datetime ) + ' 23:59:59' )

|||If the query works as an ad hoc call, but not in a procedure, it is unlikely that there is anything
"wrong" with the query itself. There is something missing that needs to be supplied before we can make a judgment. Maybe a param or something... Or an IF...THEN around the query. We need to see the entire proc...|||

RazaRana wrote:

The data involved is huge (millions of rows) but regardless the sql statements copied from the proc and written in query window returns result in 5 - 7 seconds and when the same proc is executed it doesnot return any result.

...

WHERE (sim.TRANSACTION_STAMP BETWEEN CONVERT(CHAR(10), GETDATE() - 1, 101) AND CONVERT(CHAR(10), GETDATE() - 1, 101) + ' 23:59:59') AND (NOT (dbo.REFERENCE_TRANSACTION_TYPES.TRANSACTION_TYPE IN ('2', '7')))

Thanks

I don't think that this WHERE clause is correct and will work to return data. I suggest the following alteration:

WHERE ( sim.TRANSACTION_STAMP BETWEEN convert( char(10), getdate() - 1, 101 )
AND convert( char(10), getdate() - 1, 101 ) + ' 23:59:59' )
AND dbo.REFERENCE_TRANSACTION_TYPES.TRANSACTION_TYPE NOT IN ( '2', '7' )
)

|||You are correct, it technically "works". I should have said "NEVER gives the expected results". :)

This is the 3rd time in 3 months I have seen someone trying to do this exact same WHERE clause with BETWEEN a date and 2 strings.|||Hi

Sorry for late reply. The query works perfectly fine and returns results (upto 15,000 rows) in less than 10 secs.

I use the same query in sproc, only the date is passed as a parameter. The sproc takes forever, i waited for 1 hr and 25 minutes and still no results.

The interesting thing is that i killed a few locks created on the tempdb and ran the sproc at midnight and it returned the results in about 10 secs.

Thanx

query using commas

Hi,

I have table Article(ID,Title,FAID)

I need a query that will select all the Article.ID records where the FAID contains the number of the article ID

For exapmle, Article Table content is:

1, "Title1","2,6"2, "Title2",""3, "Title3","6,1"

6, "Title6","2"

Lets say I want to get all titles of article ID 1. I am going to its FAID which is "2,6"

So the query will return : "Title2", "Title6"

Can you advice how to write this? I can do a walk around solution where I will open a new table nameFAbut I rather not to.

You could use the CHARINDEX function in T-SQL. Something like this:

WHERE CHARINDEX(FAID, ID) > 0

This is untested, but will probably work for you.

BUT, this looks an awful lot like a non-normalized table, since you have multiple items in the FAID field. You're likely to be able to write much cleaner and more efficient queries if you normalize it.

Don

|||

the FAID fiels containd a related article IDs. this mean I will use this field only in one query. the one that I am building right now.

although at the moment I have normlized table ReleatedArticle (FAID,ArticleID). for each FAID, I have multiple ArticleIDs, and this is normlize.

do you think I should leave it like this normlized, and not to change to the CHARINDEX solution?

I don't like to open a new table when it seems like unneccessary one. please advice.

|||

Well, the normalization rules are not absolute, although there are people who treat them as such. Sometimes there are good reasons to break the rules. If you truly will not use the information in any other way, in any other queries, this might possibly be efficient for you. But if you ever find yourself writing any convoluted T-SQL or client code to work with the related article ID data, consider putting it into a separate table.

Don

query to xml question

I have a table that contains 3 columns SiteID, Results and date. the table has 4 rows. I want to query the table and end up with 1 row that combines all the field into in the Results Column.

so in table form it looks like

627 test 3/3/7
627 bob 3/3/7
627 tom 3/9/7
627 rob 3/8/7

I want the resulting query to bring back one row:

test,bob,tom,rob

the following query will do 90% of what I want,

SELECT test +','
FROM #temp1
FORXMLPATH('')

BUT I cannot figure out how to provide a column name for the query result. instead I appear to get a guid of XML_F52E2B61-18A1-11d1-B105-00805F49916B

Q - is there a way to name the column, or is there a different way to create this query result without using XML?

I am us

Jim:

The best way is to make your select statement into a "derived table" or a correlated subquery; For example:

create table #temp1
( SiteID integer,
Results varchar(10),
date datetime
)
insert into #temp1 values (627, 'billy joe', '3/3/7')
insert into #temp1 values (627, 'bob', '3/3/7')
insert into #temp1 values (627, 'tom', '3/9/7')
insert into #temp1 values (627, 'rob', '3/8/7')

select distinct
siteId,
replace(replace(
( select replace (x.results, ' ', '~') as [data()]
from #temp1 x
where x.siteId = x.siteId
order by date
for xml path ('')
), ' ', ','), '~', ' ') as dataLabel
from #temp1 a

-- siteId dataLabel
-- --
-- 627 billy joe,bob,rob,tom

go

drop table #temp1
go

|||

selectcast((SELECT test +','FROM #temp1 FORXMLPATH(''))asvarchar(max))as YourName

|||I think I like Konstantin's better.|||

Thanks to both of you for the quick reply, they both work, but think I will use Konstantin's

|||

Actually I now have a different problem:

I am using

select siteid, Cast((SELECT Anomalies+ ',' FROM #temp1 FOR XML PATH('')) as varchar(max) ) as Anomaly from #temp1

this does work, sort of.... But it creates the result for all records in the table. I need it to create a seperate record for each siteid, otherwise all the resulting data is the same for all siteids?

any ideas?

|||Just add filter to subquery:
select siteid, Cast((SELECT Anomalies+ ',' FROM #temp1 where siteid=t.siteid FOR XML PATH('')) as varchar(max) ) as Anomaly from #temp1 t
sql

Query to return Xml column data as relational table - how?

Greetings,

I've just begun storing Xml in a SQL Server Xml column. I've got a column that contains something like this 3 element example:

<document xmlns="http://www.lotus.com/dxl" version="7.0" maintenanceversion="2.0" replicaid="852571B800111CE3" form="data">

<item name="OriginalModTime">

<datetime dst="true">20060813T135156,51-05</datetime>

</item>

<item name="Genius_Status_1">

<text>1</text>

</item>

<item name="archive_date">

<datetime>20040331</datetime>

</item>

</document>

I need to write a query that will return a combination of attribute and data values at different "levels". This is what I must achieve as query results:

Name Type Value

- - --

OriginalModTime datetime 20060813T135156,51-05

Genius_Status_1 text 1

archive_date datetime 20040331

Each resultset row must correspond to one "item" element.

Can someone give me an idea of what this query should look like? I'm trying to puzzle my way through xquery...

Thanks,

BCB

I've got everything but the "Type" column figured out. This query returns "Name" and "Value". Can someone suggest the missing logic to return the "Type" value?

Thanks... BCB

SELECT TOP 1000

Item.value('./@.name', 'NVARCHAR(MAX)') as [Notes Field],

Item.value('.', 'NVARCHAR(MAX)') as Value

FROM

NotesAudit

CROSS APPLY

XmlBlob.nodes('declare namespace MI="http://www.lotus.com/dxl"; /MIocument/MI:item') AS T1(Item)

|||

This is the working query:

SELECT TOP 1000

Item.value('./@.name', 'NVARCHAR(MAX)') AS [Notes Field Name],

Item.value('local-name(./*[1])', 'NVARCHAR(256)') AS [Data Type],

Item.value('.', 'NVARCHAR(MAX)') AS [Field Value]

FROM

NotesAudit

CROSS APPLY

XmlBlob.nodes('declare namespace MI="http://www.lotus.com/dxl"; /MIocument/MI:item') AS T1(Item)

sql

Query to return Xml column data as relational table - how?

Greetings,

I've just begun storing Xml in a SQL Server Xml column. I've got a column that contains something like this 3 element example:

<document xmlns="http://www.lotus.com/dxl" version="7.0" maintenanceversion="2.0" replicaid="852571B800111CE3" form="data">

<item name="OriginalModTime">

<datetime dst="true">20060813T135156,51-05</datetime>

</item>

<item name="Genius_Status_1">

<text>1</text>

</item>

<item name="archive_date">

<datetime>20040331</datetime>

</item>

</document>

I need to write a query that will return a combination of attribute and data values at different "levels". This is what I must achieve as query results:

Name Type Value

- - --

OriginalModTime datetime 20060813T135156,51-05

Genius_Status_1 text 1

archive_date datetime 20040331

Each resultset row must correspond to one "item" element.

Can someone give me an idea of what this query should look like? I'm trying to puzzle my way through xquery...

Thanks,

BCB

I've got everything but the "Type" column figured out. This query returns "Name" and "Value". Can someone suggest the missing logic to return the "Type" value?

Thanks... BCB

SELECT TOP 1000

Item.value('./@.name', 'NVARCHAR(MAX)') as [Notes Field],

Item.value('.', 'NVARCHAR(MAX)') as Value

FROM

NotesAudit

CROSS APPLY

XmlBlob.nodes('declare namespace MI="http://www.lotus.com/dxl"; /MIocument/MI:item') AS T1(Item)

|||

This is the working query:

SELECT TOP 1000

Item.value('./@.name', 'NVARCHAR(MAX)') AS [Notes Field Name],

Item.value('local-name(./*[1])', 'NVARCHAR(256)') AS [Data Type],

Item.value('.', 'NVARCHAR(MAX)') AS [Field Value]

FROM

NotesAudit

CROSS APPLY

XmlBlob.nodes('declare namespace MI="http://www.lotus.com/dxl"; /MIocument/MI:item') AS T1(Item)

query to remove HTML tags

Hi,
I have a table with 3 fields. One of the fields contains HTML tags which I
want to get rid of. Any quick way to do this?
thanksYou can use function REPLACE or STUFF to replace characteres not wanted. See
BOL for more information.
AMB
"Rafael Chemtob" wrote:

> Hi,
> I have a table with 3 fields. One of the fields contains HTML tags which
I
> want to get rid of. Any quick way to do this?
> thanks
>
>|||See if this helps:
http://groups.google.ca/groups?selm...FTNGP09.phx.gbl
Anith

Query the Full-Text Words List?

Hi Guys. I’m doing searches in a DB and am using the Full-Text Indexing.

CONTAINS() will only process strings (words!) three letters or more in length. Even though it can do substring searching, it will only do this if it recognises the parameter as a word.

So take this code for example:

Dim mySQLStatement As String = "SELECT TOP 100 Description, Price, Stock FROM Products WHERE "

x = Split(strString, " ")

For i = 0 To x.GetUpperBound(0)

If x(i).Length > 2 Then

mySQLStatement &= " CONTAINS(Description, '*" & x(i) & "*') AND "

ElseIf x(i).Length = 1 Or x(i).Length = 2 Then

mySQLStatement &= " Description LIKE '%" & x(i) & "%' AND "

End If

Next

If mySQLStatement.EndsWith(" AND ") Then mySQLStatement = Left(mySQLStatement, Len(mySQLStatement) - 5)

mySQLStatement &= " ORDER BY PRICE DESC"

dolog(mySQLStatement)

mySQLCommand = New SqlCommand(mySQLStatement, mySQLConnection)

mySQLAdapter = New SqlDataAdapter(mySQLCommand)

myDataSet = New DataSet : mySQLAdapter.Fill(myDataSet)

That is code I’m using in a small proof-of-concept application I’m writing – so I’m aware I can use StringBuilders and should be using SPs and all that jazz.

It will process the queries people type in, so if the person were to type in “sql server”, it would perform all of the following queries: (this is by design by the way)

SELECT TOP 100 Description, Price, Stock FROM Products WHERE Description LIKE '%s%' ORDER BY PRICE DESC

SELECT TOP 100 Description, Price, Stock FROM Products WHERE Description LIKE '%sq%' ORDER BY PRICE DESC

SELECT TOP 100 Description, Price, Stock FROM Products WHERE CONTAINS(Description, '*sql*') ORDER BY PRICE DESC

SELECT TOP 100 Description, Price, Stock FROM Products WHERE CONTAINS(Description, '*sql*') ORDER BY PRICE DESC

SELECT TOP 100 Description, Price, Stock FROM Products WHERE CONTAINS(Description, '*sql*') AND Description LIKE '%s%' ORDER BY PRICE DESC

SELECT TOP 100 Description, Price, Stock FROM Products WHERE CONTAINS(Description, '*sql*') AND Description LIKE '%se%' ORDER BY PRICE DESC

SELECT TOP 100 Description, Price, Stock FROM Products WHERE CONTAINS(Description, '*sql*') AND CONTAINS(Description, '*ser*') ORDER BY PRICE DESC

SELECT TOP 100 Description, Price, Stock FROM Products WHERE CONTAINS(Description, '*sql*') AND CONTAINS(Description, '*serv*') ORDER BY PRICE DESC

SELECT TOP 100 Description, Price, Stock FROM Products WHERE CONTAINS(Description, '*sql*') AND CONTAINS(Description, '*serve*') ORDER BY PRICE DESC

SELECT TOP 100 Description, Price, Stock FROM Products WHERE CONTAINS(Description, '*sql*') AND CONTAINS(Description, '*server*') ORDER BY PRICE DESC

I figured that if CONTAINS() ignores anything under 3 letters, then I’ll use LIKE for things under 3 letters. Problem is though, only the lines in bold work.

This is because CONTAINS() does not bother searching (as far as I can tell) on the parameters: '*ser*','*serv*' and '*serve*'.

So. My question is. How can I find out what strings CONTAINS() does and does not consider searchable words. Because I’d like to check if the query should be executed via LIKE or CONTAINS and build the SQL Statement accordingly. Having said that, using LIKE on its own is running quickly enough, but I would really like to use CONTAINS() instead.

I may of course be making so big logical error here or not understand something in particular, so any help would be appreciated…

Jamie, I need some more information to help answer your question:

What exactly do you mean when you say only the queries in bold "work"? Do you get an error or no results?
What is your sample data like?
What is your intention behind putting '*' before and after a string?

--
Sara Tahir
Program Manager
Microsoft SQL Server

|||

plenderj wrote:

CONTAINS() will only process strings (words!) three letters or more in length. Even though it can do substring searching, it will only do this if it recognises the parameter as a word.
...
I figured that if CONTAINS() ignores anything under 3 letters, then I’ll use LIKE for things under 3 letters. Problem is though, only the lines in bold work.

This is because CONTAINS() does not bother searching (as far as I can tell) on the parameters: '*ser*','*serv*' and '*serve*'.

My question is. How can I find out what strings CONTAINS() does and does not consider searchable words.

Full-Text Search results for your queries depend on multiple things including the wordbreaker, noise word list and whether the intention is to prefix, etc. Without that information, it’s hard to recommend which approach is better.

Here is some information that may help:

Full-Text is token (word) based search and not a substring search. Therefore it will not find arbitrary 3 char string patterns in middle of strings.

Tokenizing is dependent on the wordbreaker and that depends on the language of the column. Some tokenizers could consider the * as punctuation and strip it out - others might leave it intact. In any case Full-Text will be consistent in query and indexing, if the intent was to search the exact string with * and so will be able to match.

Full-Text does do prefix search - meaning if the user provides the leading part of a token, it can find all the tokens that have that prefix. The syntax for such a query is ' "foo*" ' where foo is the prefix and both the prefix and the * are enclosed in double-quotes. So if the intent below of trailing * was to do prefix match, you would need to put it in double-quotes.

There is no support for equivalent suffix match. So if the intent of leading * was that, then Full-Text does not support it.

Full-Text does not have any restrictions on the size of tokes - except 64 characters is max and by default single character token are considered noise.

The only things Full-Text does not consider searchable are the noise words – you can find that list in the noise word file for the specific language.

|||

Hello, everyone! I started to use full-text search and found out this not nice limitation in length of words and noise words. Firstly, I'm not sure that this limit exist, because I can search for two letters long words. If we look in noise file we can find all letters typed in, so one letter long words are noise words and this is not lenght limitation (must try to remove those letters and try search for them).
Again, if I have noise words in query I get error message "Server: Msg 7619, Level 16, State 1, Line 1" which doesn't tell me anything about noise word, so I can't determine it and do proper search using "like" for noise words and "contains" for all other.
Finaly, question. What is a best practice to go around this problem. Import all noise words into application or remove all lines from noise words file.

|||Strange that contains doesn't allow one letter words (just letter) but in noise word files I can see letters. If I remove them, doesn't help.|||Erik,
Firstly, The above reference limit of 3 characters does not exist (otherwise, what would of been the point in adding FTS to SQL 7.0, 2000 and 2005 in the first place?)

Secondly, what exactly was the noise word file you modified? Was it noise.enu (US English) or some other noise.* file under \FTDATA\SQLServer\Config where you have SQL Server 2000 installed? Assuming that you modified the correct file for the "Language for Word Breaker" for your FT-enabled table, did you run a Full Population? If not, then re-run a Full Population as this is required after making changes to the noise word files.

Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
|||I removed data from all of noise word files, but limit for one letter is stil exist. I also done full population.
It's not realy a problem now, problem is that if I want to install my application I also need different instance of SQL server, right? Also, I can run application on same instance with others but then I need to determine those noise words, but I'm not sure that there is a safe way to do it.|||Hi Erik,
So, you left only one letter? What really needs to be done is to leave a single space character in your language specific noise.* file. If you're using US English, then the noise word file is noise.enu. You can determine this by running the following code in your FT-enabled database:

sp_help_fulltext_columns
-- FULLTEXT_LANGUAGE value of 1033 = US English

There was no need to remove all text from all noise word files. If you leave a single space in the nosie word file (noise.enu) and then run a Full Population it will work for you.

No, you don't need to install a different instance (unless you want to), as you can use the default install of SQL Server 2000. Note, I'm assuming that you're using SQL Server 2000 (let me know if you're using SQL Server 2005), there is one MSSearch service for SQL 2000, but the noise word files are installed into each named instance folder, specificly under:

\MSSQL<$Instance_Name>\FTDATA\SQLServer<$Instance_Name>\Config

for example: for Instance name "SQL80":
\MSSQL$SQL2K\FTData\SQLServer$SQL2K\Config\noise.enu

On safe way to determine where are the instance noise word files, is to store the above path in a table along with the instance name. The path is also recorded in the registry under an instance specific registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\ContentIndexCommon\LanguageResources\Override\SQLServer$SQL2K\English (United States)
Locale value= 1033
NoiseFile value= d:\mssql80\MSSQL$SQL2K\FTData\SQLServer$SQL2K\Config\noise.enu

Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/

|||Hi Jamie,
Drop me a line re: this an S205 noise lists and how to develop your own/circumvent.
Do remember they are localised.
fb|||I've since resolved/worked-around by using LIKE:

Shared Function GetMatchingDSForProducts(ByVal strString As String, ByRef mySQLStatement As String) As DataSet
Dim mySQLConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("SQLConn").ToString)
mySQLStatement = "SELECT TOP 100 Description, Price, Stock, [SAP Code] FROM Products WHERE "
mySQLConnection.Open()
Dim x() As String, i As Integer
x = Split(strString, " ")
For i = 0 To x.GetUpperBound(0)
If Not x(i) = "instock" Then mySQLStatement &= " Description LIKE '%" & x(i) & "%' AND "
Next
If Not InStr(strString, "instock") = 0 Then mySQLStatement &= " STOCK > 0 "
If mySQLStatement.EndsWith(" AND ") Then mySQLStatement = Left(mySQLStatement, Len(mySQLStatement) - 4)
mySQLStatement &= " ORDER BY PRICE DESC"
Dim mySQLCommand As New SqlCommand(mySQLStatement, mySQLConnection)
Dim mySQLAdapter As New SqlDataAdapter(mySQLCommand)
mySQLAdapter.SelectCommand = mySQLCommand
Dim myDataSet As New DataSet : mySQLAdapter.Fill(myDataSet)
mySQLConnection.Close()
Return myDataSet
End Function

Query Table Without Data

I'm writting a stored proc that has to query 2 tables. One table is a table of "jobs" and the other table contains jobs that have been invoiced (2 tables are jobs and invoicedJobs). The invoiced table only contains records for jobs that have an invoice and not jobs that do not have an invoice.

My dilemma is that I need to write a query that can retrieve allun-invoiced jobs in my stored proc. You can't rightly join a table that does not have a relationship with another table (can you?). So in my query for jobs with an invoice, I simply join my jobs table and invoice table based on a job id that both tables contain. But how could I perform a query for jobs thatdo not exist in my invoice table inside my stored proc? Any help would be greatly appreciated.

SELECT jobs.*
FROM jobs
LEFT JOIN invoices ON (jobs.id=invoiced.id)
WHERE invoiced.id IS NULL

or

SELECT *
FROM JOBS
WHERE id NOT IN (SELECT id FROM invoices)

|||

Awsome. Thank you.