Geovani Martinez Geovani Martinez - 5 months ago 5
SQL Question

Oracle 8i SQL command not properly ended

In the SQL statement below I am passing ROUTER_ID and expecting to have the most recent version of RTR_HEADER record along with related RTR_SEQUENCE returned.

SELECT
RH.ROUTER_ID,
RH.DESCRIPTION,
RSEQ.SEQUENCE_NUMBER
FROM
CFG.RTR_SEQUENCE RSEQ
INNER JOIN CFG.RTR_HEADER RH
ON RSEQ.ROUTER_ID = RH.ROUTER_ID
AND RSEQ.ROUTER_REV = RH.ROUTER_REV
WHERE
RH.ROUTER_REV = (SELECT MAX(RVER.ROUTER_REV) FROM CFG.RTR_HEADER RVER WHERE RVER.ROUTER_ID = RH.ROUTER_ID)
AND RH.ROUTER_ID = 'XXXXX-XXX'
ORDER BY RH.ROUTER_ID, RSEQ.SEQUENCE_NUMBER


I keep getting SQL command not properly ended with intelligence pointing at the INNER JOIN. If I omit the INNER JOIN and its related table and fields the SQL statement runs. I do get the recent version of the RTR_HEADER record, but I need the matching RTR_HEADER as well. I looked at many of the other existing answers here on SO, taken their resolution into consideration without any results.

Visual Representation of Relationship

Intelisence displaying error

Answer
SELECT 
   RH.ROUTER_ID,
   RH.DESCRIPTION,
   RSEQ.SEQUENCE_NUMBER
FROM 
   CFG.RTR_SEQUENCE RSEQ,CFG.RTR_HEADER RH
WHERE RSEQ.ROUTER_ID = RH.ROUTER_ID
  AND RSEQ.ROUTER_REV = RH.ROUTER_REV
  AND RH.ROUTER_REV = (SELECT MAX(RVER.ROUTER_REV) FROM CFG.RTR_HEADER RVER WHERE RVER.ROUTER_ID = RH.ROUTER_ID) 
AND RH.ROUTER_ID = 'XXXXX-XXX'
ORDER BY RH.ROUTER_ID, RSEQ.SEQUENCE_NUMBER
Comments