sqlsqlbase

SQLBase Error: 00905 PRS MRP Missing right parenthesis

I'm trying to select the most recent inspection record for each unit in our DB, which is running on SQLBase 11.7. I thought maybe I could do something like

SELECT Col1, Col2, Col3 
FROM Insp_Header 
WHERE (unit_id, insp_dt) IN (SELECT unit_id, MAX(insp_dt) 
                             FROM Insp_Header 
                             GROUP BY unit_id);

but I get the error in the title when I try this. I also tried to get around this by using an explicit join,

SELECT Col1, Col2, Col3 FROM Insp_header 
INNER JOIN (
     SELECT unit_id, MAX(insp_dt) 
     FROM Insp_Header GROUP BY unit_id) meh 
        ON Insp_Header.unit_id=meh.unit_id 
        AND Insp_Header.insp_dt=meh.insp_dt;

and when that didn't work, an implicit one.

SELECT Col1, Col2, Col3 
FROM Insp_Header
, (  SELECT unit_id, MAX(insp_dt) 
     FROM Insp_Header GROUP BY unit_id) meh 
     WHERE Insp_Header.unit_id=meh.unit_id 
     AND Insp_Header.insp_dt=meh.insp_dt;

Both resulted in the same error mentioned in the title. The only difference is that the arrow indicating where the error occurred pointed to the comma in the expression between the WHERE and IN keywords in my original attempt and now the arrow is pointing to the comma after the first column in the SELECT clause of the subquery. I'm struggling to think of a way to make this work or an alternate way to accomplish the same goal. Any suggestions would be appreciated, thank you.

Solution

  • All the examples given so far are no way SQLBase syntax. You're obviously thinking of SQLServer . The following is quick and easy ( and SQLBase syntax ), and using Travis Shands very first example - simply replace the ',' in the Where clause with '||'

    SELECT Col1, Col2, Col3 
FROM Insp_Header 
WHERE (unit_id || insp_dt) IN (SELECT unit_id || MAX(insp_dt) 
                               FROM Insp_Header 
                               GROUP BY unit_id);

    p.s. For your future reference, using words like

    'unfortunately is SQLBase 11.7'

    does little to encourage SQLBase fans to help you .