Can you spot the error in this sql query?-CodePudding

So, I have this query here. On trying to run, I get an error saying SQL command not properly ended. I can't find any obvious errors here. Hence, Seeking help.

SELECT 
g0.ARCHIVE_PERIOD,g0.PROJECT_OWNER,g0.CONTRACT_NUMBER
FROM 
(
        SELECT ROW_NUMBER() OVER (
        ORDER BY ARCHIVE_PERIOD
        ) ID,
        ARCHIVE_PERIOD,
        PROJECT_OWNER,
        CONTRACT_NUMBER
        FROM ARC_WORK_CONTRACT_GENERAL WHERE CONTRACT_NUMBER='whatever number u want'
    )AS g0
WHERE g0.id IN
(
    SELECT 
       g2.id
    FROM
        g0 g1
            INNER JOIN
        g0 g2 ON g2.id=g1.id 1 AND g1.PROJECT_OWNER!=g2.PROJECT_OWNER AND g1.CONTRACT_NUMBER=g2.CONTRACT_NUMBER
)

I was trying to compare consecutive rows of a derived dataset and output the details of rows having different project owners. I would like to know what is wrong/why is this query wrong syntactically

CodePudding user response：

You can use a sub-query factoring clause to be able to re-use G0:

WITH g0 (id, archive_period, project_owner, contract_number) AS (
  SELECT ROW_NUMBER() OVER (ORDER BY ARCHIVE_PERIOD),
         ARCHIVE_PERIOD,
         PROJECT_OWNER,
         CONTRACT_NUMBER
  FROM   ARC_WORK_CONTRACT_GENERAL
  WHERE  CONTRACT_NUMBER='whatever number u want'
)
SELECT ARCHIVE_PERIOD,
       PROJECT_OWNER,
       CONTRACT_NUMBER
FROM   g0
WHERE  id IN (
         SELECT g2.id
         FROM   g0 g1
                INNER JOIN g0 g2
                ON     g2.id = g1.id 1
                   AND g1.PROJECT_OWNER!=g2.PROJECT_OWNER
                   AND g1.CONTRACT_NUMBER=g2.CONTRACT_NUMBER
       )

or, it looks like (from Oracle 12) you can use MATCH_RECOGNIZE:

SELECT *
FROM   (
  SELECT ARCHIVE_PERIOD,
         PROJECT_OWNER,
         CONTRACT_NUMBER
  FROM   ARC_WORK_CONTRACT_GENERAL
  WHERE  CONTRACT_NUMBER='whatever number u want'
)
MATCH_RECOGNIZE(
  ORDER BY archive_period
  ALL ROWS PER MATCH
  PATTERN (matched)
  DEFINE
    matched AS  PREV(project_owner)   != project_owner
            AND PREV(contract_number) =  contract_number
)

or LAG:

SELECT *
FROM   (
  SELECT ARCHIVE_PERIOD,
         PROJECT_OWNER,
         CONTRACT_NUMBER,
         LAG(project_owner) OVER (ORDER BY archive_period) AS prev_owner,
         LAG(contract_number) OVER (ORDER BY archive_period) AS prev_contract
  FROM   ARC_WORK_CONTRACT_GENERAL
  WHERE  CONTRACT_NUMBER='whatever number u want'
)
WHERE prev_owner    != project_owner
AND   prev_contract =  contract_number;

CodePudding user response：

There are a few issues with the query you have provided:

The FROM clause at the end of the query refers to a table alias g0, but this alias is not defined in that scope. You can only reference a table alias in the same FROM clause in which it is defined.

The FROM clause at the end of the query also includes a JOIN clause, but there is no ON clause to specify the join condition.

The SELECT statement is missing a FROM clause, which is required to specify the source of the data being selected.

To fix these issues, you will need to modify the query as follows:

SELECT g0.ARCHIVE_PERIOD, g0.PROJECT_OWNER, g0.CONTRACT_NUMBER
FROM (
  SELECT ROW_NUMBER() OVER (
    ORDER BY ARCHIVE_PERIOD
  ) ID,
  ARCHIVE_PERIOD,
  PROJECT_OWNER,
  CONTRACT_NUMBER
  FROM ARC_WORK_CONTRACT_GENERAL WHERE CONTRACT_NUMBER='whatever number u want'
) AS g0
INNER JOIN (
  SELECT ROW_NUMBER() OVER (
    ORDER BY ARCHIVE_PERIOD
  ) ID,
  ARCHIVE_PERIOD,
  PROJECT_OWNER,
  CONTRACT_NUMBER
  FROM ARC_WORK_CONTRACT_GENERAL WHERE CONTRACT_NUMBER='whatever number u want'
) AS g1
ON g1.id = g0.id   1 AND g0.PROJECT_OWNER != g1.PROJECT_OWNER AND g0.CONTRACT_NUMBER = g1.CONTRACT_NUMBER

This revised query should correctly compare consecutive rows of the derived dataset and output the details of rows having different project owners.