September 22, 2008 at 11:18 pm
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
September 22, 2008 at 11:34 pm
Hi
I have'nt gone thru your post in detail.. sorry for that 🙂
You could take a look at CTE 's for fetching hierarchial data.
That way you wont have to worry abt levels of data.
"Keep Trying"
September 22, 2008 at 11:37 pm
I dont think it is available - I have SQL2008 (I know this is a 2005 forum)
September 23, 2008 at 2:31 am
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION dbo.GetLocationDescription
(
    @location_id int
)
RETURNS varchar(8000)
AS
BEGIN
    DECLARE @result varchar(8000)
    SET @result = ''
   WITH cte (HLevel, Parent, [Description])
    AS
    (
        SELECT 1 AS HLevel, T1.Parent, T1.[Description]
        FROM dbo.Location T1
        WHERE T1.location_id = @location_id
        UNION ALL
        SELECT C2.HLevel + 1, T2.Parent, T2.[Description]
        FROM dbo.Location T2
            JOIN cte C2
                ON T2.location_id = C2.Parent
    )
    SELECT @result = @result + C.[Description] + ' | '
    FROM cte C
    ORDER BY C.HLevel DESC
    RETURN LEFT(@result, LEN(@result) - 2)
END
GO
SELECT E.emp_user_id
    ,C.compound_nme
    ,B.batch_id
    ,B.compound_id
    ,dbo.GetLocationDescription(B.location_id) AS Location
    ,B.expiry_dte
FROM dbo.compound C
    JOIN dbo.batch B
        ON C.compound_id = B.compound_id
    JOIN dbo.[Employee Full] E
        ON B.create_by_emp_no = E.emp_no
WHERE B.expiry_dte < GETDATE()
ORDER BY E.emp_user_id
September 23, 2008 at 2:52 am
Are'nt CTE's available in SQL 2008 ?
"Keep Trying"
September 23, 2008 at 2:52 am
Hi There and thanks for the reply
This problem has been dogging me now for oiver a week. When I copy this into query analyser to test I get two errors
Server: Msg 156, Level 15, State 1, Procedure GetLocationDescription, Line 11
Incorrect syntax near the keyword 'WITH'.
Server: Msg 137, Level 15, State 1, Procedure GetLocationDescription, Line 21
Must declare the variable '@t'.
I am using sql server 2000 (I posted incorrectly earlier) does this version support CTE Recursion?
Thanks for your help
September 23, 2008 at 3:05 am
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
September 23, 2008 at 3:10 am
Server: Msg 444, Level 16, State 2, Procedure GetLocationDescription, Line 27
Select statements included within a function cannot return data to a client.
September 23, 2008 at 3:53 am
Add the INSERT INTO @cte which I forgot.
September 23, 2008 at 4:00 am
Could you show me where this needs to be inserted please
September 23, 2008 at 11:33 pm
Add the insert into @cte does not make sense to me. The error message that I get indicates that you cannot return a recordset to the client from within a function. And this is a function!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply