Tree View

Natalie · September 22nd, 2003, 10:44 AM

I'm going out of my mind here trying to figure this problem out. I hope someone can help....

I have a treeview with many nodes in a structure not unlike this:

Mrs J Bloggs, F, 104591
   - Account
   - Orders
   - Notes
Mr N Bloggs, A, 160891
   - Account
   - Orders
   - Notes
Miss H Bloggs, J, 153945
   - Account
   - Orders
   - Notes

The root for each customer is the result of an initial SQL query and at present it is only ordering on the customer number.

The spec I am working to requires me to order initially by the customer type "F", then "A" then "J", where there will only ever be 1 "F", but may be more than 1 "A" or "J" and then order by customer number.

Anyone know how I can do this?? The ordering by Customer Number is fine and handles in the query, but I can not order by customer type!!

HELP!!!

owain · September 22nd, 2003, 11:20 AM

Try this SQL statement:

Code:

  SELECT CASE WHEN Type = 'F' THEN 1
              WHEN Type = 'A' THEN 2
              WHEN Type = 'J' THEN 3 END AS NumericType,
         [Name], Type, CustomerID
    FROM Customers
ORDER BY 1, 4

Here I have assumed that the table is called 'Customers', the name column (e.g. Mrs J Bloggs) is called 'Name' the type column (e.g. F) is called 'Type' and the CustomerID (e.g. 104591) is called 'CustomerID'

What I am doing is creating a new column in the result set called 'NumericType' which converts all type F's to 1, all type A's to 2 and all type J's to 3. It then uses this calculated column to perform the primary sort, followed by the CustomerID.

This will give you the results in the order you require. You can of course add other tables to this query as and when you need them (e.g. Account, Orders and Notes.) Just treat it like any other SQL statement.

Someone will probably point out that I have not included an ELSE in the CASE statement, I did this because I though it made the query easier to understand without it. For more information on the CASE statement have a look in SQL Server Books Online.

Regards
Owain Williams

Jeff Mason · September 22nd, 2003, 12:21 PM

You didn't include an ELSE clause in the CASE expression. :D

(But, it is not required, and if none of the predicates in a CASE expression evaluate to TRUE, then the value of the CASE expression is NULL, and your query will still work - those NULL values will sort before the other non-NULL values of 'NumericType').

P.S. Not to be pedantic here, but CASE is an expression and not a statement...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Natalie · September 23rd, 2003, 05:01 AM

Sorry if I'm sounding a bit thick, but I am new to SQL. My SQL Script at present is:-

SELECT * FROM Customer
WHERE CustomerNumber = '155803' OR FamilyMember = '155803'

UNION SELECT a.* FROM Customer a, Customer b
WHERE b.CustomerNumber = '155803'
AND a.CustomerNumber=b.FamilyMember

UNION SELECT * FROM Customer
WHERE FamilyMember = (SELECT b.CustomerNumber FROM Customer a, Customer b
WHERE a.CustomerNumber ='155803' AND a.familymember=b.CustomerNumber)

Sorry if this is in the wrong forum, but where should the Case statement go?? I am getting all sorts of errors occuring!!

Thanks for any help you can give me, it is most appreciated!!

owain · September 23rd, 2003, 06:32 AM

You will need to place the CASE expression in each SQL statement thus:

Code:

SELECT CASE Type WHEN 'F' THEN 1
                 WHEN 'A' THEN 2
                 WHEN 'J' THEN 3 END AS NumericType, *
FROM Customer 
WHERE CustomerNumber = '155803' OR FamilyMember = '155803' 

UNION SELECT CASE a.Type WHEN 'F' THEN 1
                         WHEN 'A' THEN 2
                         WHEN 'J' THEN 3 END AS NumericType, a.*
FROM Customer a, Customer b 
WHERE b.CustomerNumber = '155803' 
      AND a.CustomerNumber=b.FamilyMember 

UNION SELECT CASE Type WHEN 'F' THEN 1
                       WHEN 'A' THEN 2
                       WHEN 'J' THEN 3 END AS NumericType, *
FROM Customer 
WHERE FamilyMember = (SELECT b.CustomerNumber FROM Customer a, Customer b 
                      WHERE a.CustomerNumber ='155803' AND a.familymember=b.CustomerNumber)
ORDER BY 1, CustomerNumber

Please note that I have changed the way I have written the CASE expression to make it simpler, this time I am saying CASE Type WHEN 'F'... instead of CASE WHEN Type = 'F'... In this example the meaning has not altered (I do not know why I did not do this the first time round :).)

If I may say so, it does appear to be a peculiar set of SQL statements. If you would describe your database structure, the real world example you are trying to emulate and exactly what you want the statement to do then I may be able to come up with a simpler (and perhaps faster) solution. Then again your solution may be the best way of accomplishing what you want to do.

I am not a SQL expert, I just enjoy the challenge. I am only learning the language myself, so someone else may come up with a much better solution to what I could possibly create.

Regards
Owain Williams

Natalie · September 23rd, 2003, 07:23 AM

The SQL statement I am working with is not mine, and I am only to add to it and not change it too drastically. It is not the best way to achieve the results, but the only way I am able to write it at present!! :)

Your code worked perfectly... Your an absolute star. I enjoy a challenge as well, but when you have been staring at the piece of code for over a day, it gets quite daunting!!

Thank you ever so much for your help!! I cant thank you enough!! :)