Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 6 of 6 total |
You can't SELECT within a parent SELECT? |
Sat, Oct 15 2016 3:17 PM | Permanent Link |
jasanchez | Good day,
I need to do this: In a payroll system, I have a table named PAYITEMS. Each item has one row for the same employee, like SALARY, OVERTIME, SUNDAYS_WORKED, BONUS, etc... Now I need to print in a single row each item for the distinct. Of course cursoring the query is right. But my customer wants it in just one single SELECT. SELECT empID, payCategory, payAmount FROM PAYITEMS; This will give me the serveral rows for one single employee, right? I need to have one single row and I tried this: SELECT E.empID, E.payCategory, (SELECT D.payAmount FROM PAYITEMS D WHERE (D.empID, D.payCategory) = (E.empID, E.payCategory) AND D.payCategory = 'SALARY')) SALARY, (SELECT D.payAmount FROM PAYITEMS D WHERE (D.empID, D.payCategory) = (E.empID, E.payCategory) AND D.payCategory = 'OVERTIME')) OVERTIME, (SELECT D.payAmount FROM PAYITEMS D WHERE (D.empID, D.payCategory) = (E.empID, E.payCategory) AND D.payCategory = 'SUNDAYS_WORKED')) SUNDAYS_WORKED FROM PAYITEMS E ORDER BY empID, payCategory Does DBISAM not support a standard SQL subquery like this? Thanks Javier. |
Sun, Oct 16 2016 3:08 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | jasanchez
I don't think even the latest versions of DBISAM allow subselects (I could be wrong because I use ElevateDB now and my DBISAM is an old version), but what you can do is use a script and a memory table. Something along the lines of: SELECT EmpID, 0 AS SALARY, 0 AS OVERTIME etc FROM PAYITEMS INTO Memory\tmp; UPDATE Memory\tmp SET SALARY = X.SALARY FROM PAYITEMS X LEFT OUTERJOIN Memory\tmp M ON M.EmpID = X.EmpID; .... ,,, SELECT * FROM Memory\tmp ORDER BY empID; The syntax may be wrong a bit because its a long time since I last used DBISAM but the principle is sound. You'll also need to DROP Memory\tmp when you've finished using it or it will just hang around until the program is closed. Roy Lambert |
Mon, Oct 17 2016 3:20 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | jasanchez
You may be able to do something with self referencing joins eg select empid, sum( if(paycategory='salary',payamount,0)),x.payamount ,0 from payitems left outer join payitems x on x.empid = payitems.empid and paycategory = 'bonus' group by empid You'll need one join for each category Sorry for the appearance but I just had a mess in DBSys Roy Lambert |
Mon, Oct 17 2016 12:57 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | jasanchez wrote:
<< I need to have one single row and I tried this: >> Unfortunately, no, you cannot use sub-queries in the SELECT statement of a query in DBISAM. Furthermore, you can't use correlated sub-queries at all in DBISAM. To workaround this, you should do what Roy suggests, namely use a script to 1) Generate the initial in-memory table using a SELECT statement, 2) use an UPDATE statement to update the in-memory table with the values that you want from the other tables, and 3) use a SELECT statement to select from the in-memory table. Tim Young Elevate Software www.elevatesoft.com |
Wed, Oct 19 2016 3:13 PM | Permanent Link |
jasanchez | Tim Young [Elevate Software] wrote:
jasanchez wrote: << I need to have one single row and I tried this: >> Unfortunately, no, you cannot use sub-queries in the SELECT statement of a query in DBISAM. Furthermore, you can't use correlated sub-queries at all in DBISAM. To workaround this, you should do what Roy suggests, namely use a script to 1) Generate the initial in-memory table using a SELECT statement, 2) use an UPDATE statement to update the in-memory table with the values that you want from the other tables, and 3) use a SELECT statement to select from the in-memory table. Tim Young Elevate Software www.elevatesoft.com Tim and Robert: Thank you very much. At least there's a way out for doing that. Very much appreciated. |
Wed, Oct 19 2016 3:14 PM | Permanent Link |
jasanchez | jasanchez wrote:
Tim Young [Elevate Software] wrote: jasanchez wrote: << I need to have one single row and I tried this: >> Unfortunately, no, you cannot use sub-queries in the SELECT statement of a query in DBISAM. Furthermore, you can't use correlated sub-queries at all in DBISAM. To workaround this, you should do what Roy suggests, namely use a script to 1) Generate the initial in-memory table using a SELECT statement, 2) use an UPDATE statement to update the in-memory table with the values that you want from the other tables, and 3) use a SELECT statement to select from the in-memory table. Tim Young Elevate Software www.elevatesoft.com Tim and Robert: Thank you very much. At least there's a way out for doing that. Very much appreciated. Sorry it's Roy not Robert Thanks. |
This web page was last updated on Wednesday, April 24, 2024 at 11:07 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |