Urgent SQL Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Urgent SQL Help

Author

Topic

WelshPunk
Yak Posting Veteran

67 Posts

Posted - 2008-09-19 : 04:57:40

Hi clever sql people - could someone give their expertise here.

I have a table that is used for the hierarchical structure of rooms in an organisation. Here is the makeup of the table

location_id - parent - description
1 - 205 - Bridge Laboratory
2 - 1 - Flammable Store
5 - 205 - Waste
9 - 5 - Waste Station
10 - 205 - Process Engineering

Plus about a hundred more. In my web application I need to write some sql that will look for xpired chemical batches due to expire in the next thirty days. My problem is that I need to list the full location of each batch - and that is the problem - some locations have a location - and a sub location - but others have up to 5 or six sub locations. The way that I have tried to do this is by the following code. It is not correct as it goes to four levels but I am not dealing with what happens if there is a batch in a location where there is only one or two sublocations. Here is my code

Use new_scitech

SELECT dbo.[Employee Full].emp_user_id,
dbo.compound.compound_nme,
dbo.batch.batch_id,
dbo.batch.compound_id,
location_4.description AS Loc1,
location_3.description AS Loc2,
location_2.description AS Loc3,
location_1.description AS Loc4,
dbo.batch.expiry_dte
FROM
dbo.compound
INNER JOIN dbo.batch ON dbo.compound.compound_id = dbo.batch.compound_id
INNER JOIN dbo.location ON dbo.batch.location_id = dbo.location.location_id
INNER JOIN dbo.[Employee Full] ON dbo.batch.create_by_emp_no = dbo.[Employee Full].emp_no
INNER JOIN dbo.location location_1 ON dbo.location.location_id = location_1.location_id
INNER JOIN dbo.location location_2 ON location_1.parent = location_2.location_id
INNER JOIN dbo.location location_3 ON location_2.parent = location_3.location_id
INNER JOIN dbo.location location_4 ON location_3.parent = location_4.location_id
GROUP BY
dbo.[Employee Full].emp_user_id,
dbo.compound.compound_nme,
location_4.description,
location_3.description,
location_2.description,
location_1.description,
dbo.batch.expiry_dte,
dbo.batch.batch_id,
dbo.batch.compound_id
HAVING
(dbo.batch.expiry_dte < GETDATE())
ORDER BY
dbo.[Employee Full].emp_user_id

I guess what I need to do is put all locations into one field and put a '|' between them. Add a few more location tables and wrap the selects in a case statement that would put append a '' to any locations that return a null. I am not sure how to go about the joins for this so - if anyone can help that would be great.

Great forum and thanks in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-19 : 05:34:00

SQL Server 2005?
Search for other solutions including RECURSIVE CTE.

E 12°55'05.63"
N 56°04'39.26"

WelshPunk
Yak Posting Veteran

67 Posts

Posted - 2008-09-19 : 06:00:52

I have found an example of recursive_cte but I am having problems using it in my example. Could anyone help please

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-19 : 06:21:47

quote:
Originally posted by WelshPunk

I have found an example of recursive_cte but I am having problems using it in my example. Could anyone help please

post what you've tried till now and we'll help to get it to work

WelshPunk
Yak Posting Veteran

67 Posts

Posted - 2008-09-19 : 07:22:55

Use new_scitech

WITH LOCATION_CTE AS(
SELECT
dbo.[Employee Full].emp_user_id,
dbo.compound.compound_nme,
dbo.batch.batch_id,
dbo.batch.compound_id,
dbo.location.description AS Loc1,
dbo.batch.expiry_dte
FROM
dbo.compound
INNER JOIN dbo.batch ON dbo.compound.compound_id = dbo.batch.compound_id
INNER JOIN dbo.location ON dbo.batch.location_id = dbo.location.location_id
INNER JOIN dbo.[Employee Full] ON dbo.batch.create_by_emp_no = dbo.[Employee Full].emp_no
WHERE
dbo.location_description is NULL AND(dbo.batch.expiry_dte < GETDATE())
UNION ALL

SELECT dbo.[Employee Full].emp_user_id,
dbo.compound.compound_nme,
dbo.batch.batch_id,
dbo.batch.compound_id,
dbo.location.description AS Loc1,
dbo.batch.expiry_dte
FROM
dbo.compound
INNER JOIN dbo.batch ON dbo.compound.compound_id = dbo.batch.compound_id
INNER JOIN dbo.location ON dbo.batch.location_id = dbo.location.location_id
INNER JOIN dbo.[Employee Full] ON dbo.batch.create_by_emp_no = dbo.[Employee Full].emp_no
WHERE
dbo.location_description is NULL AND(dbo.batch.expiry_dte < GETDATE())

)
SELECT * FROM LOCATION_CTE

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-19 : 07:34:38

You need to join to CTE in second query part (after UNION ALL) for it to recurse.

WelshPunk
Yak Posting Veteran

67 Posts

Posted - 2008-09-19 : 07:39:38

I have SQL SERVER 8.0

Does this recursive CTE work here as I get an error on 'WITH'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-19 : 07:48:50

You dont have ctes in sql server 8.0 try using below method then

http://msdn.microsoft.com/en-us/library/aa172799(SQL.80).aspx

WelshPunk
Yak Posting Veteran

67 Posts

Posted - 2008-09-22 : 04:28:23

I have never used temp tables in sql before. I followed the link and tried to understand it. Could someone show me how I could use my example in temp tables to get the correct result. Sorry for being dopey but I am new to all this

WelshPunk
Yak Posting Veteran

67 Posts

Posted - 2008-09-25 : 01:03:05

I have had some help on this problem and almost got a solution. See below
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION dbo.GetLocationDescription
(
@location_id int
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @result varchar(8000)
,@Level int
SELECT @result = ''
,@Level = 1

DECLARE @cte TABLE
(
HLevel int NOT NULL
,Parent int NOT NULL
,[Description] varchar(255) NOT NULL
)

INSERT INTO @cte
SELECT @Level, T1.Parent, T1.[Description]
FROM dbo.Location T1
WHERE T1.location_id = @location_id

WHILE 1=1
BEGIN
SELECT C2.HLevel + 1, T2.Parent, T2.[Description]
FROM dbo.Location T2
JOIN @cte C2
ON T2.location_id = C2.Parent
WHERE C2.HLevel = @Level

IF @@rowcount = 0
BREAK

SET @Level = @Level + 1
END

SELECT @result = @result + C.[Description] + ' | '
FROM @cte C
ORDER BY C.HLevel DESC

RETURN LEFT(@result, LEN(@result) - 2)
END
GO

But I get the following message in QA
Server: Msg 444, Level 16, State 2, Procedure GetLocationDescription, Line 27
Select statements included within a function cannot return data to a client.

Someone mentioned that I should add the insert into @cte can anyone show me what they mean please

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources