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.

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

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

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 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.

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.