We help IT Professionals succeed at work.

Need to change the query with out referring to partition...

Rao_S asked
we have a partition on a table called (INBOUND) and (OUTBOUND) - table is list partitioned ...
we have to drop the partition so have to have a alternate for the inbound_outbound_ind ....etc..
i have attached a file which shows a query... can you help me rewrite the query when the partition is dropped...... change-this-query.txt
Watch Question

Most Valuable Expert 2012
Distinguished Expert 2019
>>FIRST_VALUE (inbound_seq_pk) OVER (PARTITION BY miss_object_id ORDER BY seq_nbr ASC)

This is NOT like table partitions and has nothing to do with them.  the 'partition by' clause of a data warehouse window function like FIRST_VALUE is the same as a 'group by' clause on a regular select.

It tells the function how to group the 'window'.
HainKurtSr. System Analyst
     (SELECT DISTINCT FIRST_VALUE (inbound_seq_pk) OVER (PARTITION BY miss_object_id ORDER BY seq_nbr ASC) /** i dont understand this line */

this means, group the records by miss_object_id, then order by seq_nbr ASC and get the first_value
HainKurtSr. System Analyst
also the query you posted is not complete, it requires "FROM someTable" clause...


sorry about that...
attached the corrected file... change-this-query.txt
The relevant part of the query is actually this:

UPDATE aqtit01.tqt_it_inbound_seq PARTITION(INBOUND)

Can you post the table definition (including the partitioning).

I think you will be able to replace the PARTIITON(INBOUND) by an additional WHERE clause that specifies the equivalent of the partition values for INBOUND.


thank you..!!!