asked on

Hierarchy Function Is Not Working Properly

Greetings,

I posted this problem weeks ago and moved on to something else but I want to visit it again to close the matter. Look at the details below. I am basically trying to see why I got an error from this function. Table structure is listed below as well.

for sql server 2000:
create function hirearchy(@id as int) returns varchar(1000)
as
begin
declare @tree as varchar(1000)
set @tree = ''
if (parent_cat_code <> 0 and parent_cat_code is not null )

select @tree = dbo.hirearchy(parent_Cat_code) +'>' + description from ticket_category_tmp where id = @id

return @tree
end
go

select id, dbo.hirearchy(id) as tree,cat_level
from ticket_category_tmp
Order By dbo.hirearchy(id)
go

Comment from imran_fast
Date: 05/03/2006 05:03AM PDT
Comment

for sql 2005
=========
WITH DirectReports(id , description, cat_level) AS
(
SELECT id, description, 0 AS cat_level
FROM ticket_category_tmp
WHERE parent_cat_code =0
UNION ALL
SELECT e.id, e.description, cat_level + 1
FROM ticket_category_tmp e
INNER JOIN DirectReports d
ON e.parent_cat_code = d.EmployeeID
)
SELECT id , description, cat_level
FROM DirectReports

GO

Comment from Omega002
Date: 05/03/2006 06:03AM PDT
Your Comment

Greetings,

I got the following error when I attempted your sql 2000 remedy:
Server: Msg 207, Level 16, State 3, Procedure hirearchy, Line 6
Invalid column name 'parent_cat_code'.
Server: Msg 207, Level 16, State 1, Procedure hirearchy, Line 6
Invalid column name 'parent_cat_code'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.hirearchy'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.hirearchy'.

Accepted Answer from imran_fast
Date: 05/06/2006 12:16AM PDT
Grade: B
Accepted Answer

>Invalid column name 'parent_cat_code'.

what is the structure of your table ticket_category_tmp does this column exists there please post the schema for this table.

Comment from Omega002
Date: 05/22/2006 11:26AM PDT
Your Comment

Got a questions for you. How would I get a count of all the tickets from this hieracrchy query? here is the query listed below but how could I get the number of tickets in a date range for each category?

SELECT dbo.ticket_category_tmp.ID, dbo.hirearchy(dbo.ticket_category_tmp.ID) AS tree, dbo.ticket_category_tmp.SITE_ID, dbo.ticket.entered,
dbo.ticket.pri_code, dbo.ticket.problem, dbo.ticket.ticketid
FROM dbo.ticket_category_tmp INNER JOIN
dbo.ticket ON dbo.ticket_category_tmp.ID = dbo.ticket.ticketid
WHERE (dbo.ticket.entered BETWEEN '20060101' AND '20060531')
ORDER BY dbo.hirearchy(dbo.ticket_category_tmp.ID)

Comment from Omega002
Date: 05/25/2006 02:12PM PDT
Your Comment

CREATE TABLE [ticket_category] (
[id] [int] NOT NULL ,
[cat_level] [int] NOT NULL ,
[parent_cat_code] [int] NOT NULL ,
[description] [varchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[full_description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[default_item] [bit] NULL ,
[level] [int] NULL ,
[enabled] [bit] NULL ,
[site_id] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Comment from Omega002
Date: 05/25/2006 02:13PM PDT
Your Comment

Are you still there?

Taconvino

This might work

Select A.dbo.ticket_category_tmp.ID, count(*) from (
SELECT dbo.ticket_category_tmp.ID, dbo.hirearchy(dbo.ticket_category_tmp.ID) AS tree, dbo.ticket_category_tmp.SITE_ID, dbo.ticket.entered,
dbo.ticket.pri_code, dbo.ticket.problem, dbo.ticket.ticketid
FROM dbo.ticket_category_tmp INNER JOIN
dbo.ticket ON dbo.ticket_category_tmp.ID = dbo.ticket.ticketid
WHERE (dbo.ticket.entered BETWEEN '20060101' AND '20060531')
ORDER BY dbo.hirearchy(dbo.ticket_category_tmp.ID)
) A
Group By A.dbo.ticket_category_tmp.ID

TCV

Taconvino

Sorry...

Select A.tree, count(*) from (
SELECT dbo.ticket_category_tmp.ID, dbo.hirearchy(dbo.ticket_category_tmp.ID) AS tree, dbo.ticket_category_tmp.SITE_ID, dbo.ticket.entered,
dbo.ticket.pri_code, dbo.ticket.problem, dbo.ticket.ticketid
FROM dbo.ticket_category_tmp INNER JOIN
dbo.ticket ON dbo.ticket_category_tmp.ID = dbo.ticket.ticketid
WHERE (dbo.ticket.entered BETWEEN '20060101' AND '20060531')
ORDER BY dbo.hirearchy(dbo.ticket_category_tmp.ID)
) A
Group By A.tree

TCV

Omega002

ASKER

This is the error your first suggested query:
Server: Msg 1033, Level 15, State 1, Line 8
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

This is the error your second suggested query:
Server: Msg 1033, Level 15, State 1, Line 8
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

I am under the impression that the hierarchy function was created incorrectly. This is the function listed below:
create function hirearchy(@id as int) returns varchar(1000)
as
begin
declare @tree as varchar(1000)
set @tree = ''
if (parent_cat_code <> 0 and parent_cat_code is not null )
select @tree = dbo.hirearchy(parent_Cat_code) +'>' + description from ticket_category_tmp where id = @id
return @tree
end
go

Error:
Server: Msg 207, Level 16, State 3, Procedure hirearchy, Line 6
Invalid column name 'parent_cat_code'.
Server: Msg 207, Level 16, State 1, Procedure hirearchy, Line 6
Invalid column name 'parent_cat_code'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.hirearchy'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.hirearchy'.

I commented out this section of the function in order to create this function:
if (parent_cat_code <> 0 and parent_cat_code is not null )

The results from the tree when using the hierarchy function is not accurate. Do you have any suggestions for this hierarchy function?

Taconvino

Ok, for the first problem:

Select A.tree, count(*) from (
SELECT dbo.ticket_category_tmp.ID, dbo.hirearchy(dbo.ticket_category_tmp.ID) AS tree, dbo.ticket_category_tmp.SITE_ID, dbo.ticket.entered,
dbo.ticket.pri_code, dbo.ticket.problem, dbo.ticket.ticketid
FROM dbo.ticket_category_tmp INNER JOIN
dbo.ticket ON dbo.ticket_category_tmp.ID = dbo.ticket.ticketid
WHERE (dbo.ticket.entered BETWEEN '20060101' AND '20060531')
) A
Group By A.tree
Order by A.tree

That should work OK.

Now, for your function:

if (parent_cat_code <> 0 and parent_cat_code is not null )

This line makes no sense to me, as there is no table mentioned in the condition. Remember that this is SQL, and proper SQL syntax has to be used. So, it should be something like this:

Declare @PCC numeric
Select @PCC = parent_cat_code from ticket_category_tmp where id = @id

if (@PCC <> 0 and @PCC is not null )
-- The rest of your UDF goes here.

I'm not sure if this is what you need, as I don't fully understand what you are trying to do, but at least this should point you in the right direction.

TCV

Omega002

ASKER

Do you have a the proper syntax if this dbo.hierarchy function?

Taconvino

It should go like this:

create function hirearchy(@id as int) returns varchar(1000)
as
begin
declare @tree as varchar(1000)
set @tree = ''
Declare @PCC numeric
Select @PCC = parent_cat_code from ticket_category_tmp where id = @id

if (@PCC <> 0 and @PCC is not null )
select @tree = dbo.hirearchy(parent_Cat_code) +'>' + description from ticket_category_tmp where id = @id
return @tree
end
go

Now, if the condition is not met, the function will return an empty string.

TCV

Omega002

ASKER

TCV how would you show totals and subtotal in using this query?

Select A.tree, count(*) as #tickets from (
SELECT dbo.ticket_category.ID, dbo.hirearchy(dbo.ticket_category.ID) AS tree, dbo.ticket_category.cat_level, dbo.ticket.entered,
dbo.ticket.pri_code, dbo.ticket.problem, dbo.ticket.ticketid
FROM dbo.ticket_category INNER JOIN
dbo.ticket ON dbo.ticket_category.parent_cat_code = dbo.ticket.pri_code
WHERE (dbo.ticket.entered BETWEEN '20060101' AND '20060131')
) A
Group By A.tree with rollup

Taconvino

That query shows a total grouped by the results of your function. I don't know how you can get sub-totals from it... sorry.

TCV

Omega002

ASKER

This is the results of the query:
tree
>Administration>Authorizations        74
>Administration>Password Change      74
>Administration>UserID Change        74
>Content>Incorrect Info      47
>Content>Missing Product        47
>Content>No Locals        47
>Content>Other        47
>Inquiry>Ads        276
>Inquiry>Channel        276
>Inquiry>Other        276

also is there a way to place the child values in another column next to parent colum and the sub totals and totals like this:
Parent Child #tickets
--------- ------------------- --------
Administration Authorizations      74
Administration Password Change      74
Administration UserID Change      74
sub-total 222
Content Incorrect Info      47
Content Missing Product      47
Content No Locals      47
Content Other       47
subtotal 188
Inquiry Ad Crawl      276
Inquiry Back Channel      276
Inquiry Other        276
sub-total 828
total 1238

Taconvino

This will sepparate the first column:

Select substring(A.tree, 1, PATINDEX('%>%', A.tree)) as Parent, substring(A.tree, PATINDEX('%>%', A.tree), len(A.tree)) as Child, count(*) as tickets from (
SELECT dbo.ticket_category.ID, dbo.hirearchy(dbo.ticket_category.ID) AS tree, dbo.ticket_category.cat_level, dbo.ticket.entered,
dbo.ticket.pri_code, dbo.ticket.problem, dbo.ticket.ticketid
FROM dbo.ticket_category INNER JOIN
dbo.ticket ON dbo.ticket_category.parent_cat_code = dbo.ticket.pri_code
WHERE (dbo.ticket.entered BETWEEN '20060101' AND '20060131')
) A
Group By A.tree with rollup

I can't try this here (I don't have your data/tables), so this is the best I can do. Check Books on Line (SQLServer help) and search for the COMPUTE clause. This clause can get you the sub-totals you need.

TCV

Omega002

ASKER

That just created another colum with the '>' character and no value like this:
parent child #tickets
------- ------- ----------
> >Inquiry>Ad 8

I have a question for you. When I run this query:
Select A.tree, count(*) as #tickets from (
SELECT dbo.ticket_category.ID, dbo.hirearchy(dbo.ticket_category.ID) AS tree, dbo.ticket_category.cat_level, dbo.ticket.entered,
dbo.ticket.pri_code, dbo.ticket.problem, dbo.ticket.ticketid
FROM dbo.ticket_category INNER JOIN
dbo.ticket ON dbo.ticket_category.parent_cat_code = dbo.ticket.pri_code
WHERE (dbo.ticket.entered BETWEEN '20060101' AND '20060131')
) A
Group By A.tree with rollup

It show the total for each group but you can't see the different total for each child within the group so is there a way to do the count inside the function instead of outside this function:
CREATE function hirearchy(@id as int) returns varchar(1000)
as
begin
declare @tree as varchar(1000)
set @tree = ''
select @tree = dbo.hirearchy(parent_cat_code) +'>' + description from dbo.ticket_category where id = @id
return @tree
end

You would have to add the ticket table into a join right in this function. How would that look?

Omega002

ASKER

This is the function modified but I know it is wrong so how would I approach it?
CREATE function hirearchy(@id as int) returns varchar(1000)
as
begin
declare @tree as varchar(1000)
set @tree = ''
select @tree = dbo.hirearchy(parent_cat_code) +'>' + description from dbo.ticket_category where id = @id
return @tree
INNER JOIN
dbo.ticket ON dbo.ticket_category.parent_cat_code = dbo.ticket.pri_code
end

Taconvino

As far as I know, there is no way you can return several values from a function. Or at least, I don't know how you can apply it (if possible) to your problem.

For your second question, I would approach this problem from another perspective: every time a query gets to complicated for me to read and understand at first glance, I turn it into a clear and well commented stored procedure.

TCV

Omega002

ASKER

So it we turned this into a stored procedure, what would it look like?

imran_fast

try this
=======

SELECT

      (select count(*) FROM dbo.ticket_category_tmp d INNER JOIN
       dbo.ticket b ON d.ID =b.ticketid
      WHERE a.entered = b.entered and dbo.hirearchy(c.ID) = dbo.hirearchy(d.ID)
      group by dbo.hirearchy(d.ID)
      ) as total,
      c.ID,
      dbo.hirearchy(c.ID) AS tree,
      c.SITE_ID,
      a.entered,
      a.pri_code,
      a.problem,
      a.ticketid
FROM dbo.ticket_category_tmp c INNER JOIN
dbo.ticket a ON c.ID = dbo.ticket.ticketid
WHERE (a.entered BETWEEN '20060101' AND '20060531')
ORDER BY dbo.hirearchy(c.ID)

imran_fast

sorry
===

SELECT

      (select count(*) FROM dbo.ticket_category_tmp d INNER JOIN
       dbo.ticket b ON d.ID =b.ticketid
      WHERE a.entered between '20060101' AND '20060531' and dbo.hirearchy(c.ID) = dbo.hirearchy(d.ID)
      group by dbo.hirearchy(d.ID)
      ) as total,
      c.ID,
      dbo.hirearchy(c.ID) AS tree,
      c.SITE_ID,
      a.entered,
      a.pri_code,
      a.problem,
      a.ticketid
FROM dbo.ticket_category_tmp c INNER JOIN
dbo.ticket a ON c.ID = dbo.ticket.ticketid
WHERE (a.entered BETWEEN '20060101' AND '20060531')
ORDER BY dbo.hirearchy(c.ID)

Omega002

ASKER

It returned this error:
Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'dbo.ticket' does not match with a table name or alias name used in the query.

ASKER CERTIFIED SOLUTION

imran_fast

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Omega002

ASKER

Greetings that produced the same results. The object is to see the individual totals for each parent and child value. You may want to also link it by ticket_category.parent_cat_code and ticket.pri_code for the joins condition. Here is some sample data from the ticket table and ticket_category table.

TICKET Table:
"ticketid","org_id","contact_id","problem","entered","pri_code","level_code","source_code","cat_code","subcat_code1","subcat_code2","subcat_code3","solution","scode","state_id","site_id"

**Part of this comment deleted by CetusMOD per http:Q_21871461.html**
**Additional part of this comment deleted by Netminder 3 Oct 2006**

Omega002

ASKER

Good News,

With your help this is the query the gives an accurate count of the parent level and child level listed below:
****************************************************************************
SELECT
dbo.hirearchy(dbo.ticket_category.parent_cat_code) AS tree,
count(*)as #tickets
FROM dbo.ticket_category
INNER JOIN dbo.ticket
ON dbo.ticket_category.id = dbo.ticket.ticketid
WHERE dbo.ticket.entered BETWEEN '20060101' AND '20060228' AND ticket_category.cat_level<=2
GROUP BY dbo.hirearchy(dbo.ticket_category.parent_cat_code)with rollup

Results Of This QUERY:
*************************

>Hardware        2
>Hardware>Machine names        4
>Hardware>Phone Call        4
>Software Application        45
>Software Application>Access        11
>Software Application>Excel        10
>Software Application>MSWORD        8
>Software Application>WP        13
>Software Application>WinPRO        12
>Software OS        2
>Software OS>Alarms / Page        8
>Software OS>Phone Call        2
**************************************************

Now with this query how can I produce a sub total for each group?