Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » Elevate Web Builder Technical Support » Support Forums » Elevate Web Builder General » View Thread |
Messages 1 to 10 of 10 total |
SQL help within EWB |
Fri, Jul 6 2018 12:49 AM | Permanent Link |
Paul Coshott | Hi All,
I am trying to create an SQL dataset within the EWB Database Manager. I have a table of Staff members and another table containing time off requests. The request table has a column called Approved (integer) where -1 = new request 0 = request denied 1 = request approved What I want to end up with, is a list of all staff members (regardless of whether they have any requests - so Left Join), and an indicator on each returned row that is true if the staff member currently has any new requests, and false if they don't (so false if they have no requests at all, or if they only have denied or approved requests). I only want each staff member to appear once in the list (distinct). How do I write this SQL? Cheers, Paul |
Fri, Jul 6 2018 5:52 AM | Permanent Link |
Uli Becker | Paul,
> How do I write this SQL? You should ask this question in the SQL Newsgroup: https://www.elevatesoft.com/forums?action=view&category=edb&id=edb_sql and post what you've tried so far. Uli |
Fri, Jul 6 2018 12:52 PM | Permanent Link |
Paul Coshott | Hi Uli,
> You should ask this question in the SQL Newsgroup: Thanks for the reply. I can't do that, as I'm using a trial version of ElevateDB, so I'm not able to ask a question. Cheers, Paul |
Fri, Jul 6 2018 3:49 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Paul,
<< I am trying to create an SQL dataset within the EWB Database Manager. I have a table of Staff members and another table containing time off requests. The request table has a column called Approved (integer) where -1 = new request 0 = request denied 1 = request approved What I want to end up with, is a list of all staff members (regardless of whether they have any requests - so Left Join), and an indicator on each returned row that is true if the staff member currently has any new requests, and false if they don't (so false if they have no requests at all, or if they only have denied or approved requests). I only want each staff member to appear once in the list (distinct). >> You should be able to use this statement to do what you want: SELECT ID, COALESCE(ApprovedRequests.ID,False) AS HasApprovedRequests, FROM Staff LEFT OUTER JOIN (SELECT DISTINCT ID FROM Requests WHERE Requests.Approved = -1) ApprovedRequests ON Staff.ID=ApprovedRequests.ID I didn't test this out, though, so I may have missed something. Tim Young Elevate Software www.elevatesoft.com |
Mon, Jul 9 2018 3:33 AM | Permanent Link |
Paul Coshott | Hi Tim,
Thanks for help. I think I'm almost there, but I'm getting an error on the last line: "ElevateDB Error #401 The column ID does not exist in the derived table ApprovedRequests" SELECT StaffID, COALESCE(ApprovedRequests.ID,False) AS HasApprovedRequests FROM Staff LEFT OUTER JOIN (SELECT DISTINCT StaffID FROM StaffTimeOff WHERE StaffTimeOff.Approved = -1) ApprovedRequests ON Staff.StaffID=ApprovedRequests.ID The table structures are: STAFF ---------- StaffId Integer Name VarChar(50) Mobile VarChar(12) STAFFTIMEOFF ------------------------ TimeOffId Integer StaffId Integer ReqDate Date ReqStartTime Time ReqEndTime Time Reason VarChar(100) Approved Integer // -1=New Request, 0=Rejected Request, 1=Approved Request |
Mon, Jul 9 2018 12:40 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Paul,
<< Thanks for help. I think I'm almost there, but I'm getting an error on the last line: >> Your ID column name is wrong: SELECT StaffID, COALESCE(ApprovedRequests.StaffID,False) AS HasApprovedRequests FROM Staff LEFT OUTER JOIN (SELECT DISTINCT StaffID FROM StaffTimeOff WHERE StaffTimeOff.Approved = -1) ApprovedRequests ON Staff.StaffID=ApprovedRequests.StaffID Tim Young Elevate Software www.elevatesoft.com |
Mon, Jul 9 2018 11:42 PM | Permanent Link |
Paul Coshott | Hi Tim,
> SELECT StaffID, COALESCE(ApprovedRequests.StaffID,False) AS HasApprovedRequests The False in the above line was still causing an error. I changed it to -99 and the SQL then ran. The result I got was any staff without a new request got a value of -99 in the "HasApprovedRequests" column. If the staff member had a new request outstanding, then the "HasApprovedRequests" column had a value of whatever the StaffId was for that staff member. Is this the right way to do it? Cheers, Paul |
Tue, Jul 10 2018 3:59 AM | Permanent Link |
Uli Becker | Paul,
> The False in the above line was still causing an error. I changed it to -99 and the SQL then ran. The result I got was any staff without a new request got a value of -99 in the "HasApprovedRequests" column. How about this: SELECT s.Name, t.Approved, IF (t.Approved=-1 THEN TRUE ELSE FALSE) AS HasApprovedRequests FROM Staff s LEFT OUTER JOIN StaffTimeOff t ON s.StaffID = t.StaffID Uli |
Fri, Jul 13 2018 1:00 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Paul,
<< The False in the above line was still causing an error. I changed it to -99 and the SQL then ran. The result I got was any staff without a new request got a value of -99 in the "HasApprovedRequests" column. >> Yeah, sorry, I forgot that it was dealing with an integer column. Uli's response is correct. The main thing is to detect the NULLs being generated by the LOJ and then casting them into the representation that you wish. Tim Young Elevate Software www.elevatesoft.com |
Mon, Jul 16 2018 10:02 PM | Permanent Link |
Paul Coshott | Hi Uli and Tim,
Awesome, thanks for the help. That'll make things a lot simpler. Cheers, Paul |
This web page was last updated on Friday, March 29, 2024 at 03:30 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |