(This is a repost, under my correct subscriber ID.)
I have a report that I think is not too complex (a matrix inside a list).
With small datasets, all is well. But with large datasets (the threshold
seems to be around 5500 rows, which should turn out to be about 25 printed
pages), the report displays to the browser just fine, but if I attempt to
print it or preview it:
(1) I get just the 1st page, then a dialog box:
(Caption:) Invalid Page Number
(Text) The requested page does not exist.
(2) In addition, in the RS LogFiles folder, the corresponding log file has
this line in it:
aspnet_wp!chunks!72c!07/20/2006-22:22:04:: i INFO: ###
GetReportChunk('RenderingInfo_IMAGE', 2), chunk was not found!
this=93db4874-b6d5-4cf2-b885-affcf17ced96
I am guessing that the "2" here refers to page 2 of the report, which it is
bombing on.
(3) In addition, the aspnet_wp.exe process goes wild, consuming 80-95% of
the processor speed, and keeps churning until I am forced to shut it down.
========== I am seeing the same behavior (a) on my development machine, (b) on a test
server in my office, (c) at a client site. So I don't think it's an anomoly
of my environment.
I am running with SQL Server 2000, Reporting Services 2000 SP2. I also
found a hotfix on the MSFT site, which I installed, to no effect.
Any ideas, anyone'
Thanks in advance,
C17Hello,
Thank you for your post in MSDN newsgroup!
I understand that you encountered issue when prview/print a specific report
on different machines. If I'm off-base, please let me know.
Based on my research, I was not able to find any known issue on this error.
To troubleshoot the issue we may need to analyze memory dumps or use live
debug, this work has to be done by contacting Microsoft Product Support
Services. Therefore, we probably will not be able to resolve the issue
through the newsgroups. If the issue is urgent, I recommend that you open a
Support incident with Microsoft Product Support Services so that a
dedicated Support Professional can assist with this case. If you need any
help in this regard, please let me know.
For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp
If you'd still like to continue working via the newsgroup, please
understand the issue might take a long time to narrow down. During the
course of troubleshooting, we may redirect you to PSS if required. For now,
please try the following and let me know how it goes:
1. Get the sample RS project, and backend database with necessary tables
behind, and send them to me at petery@.microsoft.com. Please also let's know
the detailed reproduce steps
2. Does the issue occurs in report designer? Does the issue occur if you
configure default printer to "Microsoft Office Document Image Writer"?
3. Does the issue only occur with report with large number rows? If you
limit the rows/pages, can you reproduce the issue?
If you have other related information, please also let's know.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||I think it might be a timeout issue. I get the "Invalid Page Number" from
Print Preview after EXACTLY 5 minutes.
The question is, WHAT is timing out? I have the following set:
1) Query timeout (set as property of the query on the report) = 1800 seconds
= 30 minutes. (In fact the underlying report query takes only about 20
seconds to run.)
2) Report execution timeout (set for report using Report Manager) = 1800
seconds
3) Site settings default (set for entire site using Report Manager) = 1800
seconds
4) IIS Web Site/Default Web Site properties: 900 seconds = 15 minutes
What else is there that could be causing a time out at exactly 5 minutes'
Thanks,
C17|||Hello,
Thank you for your reply. I think this may not be related to timeout but
rather than memory related issue.
Based on my further research, the GetReportChunk exception shall not be
generated by printer renderer. It might be generated by HTML renderer which
uses url?s for the chart. Printer renderer will create only one stream for
the entire report and this stream will be in memory since we need to seek
on it. If in your report, every page has a chart or graph, We need to
preprocess these graph/chart before we will embed them in the file. This
process is taking time/memory.
If this is the issue, the workaround is to use less pages/records in one
report or use smaller graph. Also, you may contact PSS to get the following
fix to see if it resolves the issue:
905121 FIX: An error message is logged when you try to export a large
report to PDF, and you cannot open a spreadsheet file in Office Excel that
was exported from SQL Server 2000 Reporting Services
http://support.microsoft.com/default.aspx?scid=kb;EN-US;905121
If you have any update, please feel free to let's know.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Peter,
Thank you for the information, but I do not think that HotFix will solve the
problem, because the HotFix addresses problems with charts and images in
export to PDF. However, in my case:
* The problematic report does not contain ANY charts or images
* Export to PDF, and Export to Excel, works just fine. It's just Printing
and Print Preview that fails.
I will try the HotFix if you still think I should, but let me give you some
more information based on my testing:
1) I get the "Invalid Page Number" message box after 5 minutes with: Print
or Print Preview to *any* printer (including "Microsoft Office Document
Image Writer")
2) The problem occurs when running the report under *any* environment: (a)
when running my application, (b) within the report designer, (c) when
running the report from Report Manager.
3) The problem occurs at a threshold of about 800 rows in the underlying
query. If I change the report parameters so the query returns fewer rows,
the report successfully prints/previews in about 45 seconds. Above that
threshold, the report gives the "Invalid Page Number" message box after
exactly 5 minutes.
4) The problem does NOT occur upon Export to Excel, or Export to PDF.
(Export to PDF takes a long time to run, but I understand from reading the
newgroup that this is normal.)
I will bundle up a sample database, and the report, and email them to you as
you suggested in an earlier post. I don't think you will need my
application, because the problem occurs even when you run the report from
Report Manager.
Thanks,
--C17|||Hello Charles,
Thank you for your patience. I tested on RS 2005 SP1 machine and the issue
does not occur. I think you may consider the possibility of upgrading to
SQL 2005 as a workaround. Also, I?d check if I could find other workaround
on RS 2000. Please rest assured I?v reported this issue to the proper
channel. Thanks.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================Hello Charles,
I was able to reproduce the issue on my side. I tried to upgrade to build
1049 (hotfix 912715) but the issue still persists. It seems to be a product
issue for RS 2000. I will perform some more research and test it on RS
2005. Thank you for your patience.
Best Regards,
Best Regards,
Showing posts with label number. Show all posts
Showing posts with label number. Show all posts
Sunday, March 25, 2012
Thursday, March 8, 2012
Cannot limit results with ReportViewer
Hello,
I have been attempting to set up a report using the ReportViewer
control in VS2005. The report graphs the TOP X number of records based
on certain criteria. Naturally, I modified the report with a filter
for the TOP N results. This works, however, in the case of ties on the
last entry, all tied instances will get graphed. In my case, this may
be alot of entries causing the bar graph image to display unreadable
data since there are too many result to graph.
So, I attempted the following with no success since the server is SQL
2000 and not SQL 2005:
- Using the TOP keyword in the select query does not work with a
parameter (ie SELECT TOP @.rowcount FROM...) since I am using SQL 2000.
- I cannot use a stored procedure and use the "SET ROWCOUNT" command
with the select query.
- I cannot use the LIMIT keyword in SQL 2000.
Can anyone come up with a solution, other than upgrading to SQL 2005?
Thanks in advance,
Andrewcheck this one......
SELECT w1.CATEGORYID, w1.UNITSINSTOCK,COUNT(*) As Rank
FROM
(SELECT CATEGORYID, UNITSINSTOCK
FROM PRODUCTS
GROUP BY CATEGORYID, UNITSINSTOCK ) AS w1
INNER JOIN
(SELECT CATEGORYID, UNITSINSTOCK
FROM PRODUCTS
GROUP BY CATEGORYID, UNITSINSTOCK ) AS w2
ON w1.CATEGORYID = w2.CATEGORYID
AND w1.UNITSINSTOCK <= w2.UNITSINSTOCK
GROUP BY w1.CATEGORYID, w1.UNITSINSTOCK
HAVING COUNT(*) <=@.parameter
ORDER BY w1.CATEGORYID,w1.UNITSINSTOCK DESC
Regards
Dinesh
therealbigcoops@.gmail.com wrote:
> Hello,
> I have been attempting to set up a report using the ReportViewer
> control in VS2005. The report graphs the TOP X number of records based
> on certain criteria. Naturally, I modified the report with a filter
> for the TOP N results. This works, however, in the case of ties on the
> last entry, all tied instances will get graphed. In my case, this may
> be alot of entries causing the bar graph image to display unreadable
> data since there are too many result to graph.
> So, I attempted the following with no success since the server is SQL
> 2000 and not SQL 2005:
> - Using the TOP keyword in the select query does not work with a
> parameter (ie SELECT TOP @.rowcount FROM...) since I am using SQL 2000.
> - I cannot use a stored procedure and use the "SET ROWCOUNT" command
> with the select query.
> - I cannot use the LIMIT keyword in SQL 2000.
> Can anyone come up with a solution, other than upgrading to SQL 2005?
> Thanks in advance,
> Andrew
I have been attempting to set up a report using the ReportViewer
control in VS2005. The report graphs the TOP X number of records based
on certain criteria. Naturally, I modified the report with a filter
for the TOP N results. This works, however, in the case of ties on the
last entry, all tied instances will get graphed. In my case, this may
be alot of entries causing the bar graph image to display unreadable
data since there are too many result to graph.
So, I attempted the following with no success since the server is SQL
2000 and not SQL 2005:
- Using the TOP keyword in the select query does not work with a
parameter (ie SELECT TOP @.rowcount FROM...) since I am using SQL 2000.
- I cannot use a stored procedure and use the "SET ROWCOUNT" command
with the select query.
- I cannot use the LIMIT keyword in SQL 2000.
Can anyone come up with a solution, other than upgrading to SQL 2005?
Thanks in advance,
Andrewcheck this one......
SELECT w1.CATEGORYID, w1.UNITSINSTOCK,COUNT(*) As Rank
FROM
(SELECT CATEGORYID, UNITSINSTOCK
FROM PRODUCTS
GROUP BY CATEGORYID, UNITSINSTOCK ) AS w1
INNER JOIN
(SELECT CATEGORYID, UNITSINSTOCK
FROM PRODUCTS
GROUP BY CATEGORYID, UNITSINSTOCK ) AS w2
ON w1.CATEGORYID = w2.CATEGORYID
AND w1.UNITSINSTOCK <= w2.UNITSINSTOCK
GROUP BY w1.CATEGORYID, w1.UNITSINSTOCK
HAVING COUNT(*) <=@.parameter
ORDER BY w1.CATEGORYID,w1.UNITSINSTOCK DESC
Regards
Dinesh
therealbigcoops@.gmail.com wrote:
> Hello,
> I have been attempting to set up a report using the ReportViewer
> control in VS2005. The report graphs the TOP X number of records based
> on certain criteria. Naturally, I modified the report with a filter
> for the TOP N results. This works, however, in the case of ties on the
> last entry, all tied instances will get graphed. In my case, this may
> be alot of entries causing the bar graph image to display unreadable
> data since there are too many result to graph.
> So, I attempted the following with no success since the server is SQL
> 2000 and not SQL 2005:
> - Using the TOP keyword in the select query does not work with a
> parameter (ie SELECT TOP @.rowcount FROM...) since I am using SQL 2000.
> - I cannot use a stored procedure and use the "SET ROWCOUNT" command
> with the select query.
> - I cannot use the LIMIT keyword in SQL 2000.
> Can anyone come up with a solution, other than upgrading to SQL 2005?
> Thanks in advance,
> Andrew
Sunday, February 19, 2012
Cannot import Oracle data with OLE DB
I have a column in an Oracle source system with data type NUMBER(38,2). The value "-0.01" is causing problems when trying to import into a SSIS data-flow.
The only way I can import this into my data-flow is by using a Datareader connection manager using the ODBC Data Provider. My DSN is using the Oracle ODBC driver.
If I try and use the "Native OLE DB\Microsoft OLE DB Provider for Oracle" I get an error: "The data value cannot be converted for reasons other than sign mismatch or data overflow"
Judging by this post: http://microsoftdw.blogspot.com/2005/11/final-storyhow-to-get-data-out-of.html there aren't really any other combinations to try that will bring my data in as I want it.
I don't want to use ODBC though as:
- Its an old technology Its slow I have to deploy an additional DSN.
Can anyone tell me why the other options don't work? Why does Microsoft OLE DB Provider for Oracle have a problem with "-0.01"?
-Jamie
When I tried to reproduce the issue reported by you, I encountered different set of problems. I tried to copy data from a Oracle table with NUMBER(38,2) column to a SQL Server 2005 table with a NUMERIC (38,2) column using OraOLEDB (and later using MSDORA) OLE DB connection manager at the source. I dont get any error messages when I execute the package. However, no data is transferred and I do not see any data when I preview the data at the source. May be I am missing something that you did that produced the error message you got.|||
Well I can't even preview the data so I think my problem is more deep seated. My errors occur in the source adapter and hence I think I have a problem in the middleware.
-Jamie
Thursday, February 16, 2012
Cannot get my TREE VIEW Recursive query to work
My Table Structure
Category_ID Number
Parent_ID Number <--Category_ID reports to this colum
Category_Name Varchar...
MY QUERY <--I replaced the query above with my data
=============================
WITH Hierarchy(Category_ID, Category_Name, Parent_ID, HLevel)
AS
(
SELECT Category_ID, Category_Name, Parent_ID, 0 as HLevel FROM Dir_Categories
UNION ALL
SELECT SubCategory.Category_ID
, SubCategory.Category_Name,
SubCategory.Parent_ID,
HLevel + 1
FROM Dir_Categories SubCategory
INNER JOIN Hierarchy ParentCategory
ON SubCategory.Parent_ID = ParentCategory.Category_ID )
SELECT Category_ID,
Category_Name = Replicate('__', HLevel) + Category_Name,
Parent_ID,
HLevel
FROM Hierarchy
My OUTPUT============
All the categories under reporting to Parent_ID 0 or continuous, then the ones reporting to 1 and so fourth. Subcategories are not showing within their main categories. I AM GOING NUTS WITH THIS.
Can you help me please?One golden rule in database systems is: Never expect the rows to appear in a specific order. If you for some reason need the columns to be in a specific order, use order by on a suitable column. Here you don't have one, sou you'll have to add a column that you can use for sorting, for instance a varchar column containing the list of IDs from root to the node.|||One golden rule in database systems is: Never expect the rows to appear in a specific order. If you for some reason need the columns to be in a specific order, use order by on a suitable column. Here you don't have one, sou you'll have to add a column that you can use for sorting, for instance a varchar column containing the list of IDs from root to the node.
Hmm, I actually have one that lists the category IDs from root to node. That is a good Ideal, I will try that and get back to you.
Thanks|||One golden rule in database systems is: Never expect the rows to appear in a specific order. If you for some reason need the columns to be in a specific order, use order by on a suitable column. Here you don't have one, sou you'll have to add a column that you can use for sorting, for instance a varchar column containing the list of IDs from root to the node.
Look at this output, look at the PathID, can you suggest how I can order that to get the subcategories inside the Categories? I am more of a web guy, and can generally do some good querying but I am better in Oracle.
Check it out:
============================
PATHID
1
10
100,3
101,3
102,3
103,3
104,3
105,3
106,3
107,3
108,3
109,3
11
110,4
111,4
112,4
113,4
114,4
115,4
116,4
117,4
118,4
119,4
12
120,4
121,4
122,4
123,4
124,4
125,4
126,4
127,4
128,4
129,4
13
130,4
131,4
132,4
133,5
134,5
135,5
136,5
137,5
138,5
139,5
14
140,5
141,5
142,5
143,5
144,5
145,5
146,5
147,5
148,5
149,5
15
150,6
151,6
152,6
153,6
154,6
155,6
156,6
157,6
158,6
159,6
16,1
160,6
161,6
162,7
163,7
164,7
165,7
166,7
167,7
168,7
169,8
17,1
170,8
171,8
172,8
173,8
174,8
175,8
176,8
177,8
178,8
179,8
18,1
180,8
181,8
182,8
183,8
184,8
185,8
186,8
187,8
189,8
19,1
190,8
191,8
192,8
193,9
194,9
195,9
196,9
197,9
198,9
199,9
2
20,1
200,9
201,9
202,9
203,9
204,10
205,10
206,10
207,10
208,10
209,10
21,1
210,10
211,10
212,10
213,10
214,10
215,10
216,11
217,11
218,11
219,11
22,1
220,11
221,11
222,11
223,11
224,11
225,11
226,11
227,11
228,11
229,11
23,16,1
230,11
231,11
232,11
233,12
234,12
235,12
236,13
237,13
238,14
239,14
24,16,1
240,14
241,14
242,14
243,14
245,14
246,14
247,14
248,14
249,14
25,16,1
250,14
252,14
253,14
254,14
255,14
256,14
257,14
258,14
259,14
26,16,1
260,14
261,14
262,14
263,14
264,14
265,14
266,14
267,14
268,14
269,14
27,16,1
270,14
271,14
272,15
273,15
274,15
275,15
276,15
277,15
278,15
279,15
28,16,1
280,15
281,15
282
283,282
284,282
285,282
286,282
287,282
288,282
289,282
29,16,1
290,282
291,282
292,282
293,282
294,282
295,282
296,86,2
297,86,2
298,86,2
299,86,2
3
30,16,1
300,86,2
301,86,2
302,86,2
303,86,2
304,86,2
305,86,2
309,87,2
31,16,1
310,87,2
311,87,2
312,87,2
313,88,2
314,88,2
315,88,2
316,88,2
317,88,2
318,88,2
319,88,2
32,16,1
320,88,2
322,88,2
323,88,2
324,89,2
325,89,2
326,89,2
327,89,2
328,89,2
329,89,2
33,16,1
330,89,2
331,89,2
332,89,2
333,89,2
334,89,2
335,89,2
336,89,2
337,89,2
338,89,2
339,89,2
34,16,1
340,90,2
341,90,2
342,90,2
343,90,2
344,90,2
345,90,2
346,90,2
347,90,2
348,90,2
349,90,2
35,17,1
350,90,2
351,90,2
352,90,2
353,92,2
354,92,2
355,92,2
356,92,2
357,92,2
358,92,2
359,92,2
36,17,1
360,92,2
361,92,2
362,92,2
363,92,2
364,92,2
365,93,2
366,93,2
367,93,2
368,93,2
369,93,2
37,17,1
370,93,2
371,93,2
372,366,93,2
373,366,93,2
374,366,93,2
375,366,93,2
376,366,93,2
377,366,93,2
378,366,93,2
379,366,93,2
38,17,1
380,366,93,2
381,366,93,2
382,366,93,2
383,366,93,2
384,366,93,2
385,366,93,2
386,366,93,2
387,366,93,2
388,366,93,2
389,366,93,2
39,17,1
390,366,93,2
391,366,93,2
392,366,93,2
393,94,2
394,94,2
395,94,2
396,94,2
397,94,2
398,94,2
399,94,2
4
40,17,1
400,94,2
401,94,2
402,94,2
403,94,2
404,94,2
405,94,2
406,95,2
407,95,2
408,95,2
409,95,2
41,17,1
410,95,2
411,95,2
412,95,2
413,95,2
414,95,2
415,95,2
416,96,2
417,96,2
418,96,2
419,96,2
42,17,1
420,96,2
421,96,2
422,96,2
423,96,2
424,96,2
425,96,2
426,96,2
427,98,2
428,98,2
429,98,2
43,17,1
430,98,2
431,98,2
432,98,2
433,98,2
434,98,2
435,98,2
436,98,2
437,98,2
438,98,2
439,98,2
44,17,1
440,110,4
441,110,4
442,110,4
443,110,4
444,110,4
445,110,4
45,17,1
450,110,4
451,110,4
452,111,4
453,111,4
454,111,4
455,111,4
456,111,4
457,111,4
458,111,4
459,111,4
46,17,1
460,111,4
461,111,4
462,111,4
463,111,4
464,111,4
465,111,4
466,111,4
467,111,4
468,111,4
469,111,4
47,17,1
470,111,4
471,112,4
472,112,4
473,112,4
474,112,4
475,111,4
476,112,4
477,112,4
478,112,4
479,112,4
48,17,1
480,112,4
481,112,4
482,112,4
483,112,4
484,112,4
485,112,4
489,109,3
49,17,1
490,109,3
491,109,3
492,109,3
493,109,3
494,109,3
495,109,3
496,109,3
497,101,3
498,101,3
499,101,3
5
50,17,1
500,101,3
501,101,3
502,101,3
503,101,3
504,101,3
505,102,3
506,102,3
507,102,3
508
509,508
51,17,1
510,508
511,508
512,508
513,508
514,508
515,508
516,508
517,508
518,508
519,508
52,17,1
520,508
521,92,2
522,521,92,2
523,521,92,2
524,521,92,2
525,521,92,2
526,521,92,2
527,521,92,2
528,521,92,2
529,521,92,2
53,17,1
530,521,92,2
531,101,3
532,537,536
533,537,536
534,537,536
535,537,536
536
537,536
538,536
539,234,12
54,17,1
540,234,12
541,234,12
542,234,12
543,234,12
545,234,12
546,234,12
547,234,12
548,282
549,2
55,17,1
550,7
56,17,1
560,3
561,312,87,2
562,130,4
563,234,12
564,130,4
565,7
566,7
567,1
568,130,4
569,7
57,17,1
570,234,12
571,234,12
572,2
573,572,2
574,234,12
58,18,1
59,18,1
6
60,19,1
61,19,1
62,19,1
63,19,1
64,19,1
65,19,1
66,19,1
67,19,1
68,19,1
69,19,1
7
70,19,1
71,20,1
72,20,1
73,20,1
74,20,1
75,20,1
76,20,1
77,20,1
78,21,1
79,21,1
8
80,21,1
81,21,1
82,21,1
83,21,1
84,22,1
85,22,1
86,2
87,2
88,2
89,2
9
90,2
92,2
93,2
94,2
95,2
96,2
97,2
98,2
99,2|||i dunno, man, you lost me somewhere around 237,13 ;)
you're a web guy? then you know that an unlimited number of levels is likely not workable?
how many users are willing to drill down fifteen levels to find where they're going?
if you can limit the hierarchy to some fixed maximum number of levels, then you can use a series of self-joins instead of a recursive query (although of course a recursive query will work with a fixed number of levels too)
perhaps this article may help: Categories and Subcategories (http://sqllessons.com/categories.html)
and yes, there is no problem with fifteen self-joins in a query
:)|||Here is the modified query, the output is still disorganized, it looks closer to what I am looking for though. Can you let me know what needs to be changed?
================================================== ======
WITH Tree(Category_ID, Parent_ID, Category_Name, Path, Hlevel)
AS
(
SELECT Category_ID, Parent_ID, Category_Name, PathID, 0 as Hlevel
FROM Dir_Categories
WHERE Parent_ID = 0
UNION ALL
SELECT e.Category_ID, e.Parent_ID, e.Category_Name, e.PathID, Hlevel + 1
FROM Dir_Categories e
INNER JOIN Tree d
ON e.Parent_ID = d.Category_ID
)
SELECT Category_ID, Parent_ID, HLevel,
Category_Name = Replicate('__', HLevel) + Category_Name, Path
FROM Tree order by Category_ID, Path;
GO|||i dunno, man, you lost me somewhere around 237,13 ;)
you're a web guy? then you know that an unlimited number of levels is likely not workable?
how many users are willing to drill down fifteen levels to find where they're going?
if you can limit the hierarchy to some fixed maximum number of levels, then you can use a series of self-joins instead of a recursive query (although of course a recursive query will work with a fixed number of levels too)
perhaps this article may help: Categories and Subcategories (http://sqllessons.com/categories.html)
and yes, there is no problem with fifteen self-joins in a query
:)
I am not going further deeper than 4 levels. It is a web directory and Search Engines dont go that deep so I keep it no more than four. Again, you are talking about self joins and all that but maybe some examples will help, I will take a look at the article. THanks|||i dunno, man, you lost me somewhere around 237,13 ;)
you're a web guy? then you know that an unlimited number of levels is likely not workable?
how many users are willing to drill down fifteen levels to find where they're going?
if you can limit the hierarchy to some fixed maximum number of levels, then you can use a series of self-joins instead of a recursive query (although of course a recursive query will work with a fixed number of levels too)
perhaps this article may help: Categories and Subcategories (http://sqllessons.com/categories.html)
and yes, there is no problem with fifteen self-joins in a query
:)
Thanks, I applied this query and although it is very useful, it doesnt produce the display that i am looking for. And quite honestly it would be too much coding to take the data and create a fabricated HTML output that looks like a tree. It is the only thing that has worked so far though.
AAAHHHHRRGGG, this is so simple in Oracle, just using Connect By. The problem is that I have a shared windows hosting and they only have SQL server. Oracle hosting is too expensive and for the purpose I am developing this, I want anyone who I extend the code to be able to use SQL shared hosting which is the most common one.
Any other resources or suggestions?|||can you show the query that you tried? because honestly, 4 self-joins is nothing complicated
and would you mind repeating what sort of display you are looking for|||can you show the query that you tried? because honestly, 4 self-joins is nothing complicated
and would you mind repeating what sort of display you are looking for
Here is the query I tried now
===================================
select root.Category_Name as root_name
, down1.category_name as down1_name
, down2.category_name as down2_name
, down3.category_name as down3_name
from Dir_categories as root
left outer
join Dir_categories as down1
on down1.parent_id = root.Category_ID
left outer
join Dir_categories as down2
on down2.parent_id = down1.category_id
left outer
join Dir_categories as down3
on down3.parent_id = down2.category_id
where root.parent_id =0
order
by root_name
, down1_name
, down2_name
, down3_name
It displays something like the below, but that is not what I am looking for. I am looking for a tree view that looks like this. Check this website out http://www.9searches.org/sitemap.asp I did this by doing four queries one inside the other, but it i s too slow. I want performance...
======================================
Business Industry Specific Automotive Auto Sales
Business Industry Specific Automotive Car Accessories
Business Industry Specific Automotive Diesel Mechanics
Business Industry Specific Automotive Exotic and Classic Vehicles
Business Industry Specific Automotive General Enhancements
Business Industry Specific Automotive High Performance
Business Industry Specific Automotive Racing
Business Industry Specific Automotive Repair and Maintenance
Business Industry Specific Automotive Road Services
Business Industry Specific Construction NULL
Business Industry Specific Energy and Utilities NULL
Business Law Branches of Law Disabilities
Business Law Branches of Law Employment
============================================
My initial query was
Here is the modified query, the output is still disorganized, it looks closer to what I am looking for though. Can you let me know what needs to be changed?
================================================== ======
WITH Tree(Category_ID, Parent_ID, Category_Name, Path, Hlevel)
AS
(
SELECT Category_ID, Parent_ID, Category_Name, PathID, 0 as Hlevel
FROM Dir_Categories
WHERE Parent_ID = 0
UNION ALL
SELECT e.Category_ID, e.Parent_ID, e.Category_Name, e.PathID, Hlevel + 1
FROM Dir_Categories e
INNER JOIN Tree d
ON e.Parent_ID = d.Category_ID
)
SELECT Category_ID, Parent_ID, HLevel,
Category_Name = Replicate('__', HLevel) + Category_Name, Path
FROM Tree order by Category_ID, Path;
GO
This query produces this output, Tree like but not within corresponding parent categories
==================================
1 0 0 Arts & Humanities
2 0 0 Business
3 0 0 Children
4 0 0 Computers
5 0 0 Education
6 0 0 Entertainment
7 0 0 Home & Garden
8 0 0 Health & Fitness
9 0 0 News & Politics
10 0 0 People & Society
11 0 0 Real Estate
12 0 0 Recreation & Sports
13 0 0 Science & Technology
14 0 0 Shopping
15 0 0 Travel
16 1 1 |__Books and Literature
17 1 1 |__Philosophy
18 1 1 |__Antiques
19 1 1 |__Arts
20 1 1 |__Humanities
21 1 1 |__Museums
22 1 1 |__Organizations
23 16 2 |__|__Art Books
24 16 2 |__|__Authors
25 16 2 |__|__Banned Books
26 16 2 |__|__Best-Seller Lists
27 16 2 |__|__Bookstores and Booksellers
28 16 2 |__|__Book Reading Groups
29 16 2 |__|__Downloadable Texts
30 16 2 |__|__By Genre
31 16 2 |__|__Magazines and e-Zines
32 16 2 |__|__Periods and Movements
33 16 2 |__|__Publishers
34 16 2 |__|__Reviews
35 17 2 |__|__Academic Departments
36 17 2 |__|__Ancient Greek Philosophy
37 17 2 |__|__Axiology
38 17 2 |__|__Chats and Forums
39 17 2 |__|__Chinese Philosophy
40 17 2 |__|__Continental Philosophy
41 17 2 |__|__Current Movements
42 17 2 |__|__Eastern
43 17 2 |__|__Epistemology
44 17 2 |__|__Ethics
45 17 2 |__|__German Idealism
46 17 2 |__|__History
47 17 2 |__|__Humanism
48 17 2 |__|__Journals
49 17 2 |__|__Logic
50 17 2 |__|__Metaphysics
51 17 2 |__|__New Age
52 17 2 |__|__Organizations
53 17 2 |__|__Personal Pages
54 17 2 |__|__Philosophers
55 17 2 |__|__Philosophy of Religion
56 17 2 |__|__Philosophy of Science
57 17 2 |__|__Reference
58 18 2 |__|__Antique Jewelry
59 18 2 |__|__Restoration
60 19 2 |__|__Animation
61 19 2 |__|__Arts Publications
62 19 2 |__|__Arts Resources
63 19 2 |__|__Art Exhibits
64 19 2 |__|__Cartoons
65 19 2 |__|__Design Arts
66 19 2 |__|__Education
67 19 2 |__|__Museums
68 19 2 |__|__Performing Arts
69 19 2 |__|__Visual Arts
70 19 2 |__|__Writers Resources
71 20 2 |__|__Books and Literature
72 20 2 |__|__Classical Studies
73 20 2 |__|__Cultural Studies
74 20 2 |__|__History
75 20 2 |__|__Languages
76 20 2 |__|__Linguistics
77 20 2 |__|__Philosophy
78 21 2 |__|__Maritime Museums
79 21 2 |__|__Railway Museums
80 21 2 |__|__Aviation Museums
81 21 2 |__|__Children Museums
82 21 2 |__|__Science Museums
83 21 2 |__|__Museums
84 22 2 |__|__National Endowment for the Arts
85 22 2 |__|__National Endowment for the Humanities
86 2 1 |__Comodities
87 2 1 |__Computing
88 2 1 |__Finance
89 2 1 |__Global Commerce and Government
90 2 1 |__Human Resources
92 2 1 |__Industry Specific
93 2 1 |__Law
94 2 1 |__Management
95 2 1 |__News and Resources
96 2 1 |__Non Profit
97 2 1 |__Professional Associations and Organizations
98 2 1 |__Sales and Marketing
99 2 1 |__Small Business
100 3 1 |__Toys
101 3 1 |__Family Entertainment
102 3 1 |__Health
103 3 1 |__Pets and Animals
104 3 1 |__Recreation
105 3 1 |__Schools
106 3 1 |__Sports
107 3 1 |__Nutrition
108 3 1 |__Children Books
109 3 1 |__Computer Related
110 4 1 |__Algorithms
111 4 1 |__Artificial Intelligence|||okay, try this --select root.Category_Name as root_name
, down1.Category_Name as down1_name
, down2.Category_Name as down2_name
, down3.Category_Name as down3_name
from Dir_categories as root
inner
join Dir_categories as down1
on down1.Parent_ID = root.Category_ID
inner
join Dir_categories as down2
on down2.Parent_ID = down1.Category_ID
inner
join Dir_categories as down3
on down3.Parent_ID = down2.Category_ID
where root.Parent_ID = 0
UNION ALL
select root.Category_Name
, down1.Category_Name
, down2.Category_Name
, null
from Dir_categories as root
inner
join Dir_categories as down1
on down1.Parent_ID = root.Category_ID
inner
join Dir_categories as down2
on down2.Parent_ID = down1.Category_ID
where root.Parent_ID = 0
UNION ALL
select root.Category_Name
, down1.Category_Name
, null
, null
from Dir_categories as root
inner
join Dir_categories as down1
on down1.Parent_ID = root.Category_ID
where root.Parent_ID = 0
UNION ALL
select root.Category_Name
, null
, null
, null
from Dir_categories as root
where root.Parent_ID = 0
order
by root_name
, down1_name
, down2_name
, down3_name|||It is just the output. it does not look like a |__TREE
|__LEAF
View|||okay, you are right, it does not show the |__ marks
note that you need the first 4 columns of the result set in order to sort the results properly
now we add one more column -- a concatenation of your marks, along with the name of the node
so what you will do is run the following query, but display only the 5th column as your hierarchy
select root.Category_Name as root_name
, down1.Category_Name as down1_name
, down2.Category_Name as down2_name
, down3.Category_Name as down3_name
, '|_|_|_' + down3.Category_Name as data_to_display
from Dir_categories as root
inner
join Dir_categories as down1
on down1.Parent_ID = root.Category_ID
inner
join Dir_categories as down2
on down2.Parent_ID = down1.Category_ID
inner
join Dir_categories as down3
on down3.Parent_ID = down2.Category_ID
where root.Parent_ID = 0
UNION ALL
select root.Category_Name
, down1.Category_Name
, down2.Category_Name
, null
, '|_|_' + down2.Category_Name as data_to_display
from Dir_categories as root
inner
join Dir_categories as down1
on down1.Parent_ID = root.Category_ID
inner
join Dir_categories as down2
on down2.Parent_ID = down1.Category_ID
where root.Parent_ID = 0
UNION ALL
select root.Category_Name
, down1.Category_Name
, null
, null
, '|_' + down1.Category_Name as data_to_display
from Dir_categories as root
inner
join Dir_categories as down1
on down1.Parent_ID = root.Category_ID
where root.Parent_ID = 0
UNION ALL
select root.Category_Name
, null
, null
, null
, root.Category_Name as data_to_display
from Dir_categories as root
where root.Parent_ID = 0
order
by root_name
, down1_name
, down2_name
, down3_name
Category_ID Number
Parent_ID Number <--Category_ID reports to this colum
Category_Name Varchar...
MY QUERY <--I replaced the query above with my data
=============================
WITH Hierarchy(Category_ID, Category_Name, Parent_ID, HLevel)
AS
(
SELECT Category_ID, Category_Name, Parent_ID, 0 as HLevel FROM Dir_Categories
UNION ALL
SELECT SubCategory.Category_ID
, SubCategory.Category_Name,
SubCategory.Parent_ID,
HLevel + 1
FROM Dir_Categories SubCategory
INNER JOIN Hierarchy ParentCategory
ON SubCategory.Parent_ID = ParentCategory.Category_ID )
SELECT Category_ID,
Category_Name = Replicate('__', HLevel) + Category_Name,
Parent_ID,
HLevel
FROM Hierarchy
My OUTPUT============
All the categories under reporting to Parent_ID 0 or continuous, then the ones reporting to 1 and so fourth. Subcategories are not showing within their main categories. I AM GOING NUTS WITH THIS.
Can you help me please?One golden rule in database systems is: Never expect the rows to appear in a specific order. If you for some reason need the columns to be in a specific order, use order by on a suitable column. Here you don't have one, sou you'll have to add a column that you can use for sorting, for instance a varchar column containing the list of IDs from root to the node.|||One golden rule in database systems is: Never expect the rows to appear in a specific order. If you for some reason need the columns to be in a specific order, use order by on a suitable column. Here you don't have one, sou you'll have to add a column that you can use for sorting, for instance a varchar column containing the list of IDs from root to the node.
Hmm, I actually have one that lists the category IDs from root to node. That is a good Ideal, I will try that and get back to you.
Thanks|||One golden rule in database systems is: Never expect the rows to appear in a specific order. If you for some reason need the columns to be in a specific order, use order by on a suitable column. Here you don't have one, sou you'll have to add a column that you can use for sorting, for instance a varchar column containing the list of IDs from root to the node.
Look at this output, look at the PathID, can you suggest how I can order that to get the subcategories inside the Categories? I am more of a web guy, and can generally do some good querying but I am better in Oracle.
Check it out:
============================
PATHID
1
10
100,3
101,3
102,3
103,3
104,3
105,3
106,3
107,3
108,3
109,3
11
110,4
111,4
112,4
113,4
114,4
115,4
116,4
117,4
118,4
119,4
12
120,4
121,4
122,4
123,4
124,4
125,4
126,4
127,4
128,4
129,4
13
130,4
131,4
132,4
133,5
134,5
135,5
136,5
137,5
138,5
139,5
14
140,5
141,5
142,5
143,5
144,5
145,5
146,5
147,5
148,5
149,5
15
150,6
151,6
152,6
153,6
154,6
155,6
156,6
157,6
158,6
159,6
16,1
160,6
161,6
162,7
163,7
164,7
165,7
166,7
167,7
168,7
169,8
17,1
170,8
171,8
172,8
173,8
174,8
175,8
176,8
177,8
178,8
179,8
18,1
180,8
181,8
182,8
183,8
184,8
185,8
186,8
187,8
189,8
19,1
190,8
191,8
192,8
193,9
194,9
195,9
196,9
197,9
198,9
199,9
2
20,1
200,9
201,9
202,9
203,9
204,10
205,10
206,10
207,10
208,10
209,10
21,1
210,10
211,10
212,10
213,10
214,10
215,10
216,11
217,11
218,11
219,11
22,1
220,11
221,11
222,11
223,11
224,11
225,11
226,11
227,11
228,11
229,11
23,16,1
230,11
231,11
232,11
233,12
234,12
235,12
236,13
237,13
238,14
239,14
24,16,1
240,14
241,14
242,14
243,14
245,14
246,14
247,14
248,14
249,14
25,16,1
250,14
252,14
253,14
254,14
255,14
256,14
257,14
258,14
259,14
26,16,1
260,14
261,14
262,14
263,14
264,14
265,14
266,14
267,14
268,14
269,14
27,16,1
270,14
271,14
272,15
273,15
274,15
275,15
276,15
277,15
278,15
279,15
28,16,1
280,15
281,15
282
283,282
284,282
285,282
286,282
287,282
288,282
289,282
29,16,1
290,282
291,282
292,282
293,282
294,282
295,282
296,86,2
297,86,2
298,86,2
299,86,2
3
30,16,1
300,86,2
301,86,2
302,86,2
303,86,2
304,86,2
305,86,2
309,87,2
31,16,1
310,87,2
311,87,2
312,87,2
313,88,2
314,88,2
315,88,2
316,88,2
317,88,2
318,88,2
319,88,2
32,16,1
320,88,2
322,88,2
323,88,2
324,89,2
325,89,2
326,89,2
327,89,2
328,89,2
329,89,2
33,16,1
330,89,2
331,89,2
332,89,2
333,89,2
334,89,2
335,89,2
336,89,2
337,89,2
338,89,2
339,89,2
34,16,1
340,90,2
341,90,2
342,90,2
343,90,2
344,90,2
345,90,2
346,90,2
347,90,2
348,90,2
349,90,2
35,17,1
350,90,2
351,90,2
352,90,2
353,92,2
354,92,2
355,92,2
356,92,2
357,92,2
358,92,2
359,92,2
36,17,1
360,92,2
361,92,2
362,92,2
363,92,2
364,92,2
365,93,2
366,93,2
367,93,2
368,93,2
369,93,2
37,17,1
370,93,2
371,93,2
372,366,93,2
373,366,93,2
374,366,93,2
375,366,93,2
376,366,93,2
377,366,93,2
378,366,93,2
379,366,93,2
38,17,1
380,366,93,2
381,366,93,2
382,366,93,2
383,366,93,2
384,366,93,2
385,366,93,2
386,366,93,2
387,366,93,2
388,366,93,2
389,366,93,2
39,17,1
390,366,93,2
391,366,93,2
392,366,93,2
393,94,2
394,94,2
395,94,2
396,94,2
397,94,2
398,94,2
399,94,2
4
40,17,1
400,94,2
401,94,2
402,94,2
403,94,2
404,94,2
405,94,2
406,95,2
407,95,2
408,95,2
409,95,2
41,17,1
410,95,2
411,95,2
412,95,2
413,95,2
414,95,2
415,95,2
416,96,2
417,96,2
418,96,2
419,96,2
42,17,1
420,96,2
421,96,2
422,96,2
423,96,2
424,96,2
425,96,2
426,96,2
427,98,2
428,98,2
429,98,2
43,17,1
430,98,2
431,98,2
432,98,2
433,98,2
434,98,2
435,98,2
436,98,2
437,98,2
438,98,2
439,98,2
44,17,1
440,110,4
441,110,4
442,110,4
443,110,4
444,110,4
445,110,4
45,17,1
450,110,4
451,110,4
452,111,4
453,111,4
454,111,4
455,111,4
456,111,4
457,111,4
458,111,4
459,111,4
46,17,1
460,111,4
461,111,4
462,111,4
463,111,4
464,111,4
465,111,4
466,111,4
467,111,4
468,111,4
469,111,4
47,17,1
470,111,4
471,112,4
472,112,4
473,112,4
474,112,4
475,111,4
476,112,4
477,112,4
478,112,4
479,112,4
48,17,1
480,112,4
481,112,4
482,112,4
483,112,4
484,112,4
485,112,4
489,109,3
49,17,1
490,109,3
491,109,3
492,109,3
493,109,3
494,109,3
495,109,3
496,109,3
497,101,3
498,101,3
499,101,3
5
50,17,1
500,101,3
501,101,3
502,101,3
503,101,3
504,101,3
505,102,3
506,102,3
507,102,3
508
509,508
51,17,1
510,508
511,508
512,508
513,508
514,508
515,508
516,508
517,508
518,508
519,508
52,17,1
520,508
521,92,2
522,521,92,2
523,521,92,2
524,521,92,2
525,521,92,2
526,521,92,2
527,521,92,2
528,521,92,2
529,521,92,2
53,17,1
530,521,92,2
531,101,3
532,537,536
533,537,536
534,537,536
535,537,536
536
537,536
538,536
539,234,12
54,17,1
540,234,12
541,234,12
542,234,12
543,234,12
545,234,12
546,234,12
547,234,12
548,282
549,2
55,17,1
550,7
56,17,1
560,3
561,312,87,2
562,130,4
563,234,12
564,130,4
565,7
566,7
567,1
568,130,4
569,7
57,17,1
570,234,12
571,234,12
572,2
573,572,2
574,234,12
58,18,1
59,18,1
6
60,19,1
61,19,1
62,19,1
63,19,1
64,19,1
65,19,1
66,19,1
67,19,1
68,19,1
69,19,1
7
70,19,1
71,20,1
72,20,1
73,20,1
74,20,1
75,20,1
76,20,1
77,20,1
78,21,1
79,21,1
8
80,21,1
81,21,1
82,21,1
83,21,1
84,22,1
85,22,1
86,2
87,2
88,2
89,2
9
90,2
92,2
93,2
94,2
95,2
96,2
97,2
98,2
99,2|||i dunno, man, you lost me somewhere around 237,13 ;)
you're a web guy? then you know that an unlimited number of levels is likely not workable?
how many users are willing to drill down fifteen levels to find where they're going?
if you can limit the hierarchy to some fixed maximum number of levels, then you can use a series of self-joins instead of a recursive query (although of course a recursive query will work with a fixed number of levels too)
perhaps this article may help: Categories and Subcategories (http://sqllessons.com/categories.html)
and yes, there is no problem with fifteen self-joins in a query
:)|||Here is the modified query, the output is still disorganized, it looks closer to what I am looking for though. Can you let me know what needs to be changed?
================================================== ======
WITH Tree(Category_ID, Parent_ID, Category_Name, Path, Hlevel)
AS
(
SELECT Category_ID, Parent_ID, Category_Name, PathID, 0 as Hlevel
FROM Dir_Categories
WHERE Parent_ID = 0
UNION ALL
SELECT e.Category_ID, e.Parent_ID, e.Category_Name, e.PathID, Hlevel + 1
FROM Dir_Categories e
INNER JOIN Tree d
ON e.Parent_ID = d.Category_ID
)
SELECT Category_ID, Parent_ID, HLevel,
Category_Name = Replicate('__', HLevel) + Category_Name, Path
FROM Tree order by Category_ID, Path;
GO|||i dunno, man, you lost me somewhere around 237,13 ;)
you're a web guy? then you know that an unlimited number of levels is likely not workable?
how many users are willing to drill down fifteen levels to find where they're going?
if you can limit the hierarchy to some fixed maximum number of levels, then you can use a series of self-joins instead of a recursive query (although of course a recursive query will work with a fixed number of levels too)
perhaps this article may help: Categories and Subcategories (http://sqllessons.com/categories.html)
and yes, there is no problem with fifteen self-joins in a query
:)
I am not going further deeper than 4 levels. It is a web directory and Search Engines dont go that deep so I keep it no more than four. Again, you are talking about self joins and all that but maybe some examples will help, I will take a look at the article. THanks|||i dunno, man, you lost me somewhere around 237,13 ;)
you're a web guy? then you know that an unlimited number of levels is likely not workable?
how many users are willing to drill down fifteen levels to find where they're going?
if you can limit the hierarchy to some fixed maximum number of levels, then you can use a series of self-joins instead of a recursive query (although of course a recursive query will work with a fixed number of levels too)
perhaps this article may help: Categories and Subcategories (http://sqllessons.com/categories.html)
and yes, there is no problem with fifteen self-joins in a query
:)
Thanks, I applied this query and although it is very useful, it doesnt produce the display that i am looking for. And quite honestly it would be too much coding to take the data and create a fabricated HTML output that looks like a tree. It is the only thing that has worked so far though.
AAAHHHHRRGGG, this is so simple in Oracle, just using Connect By. The problem is that I have a shared windows hosting and they only have SQL server. Oracle hosting is too expensive and for the purpose I am developing this, I want anyone who I extend the code to be able to use SQL shared hosting which is the most common one.
Any other resources or suggestions?|||can you show the query that you tried? because honestly, 4 self-joins is nothing complicated
and would you mind repeating what sort of display you are looking for|||can you show the query that you tried? because honestly, 4 self-joins is nothing complicated
and would you mind repeating what sort of display you are looking for
Here is the query I tried now
===================================
select root.Category_Name as root_name
, down1.category_name as down1_name
, down2.category_name as down2_name
, down3.category_name as down3_name
from Dir_categories as root
left outer
join Dir_categories as down1
on down1.parent_id = root.Category_ID
left outer
join Dir_categories as down2
on down2.parent_id = down1.category_id
left outer
join Dir_categories as down3
on down3.parent_id = down2.category_id
where root.parent_id =0
order
by root_name
, down1_name
, down2_name
, down3_name
It displays something like the below, but that is not what I am looking for. I am looking for a tree view that looks like this. Check this website out http://www.9searches.org/sitemap.asp I did this by doing four queries one inside the other, but it i s too slow. I want performance...
======================================
Business Industry Specific Automotive Auto Sales
Business Industry Specific Automotive Car Accessories
Business Industry Specific Automotive Diesel Mechanics
Business Industry Specific Automotive Exotic and Classic Vehicles
Business Industry Specific Automotive General Enhancements
Business Industry Specific Automotive High Performance
Business Industry Specific Automotive Racing
Business Industry Specific Automotive Repair and Maintenance
Business Industry Specific Automotive Road Services
Business Industry Specific Construction NULL
Business Industry Specific Energy and Utilities NULL
Business Law Branches of Law Disabilities
Business Law Branches of Law Employment
============================================
My initial query was
Here is the modified query, the output is still disorganized, it looks closer to what I am looking for though. Can you let me know what needs to be changed?
================================================== ======
WITH Tree(Category_ID, Parent_ID, Category_Name, Path, Hlevel)
AS
(
SELECT Category_ID, Parent_ID, Category_Name, PathID, 0 as Hlevel
FROM Dir_Categories
WHERE Parent_ID = 0
UNION ALL
SELECT e.Category_ID, e.Parent_ID, e.Category_Name, e.PathID, Hlevel + 1
FROM Dir_Categories e
INNER JOIN Tree d
ON e.Parent_ID = d.Category_ID
)
SELECT Category_ID, Parent_ID, HLevel,
Category_Name = Replicate('__', HLevel) + Category_Name, Path
FROM Tree order by Category_ID, Path;
GO
This query produces this output, Tree like but not within corresponding parent categories
==================================
1 0 0 Arts & Humanities
2 0 0 Business
3 0 0 Children
4 0 0 Computers
5 0 0 Education
6 0 0 Entertainment
7 0 0 Home & Garden
8 0 0 Health & Fitness
9 0 0 News & Politics
10 0 0 People & Society
11 0 0 Real Estate
12 0 0 Recreation & Sports
13 0 0 Science & Technology
14 0 0 Shopping
15 0 0 Travel
16 1 1 |__Books and Literature
17 1 1 |__Philosophy
18 1 1 |__Antiques
19 1 1 |__Arts
20 1 1 |__Humanities
21 1 1 |__Museums
22 1 1 |__Organizations
23 16 2 |__|__Art Books
24 16 2 |__|__Authors
25 16 2 |__|__Banned Books
26 16 2 |__|__Best-Seller Lists
27 16 2 |__|__Bookstores and Booksellers
28 16 2 |__|__Book Reading Groups
29 16 2 |__|__Downloadable Texts
30 16 2 |__|__By Genre
31 16 2 |__|__Magazines and e-Zines
32 16 2 |__|__Periods and Movements
33 16 2 |__|__Publishers
34 16 2 |__|__Reviews
35 17 2 |__|__Academic Departments
36 17 2 |__|__Ancient Greek Philosophy
37 17 2 |__|__Axiology
38 17 2 |__|__Chats and Forums
39 17 2 |__|__Chinese Philosophy
40 17 2 |__|__Continental Philosophy
41 17 2 |__|__Current Movements
42 17 2 |__|__Eastern
43 17 2 |__|__Epistemology
44 17 2 |__|__Ethics
45 17 2 |__|__German Idealism
46 17 2 |__|__History
47 17 2 |__|__Humanism
48 17 2 |__|__Journals
49 17 2 |__|__Logic
50 17 2 |__|__Metaphysics
51 17 2 |__|__New Age
52 17 2 |__|__Organizations
53 17 2 |__|__Personal Pages
54 17 2 |__|__Philosophers
55 17 2 |__|__Philosophy of Religion
56 17 2 |__|__Philosophy of Science
57 17 2 |__|__Reference
58 18 2 |__|__Antique Jewelry
59 18 2 |__|__Restoration
60 19 2 |__|__Animation
61 19 2 |__|__Arts Publications
62 19 2 |__|__Arts Resources
63 19 2 |__|__Art Exhibits
64 19 2 |__|__Cartoons
65 19 2 |__|__Design Arts
66 19 2 |__|__Education
67 19 2 |__|__Museums
68 19 2 |__|__Performing Arts
69 19 2 |__|__Visual Arts
70 19 2 |__|__Writers Resources
71 20 2 |__|__Books and Literature
72 20 2 |__|__Classical Studies
73 20 2 |__|__Cultural Studies
74 20 2 |__|__History
75 20 2 |__|__Languages
76 20 2 |__|__Linguistics
77 20 2 |__|__Philosophy
78 21 2 |__|__Maritime Museums
79 21 2 |__|__Railway Museums
80 21 2 |__|__Aviation Museums
81 21 2 |__|__Children Museums
82 21 2 |__|__Science Museums
83 21 2 |__|__Museums
84 22 2 |__|__National Endowment for the Arts
85 22 2 |__|__National Endowment for the Humanities
86 2 1 |__Comodities
87 2 1 |__Computing
88 2 1 |__Finance
89 2 1 |__Global Commerce and Government
90 2 1 |__Human Resources
92 2 1 |__Industry Specific
93 2 1 |__Law
94 2 1 |__Management
95 2 1 |__News and Resources
96 2 1 |__Non Profit
97 2 1 |__Professional Associations and Organizations
98 2 1 |__Sales and Marketing
99 2 1 |__Small Business
100 3 1 |__Toys
101 3 1 |__Family Entertainment
102 3 1 |__Health
103 3 1 |__Pets and Animals
104 3 1 |__Recreation
105 3 1 |__Schools
106 3 1 |__Sports
107 3 1 |__Nutrition
108 3 1 |__Children Books
109 3 1 |__Computer Related
110 4 1 |__Algorithms
111 4 1 |__Artificial Intelligence|||okay, try this --select root.Category_Name as root_name
, down1.Category_Name as down1_name
, down2.Category_Name as down2_name
, down3.Category_Name as down3_name
from Dir_categories as root
inner
join Dir_categories as down1
on down1.Parent_ID = root.Category_ID
inner
join Dir_categories as down2
on down2.Parent_ID = down1.Category_ID
inner
join Dir_categories as down3
on down3.Parent_ID = down2.Category_ID
where root.Parent_ID = 0
UNION ALL
select root.Category_Name
, down1.Category_Name
, down2.Category_Name
, null
from Dir_categories as root
inner
join Dir_categories as down1
on down1.Parent_ID = root.Category_ID
inner
join Dir_categories as down2
on down2.Parent_ID = down1.Category_ID
where root.Parent_ID = 0
UNION ALL
select root.Category_Name
, down1.Category_Name
, null
, null
from Dir_categories as root
inner
join Dir_categories as down1
on down1.Parent_ID = root.Category_ID
where root.Parent_ID = 0
UNION ALL
select root.Category_Name
, null
, null
, null
from Dir_categories as root
where root.Parent_ID = 0
order
by root_name
, down1_name
, down2_name
, down3_name|||It is just the output. it does not look like a |__TREE
|__LEAF
View|||okay, you are right, it does not show the |__ marks
note that you need the first 4 columns of the result set in order to sort the results properly
now we add one more column -- a concatenation of your marks, along with the name of the node
so what you will do is run the following query, but display only the 5th column as your hierarchy
select root.Category_Name as root_name
, down1.Category_Name as down1_name
, down2.Category_Name as down2_name
, down3.Category_Name as down3_name
, '|_|_|_' + down3.Category_Name as data_to_display
from Dir_categories as root
inner
join Dir_categories as down1
on down1.Parent_ID = root.Category_ID
inner
join Dir_categories as down2
on down2.Parent_ID = down1.Category_ID
inner
join Dir_categories as down3
on down3.Parent_ID = down2.Category_ID
where root.Parent_ID = 0
UNION ALL
select root.Category_Name
, down1.Category_Name
, down2.Category_Name
, null
, '|_|_' + down2.Category_Name as data_to_display
from Dir_categories as root
inner
join Dir_categories as down1
on down1.Parent_ID = root.Category_ID
inner
join Dir_categories as down2
on down2.Parent_ID = down1.Category_ID
where root.Parent_ID = 0
UNION ALL
select root.Category_Name
, down1.Category_Name
, null
, null
, '|_' + down1.Category_Name as data_to_display
from Dir_categories as root
inner
join Dir_categories as down1
on down1.Parent_ID = root.Category_ID
where root.Parent_ID = 0
UNION ALL
select root.Category_Name
, null
, null
, null
, root.Category_Name as data_to_display
from Dir_categories as root
where root.Parent_ID = 0
order
by root_name
, down1_name
, down2_name
, down3_name
Subscribe to:
Posts (Atom)