Pro VB 6For 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 .
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!!
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.
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...
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!!
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.
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!! :)
Tree View
Linear Mode
