Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 9 of 9 total |
Can this be done? |
Fri, Feb 13 2009 10:02 PM | Permanent Link |
darrell | Given the following table structure
ClientID Points StaffID1 StaffID2 StaffID3 Each of the staffid<n> fields may be null or contain a staff id - any staffid can only show up once per row (if at all) can I return a result set that lists (sorted by staff id, subsorted by client id) the client ids and the points per staff id regardless of which field the staffid was found in? thanks for any help darrell |
Sat, Feb 14 2009 3:56 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | darrell
Two questions 1. What are "points per staff id"? 2. Each row can have up to three distinct staff ids? Roy Lambert [Team Elevate] |
Sat, Feb 14 2009 11:04 AM | Permanent Link |
darrell | Two questions 1. What are "points per staff id"? From table 'p' (I cannot influence table structures here) ClientID Points StaffID1 StaffID2 StaffID3 ------------------------------------------------------- 100 4 10 11 12 223 3 17 12 NULL 205 5 11 10 17 I need to create a report StaffID ClientID Points ------------------------------------- 10 100 4 205 5 11 100 4 205 5 12 100 4 223 3 17 223 3 2. Each row can have up to three distinct staff ids? Yes |
Sat, Feb 14 2009 11:37 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | darrell
A script that will almost do what you want - the bit it doesn't do is the blank staff ID where its repeated Select ClientID, Points, StaffID1 AS StaffID INTO "Memory\temp" FROM P WHERE StaffID1 IS NOT NULL; INSERT INTO "Memory\temp" SELECT ClientID, Points, StaffID2 AS StaffID FROM P WHERE StaffID2 IS NOT NULL; INSERT INTO "Memory\temp" SELECT ClientID, Points, StaffID3 AS StaffID FROM P WHERE StaffID3 IS NOT NULL; SELECT StaffID, ClientID, Points FROM "Memory\Temp" ORDER BY StaffID,ClientID; Roy Lambert [Team Elevate] |
Sat, Feb 14 2009 1:41 PM | Permanent Link |
darrell | Thanks Roy - that works fine and puts an end to my fruitless efforts of trying to do this in one statement! Darrell |
Sat, Feb 14 2009 4:59 PM | Permanent Link |
"Robert" | "darrell" <dfm@gmail.com> wrote in message news:53441408-BC65-41FD-8080-2D85A5A9C30D@news.elevatesoft.com... > > > Two questions > > 1. What are "points per staff id"? > > From table 'p' (I cannot influence table structures here) > > ClientID Points StaffID1 StaffID2 StaffID3 > ------------------------------------------------------- > 100 4 10 11 12 > 223 3 17 12 NULL > 205 5 11 10 17 > > I need to create a report > > StaffID ClientID Points > ------------------------------------- > 10 100 4 > 205 5 > 11 100 4 > 205 5 > 12 100 4 > 223 3 > 17 223 3 > SELECT STAFFID1 AS STAFFID, CLIENTID, POINTS FROM TABLE WHERE STAFFID1 <> NULL UNION SELECT STAFFID2, CLIENTID, POINTS FROM TABLE WHERE STAFFID2 <> NULL UNION SELECT STAFFID3, CLIENTID, POINTS FROM TABLE WHERE STAFFID3 <> NULL ORDER BY 1, 2 Robert > > > 2. Each row can have up to three distinct staff ids? > > Yes > |
Sun, Feb 15 2009 7:51 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Robert
I thought of using UNION but stopped because I thought the ORDER BY only applied to the SELECT statement it followed. Roy Lambert |
Sun, Feb 15 2009 2:41 PM | Permanent Link |
"Robert" | "Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message news:9D879AA1-9977-4E47-A20D-FF17D4B56934@news.elevatesoft.com... > Robert > > > I thought of using UNION but stopped because I thought the ORDER BY only > applied to the SELECT statement it followed. > Would not make much sense. The whole purpose of UNION is e pluribus unum, create one of many. The INTO (if present) on the first select, any naming of columns on the first select, and the ORDER BY at the end all apply to the whole selected dataset. BTW, there is an easy way to keep the items extracted from each dataset all together, as follows SELECT 1, FIELDA, FIELDB FROM TABLEA UNION SELECT 2, FIELDX, FIELDY FROM TABLEB ORDER BY 1, 2, 3 By creating extracted constants, you can manipulate the final order, as in SELECT 0, FIELDA, FIELDB FROM TABLEA UNION SELECT IF(FIELDM > 10 THEN 2 ELSE 1), FIELDX, FIELDY FROM TABLEB ORDER BY 1, 2, 3 Robert |
Wed, Feb 18 2009 12:40 PM | Permanent Link |
"Rita" | "Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote in message news:177EF583-FC49-4536-B109-6D2AEDE59C8F@news.elevatesoft.com... E pluribus unum Nice one Rita |
This web page was last updated on Tuesday, May 7, 2024 at 06:25 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |