• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 272
  • Last Modified:

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?
0
Omega002
Asked:
Omega002
  • 10
  • 7
  • 3
1 Solution
 
TaconvinoCommented:
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
0
 
TaconvinoCommented:
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
0
 
Omega002Author Commented:
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?
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
TaconvinoCommented:
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
0
 
Omega002Author Commented:
Do you have a the proper syntax if this  dbo.hierarchy function?
0
 
TaconvinoCommented:
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
0
 
Omega002Author Commented:
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
0
 
TaconvinoCommented:
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
0
 
Omega002Author Commented:
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
0
 
TaconvinoCommented:
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
0
 
Omega002Author Commented:
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?
0
 
Omega002Author Commented:
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
0
 
TaconvinoCommented:
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
0
 
Omega002Author Commented:
So it we turned this into a stored procedure, what would it look like?
0
 
imran_fastCommented:
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)


0
 
imran_fastCommented:
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)


0
 
Omega002Author Commented:
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.
0
 
imran_fastCommented:
Try this
=====
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 = a.ticketid
WHERE     (a.entered BETWEEN '20060101' AND '20060531')
ORDER BY dbo.hirearchy(c.ID)
0
 
Omega002Author Commented:
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**

0
 
Omega002Author Commented:
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?
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 10
  • 7
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now