CODEWITHSUNDEEP
oracle-databaseaggregate-functionsminora-00904
user2102665
user2102665

Reputation: 505

Column name become invalid after referred as result of aggregate function MIN()

SELECT cust_detl.*, 
       MIN(CREATION_TIMESTAMP) OVER (PARTITION BY CUST_ID) AS MIN_TIMESTAMP
FROM CUST_DETAILS cust_detl
WHERE CREATION_TIMESTAMP=MIN_TIMESTAMP;

Above query select all columns from table CUST_DETAILS with oldest value inCREATION_TIMESTAMP column.

Any idea why MIN_TIMESTAMP encounter as an invalid identifier?

These are the columns that should display:

SELECT
  CUSTOMER_DTL_SEQ.nextval,
  CUST_ID
  CUS_REF_ID
  CUST_NAME
  CUST_ADDRESS
  CREATION_TIMESTAMP
FROM
(
  CUSTOMER_DTL_SEQ.nextval,
   cust_detl.CUST_ID,
   cust_detl.CUST_REF_ID,
   cust_detl.CUST_NAME,
   cust_detl.CUST_ADDRESS,
   cust_detl.CREATION_TIMESTAMP,
    MIN(CREATION_TIMESTAMP) OVER (PARTITION BY CUST_ID) AS min_timestamp
    FROM cust_details cust_detl
  )
 WHERE CREATION_TIMESTAMP = min_timestamp;

I would need to select CREATION_TIMESTAMP column as well, only those columns with minimum timestamp will be selected. The problem is the sequence with nextval is not allowed. I would need sequence in the query as this statment is going to use for INSERT later SELECT...INSERT INTO

The PK need to be incremented.

Upvotes: 0

Views: 84

Answers (1)

San
San

Reputation: 4538

The column name is not yet valid, data is filtered first with the where condition and then on the filtered data, select statement works. You need to put it in sub query before you can use it.

SELECT * FROM
(SELECT cust_detl.*, 
        MIN(CREATION_TIMESTAMP) OVER (PARTITION BY CUST_ID) AS MIN_TIMESTAMP
   FROM CUST_DETAILS cust_detl)
WHERE CREATION_TIMESTAMP=MIN_TIMESTAMP;

UPDATE: I don't know what list of columns you have in your table, but if you need only specific columns, then the query goes like this(assuming you need only columns cust_id, column1, column2 and column3 in select list)

SELECT cust_id,
       column1,
       column2,
       column3
  FROM (SELECT cust_detl.cust_id,
               cust_detl.column1,
               cust_detl.column2,
               cust_detl.column3,
               cust_detl.creation_timestamp,
               MIN(creation_timestamp) over(PARTITION BY cust_id) AS min_timestamp
          FROM cust_details cust_detl)
 WHERE creation_timestamp = min_timestamp;

If you still don't get your solution, the post the list of columns from the table and the expected output.

Update2 : Fetch the cursor in the outer query, this query should work fine.

SELECT customer_dtl_seq.nextval,
       cust_id,
       cus_ref_id, 
       cust_name,
       cust_address,
       creation_timestamp
  FROM (SELECT cust_detl.cust_id,
               cust_detl.cust_ref_id,
               cust_detl.cust_name,
               cust_detl.cust_address,
               cust_detl.creation_timestamp,
               MIN(creation_timestamp) over(PARTITION BY cust_id) AS min_timestamp
          FROM cust_details cust_detl)
 WHERE creation_timestamp = min_timestamp;

Upvotes: 1

Related Questions