Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > Pro VB 6
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Pro VB 6 For advanced Visual Basic coders working in version 6 (not .NET). Beginning-level questions will be redirected to other forums, including Beginning VB 6.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Pro VB 6 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old September 22nd, 2003, 11:44 AM
Registered User
 
Join Date: Sep 2003
Location: Hampshire, , United Kingdom.
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Natalie
Default Tree View

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!!!
Reply With Quote
  #2 (permalink)  
Old September 22nd, 2003, 12:20 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Cardiff, , United Kingdom.
Posts: 231
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #3 (permalink)  
Old September 22nd, 2003, 01:21 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #4 (permalink)  
Old September 23rd, 2003, 06:01 AM
Registered User
 
Join Date: Sep 2003
Location: Hampshire, , United Kingdom.
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Natalie
Default

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!!
Reply With Quote
  #5 (permalink)  
Old September 23rd, 2003, 07:32 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Cardiff, , United Kingdom.
Posts: 231
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #6 (permalink)  
Old September 23rd, 2003, 08:23 AM
Registered User
 
Join Date: Sep 2003
Location: Hampshire, , United Kingdom.
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Natalie
Default

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!! :)
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Tree View dotnetDeveloper ASP.NET 3.5 Basics 1 November 26th, 2008 05:05 PM
Tree view control g_vamsi_krish ASP.NET 1.0 and 1.1 Basics 2 April 5th, 2006 12:02 AM
Tree View mistry_bhavin ASP.NET 1.x and 2.0 Application Design 1 August 3rd, 2005 04:58 PM
help me on tree view gps_giri_p General .NET 0 December 13th, 2004 10:36 AM
tree view msrnivas .NET Web Services 0 February 23rd, 2004 07:04 AM



All times are GMT -4. The time now is 11:34 AM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.