Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 10 of 10 total
Thread SQL help within EWB
Fri, Jul 6 2018 12:49 AMPermanent Link

Paul Coshott

Avatar

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 AMPermanent 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 PMPermanent Link

Paul Coshott

Avatar

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 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email 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 AMPermanent Link

Paul Coshott

Avatar

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 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email 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 PMPermanent Link

Paul Coshott

Avatar

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 AMPermanent 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 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email 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 PMPermanent Link

Paul Coshott

Avatar

Hi Uli and Tim,

Awesome, thanks for the help. That'll make things a lot simpler.

Cheers,
Paul
Image