-1

I have a query which should work, but it seems I am a victim of a poor database technology. I need to run the query below on a Pervasive SQL database. The manufacturer of the product using Pervasive tells me the version they are using is 10 which should support subqueries, but I have yet to be able to run even a simple subquery. So, I am wondering if the following query can be rewritten to eliminate the subquery:

SELECT
    OuterTime.Employee,
    OuterTime.Date,        
    OuterTime.Pay_ID,        
    OuterTime.Description,        
    OuterTime.Equipment,        
    OuterTime.JC_Cost_Code,        
    OuterTime.JC_Category,        
    OuterTime.Units,  
    (   
        SELECT
            SUM(SubQueryTime.Units)
        FROM
            PRT_NEW__TIME AS SubQueryTime
        WHERE
            SubQueryTime.Employee = OuterTime.Employee
        GROUP BY
            SubQueryTime.Employee
    ) AS TotalHoursForEmp
FROM
    PRT_NEW__TIME AS OuterTime
Joe Schrag
  • 855
  • 8
  • 23
  • Can you please explain what this query do, and what do you exepect from this query becouse i can't understand what is the need of subquery becouse, in the outer and the sub-query you are selecting from the same table `PRT_NEW__TIME`?? – Mahmoud Gamal Nov 15 '11 at 15:37
  • he's using it to return a sum. I guess he cant use a group on the main query for some reason – Richard Banks Nov 15 '11 at 15:44
  • I need both the Units and the sum of Units from the same table for a calculation. The query above is simplified. The goal is to calculate Units/Total Units * $44. It is a per diem calculation. – Joe Schrag Nov 15 '11 at 15:58
  • Does your database support derived tables? I.e. select * from ( select * from table ) as derivedtable. If so, that might be the way to go – Shan Plourde Nov 15 '11 at 16:09
  • @ShanPlourde Good idea. I tried that as well with no luck. – Joe Schrag Nov 15 '11 at 16:12
  • @Joseph Schrag why do you want to get rid of it – Richard Banks Nov 15 '11 at 16:23
  • What version of Pervasive are you using? What error are you getting? – mirtheil Nov 15 '11 at 16:39
  • @RichardBanks I need to get rid of it because it appears that the version of Pervasive I am using does not appear to support subqueries. – Joe Schrag Nov 15 '11 at 17:24
  • @mirtheil the error is "Database connector Error: '42000:[Sage Timberline Office][Sage Timberline Office ODBC Driver]SELECT...OuterTime.Units,(SELECT << ??? >> SUM(SubQueryTime.Units)..." --The ellipses are mine for brevity – Joe Schrag Nov 15 '11 at 17:25
  • 1
    Just for clarification, you aren't using the PSQL ODBC driver. Timberline in their infinite wisdom provides an ODBC driver that doesn't offer the same capabilities as the standard Pervasive driver. If you can switch to the Pervasive driver and you are using PSQL v10, your original query should work. – mirtheil Nov 15 '11 at 19:03
  • Ah, that is likely my problem. Thank you @mirtheil! I will look into what I need to do to get a DSN set up using a Pervasive driver. All I have currently is the Timberline driver installed with all of the Sage stuff. – Joe Schrag Nov 15 '11 at 19:14

2 Answers2

0

In standard SQL you can do this, though I have no idea about PervasiveSQL specifically...

SELECT
    OuterTime.Employee,
    OuterTime.Date,        
    OuterTime.Pay_ID,        
    OuterTime.Description,        
    OuterTime.Equipment,        
    OuterTime.JC_Cost_Code,        
    OuterTime.JC_Category,        
    OuterTime.Units,
    COALESCE(TotalHoursForEmp.TotalUnits, 0)  AS TotalUnits
FROM
    PRT_NEW__TIME AS OuterTime
LEFT JOIN
    (   
        SELECT
            Employee,
            SUM(Units) AS TotalUnits
        FROM
            PRT_NEW__TIME
        GROUP BY
            Employee
    )
    AS TotalHoursForEmp
        ON TotalHoursForEmp.Employee = OuterTime.Employee
MatBailie
  • 83,401
  • 18
  • 103
  • 137
  • Gave it a shot, but no go. From what I'm reading, in older versions of Pervasive, you can only do subqueries in the WHERE or HAVING clauses. Despite what the level 1 tech support guy at the manufacturer said, I think this is older than version 10. – Joe Schrag Nov 15 '11 at 15:52
  • 1
    then I think you're stuck doing this in two parts. Insert the sub query into a temporary table, then join on that. Depending on your operational environment, you may need to bound that in a transaction and lock the table (in case the contents changes between making the temp table, and then joining back again). – MatBailie Nov 15 '11 at 15:58
0

Not a Pervasive SQL guy, but would this work? Not sure it accomplishes your query goals:

SELECT
    OuterTime.Employee,
    OuterTime.Date,        
    OuterTime.Pay_ID,        
    OuterTime.Description,        
    OuterTime.Equipment,        
    OuterTime.JC_Cost_Code,        
    OuterTime.JC_Category,        
    SUM(OuterTime.Units) AS TotalHoursForEmp
    GROUP BY OuterTime.Employee, OuterTime.Date, OuterTime.Pay_ID,
             OuterTime.Description, OuterTime.Equipment, OuterTime.JC_Cost_Code,
             OuterTime.JC_Category
FROM
    PRT_NEW__TIME AS OuterTime
Shan Plourde
  • 8,528
  • 2
  • 29
  • 42
  • The OP requires both the `units` value for each record, as well as the SUM() of that field by employee. The query here does not logically match the one in the OP. – MatBailie Nov 15 '11 at 15:45
  • I understand, I hope that I framed my answer properly with my question "would this work?" and statement "Not sure if accomplishes your query goals". – Shan Plourde Nov 15 '11 at 16:08